Блог

Я пытаюсь получить все идентификаторы из списка 1, и с этими идентификаторами из списка 1 я пытаюсь получить все значения из списка 2 вместе с подсчетом, основанным на значениях из списка 2.

 DECLARE @Table1 AS TABLE (
    id int, 
    l1 varchar(20)
);

INSERT INTO @Table1 VALUES
(1,'sun'),
(2,'shine'),
(3,'moon'),
(4,'light'),
(5,'earth'),
(6,'revolves'),
(7,'flow'),
(8,'fire'),
(9,'fighter'),
(10,'sun'),
(10,'shine'),
(11,'shine'),
(12,'moon'),
(1,'revolves'),
(10,'revolves'),
(2,'air'),
(3,'shine'),
(4,'fire'),
(5,'love'),
(6,'sun'),
(7,'rises');

/*
OPERATION 1
fetch all distinct ID's that has values from List 1
List1
sun
moon
earth

Initial OUTPUT1:
distinct_id list1_value
1           sun
3           moon
5           earth
10          sun
12          moon
6           sun


OPERATION2
fetch all the id, count_of_list2_values, list2_values  
based on the id's that we recieved from OPERATION1

List2
shine
revolves

Expected Output:

id  list1-value count_of_list2_values, list2_values  
1     sun              1                 revolves
3     moon             1                 shine
5     earth            0                 NULL
10    sun              2                 shine,revolves
12    moon             0                 NULL
6     sun              1                 revolves
*/
  

Мой запрос:
Вот что я попробовал

 select id, count(l1),l1
from @table1
where id in ('shine','revolves') and id in ('sun','moon','earth')
  

Как я могу этого добиться?
Я знаю, что это должен быть подзапрос с несколькими in. Как этого можно достичь?

Ссылка на SQL fiddle: https://dbfiddle.uk/?rdbms=sqlserver_2017amp;fiddle=7a85dbf51ca5b5d35e87d968c46300bb
foo
foo

Это можно сделать несколькими способами. Вот как я бы это сделал:

Сначала настройте данные:

 DECLARE @Table1 AS TABLE (
    id int, 
    l1 varchar(20)
) ;

INSERT INTO @Table1 VALUES
(1,'sun'),
(2,'shine'),
(3,'moon'),
(4,'light'),
(5,'earth'),
(6,'revolves'),
(7,'flow'),
(8,'fire'),
(9,'fighter'),
(10,'sun'),
(10,'shine'),
(11,'shine'),
(12,'moon'),
(1,'revolves'),
(10,'revolves'),
(2,'air'),
(3,'shine'),
(4,'fire'),
(5,'love'),
(6,'sun'),
(7,'rises') ;
  

Поскольку это известный список, задайте «целевые» данные как собственный набор. (В SQL с таблицами почти всегда лучше работать, чем с сумасшедшими списками. Ой, опечатка! Я имел в виду списки с разделителями.)

 DECLARE @Targets AS TABLE (
    l2 varchar(20)
) ;

INSERT INTO @Targets VALUES
('sun'),
('moon'),
('earth') ;
  

ОПЕРАЦИЯ 1
извлеките все различные идентификаторы, которые имеют значения из списка 1
(солнце, Луна, Земля)

Достаточно просто с объединением:

 SELECT Id
 from @Table1  t1
  inner join @Targets  tg
   on tg.l2 = t1.l1
  

ОПЕРАЦИЯ 2
извлеките все идентификаторы, count_of_list2_values, list2_values

на основе идентификаторов, которые мы получили от OPERATION1

Если я правильно следую желаемой логике, то (сначала прочитайте комментарии «присоединиться»):

 SELECT
   tt.Id
   --  This next counts how many items in the Operation 1 list are not in the target list
   --  (Spaced out, to make it easier to compare with the next line)
  ,sum(       case when tg2.l2 is null then 1 else 0 end) 
   --  And this concatenates them together in a string (in later editions of SQL Server)
  ,string_agg(case when tg2.l2 is null then tt.l1 else null end, ', ')
 from @Table1 tt
  inner join (--  Operation 1 as a subquery, produce list of the Ids to work with
              select t1.id
               from @Table1  t1
                inner join @Targets  tg
                 on tg.l2 = t1.l1
             ) xx
   on xx.id = tt.id
  --  This is used to identify the target values vs. the non-target values
  left outer join @Targets    tg2
   on tg2.l2 = tt.l1
 --  Aggregate, because that's what we need to do
 group by tt.Id
 --  Order it, because why not?
 order by tt.Id
  

Если вы используете Sql Server 2017, вы можете использовать string_agg функцию и outer apply оператор:

 select
    l1.id,
    l1.l1,
    l2.cnt as count_of_list2_values,
    l2.l1 as list2_values  
from @Table1 as l1
    outer apply (
        select
            count(*) as cnt,
            string_agg(tt.l1, ',') as l1
        from @Table1 as tt
        where
            tt.l1 in ('shine','revolves') and
            tt.id = l1.id
    ) as l2
where
    l1.l1 in ('sun','moon','earth')
  

db fiddle demo

В предыдущих версиях я не уверен, что можно агрегировать и подсчитывать за один проход без создания специальной функции для этого. Вы, конечно, можете сделать это подобным образом с xquery , но это может быть немного излишним (я бы не стал этого делать, по крайней мере, в производственном коде):

 select
    l1.id,
    l1.l1,
    l2.data.value('count(l1)', 'int'),
    stuff(l2.data.query('for $i in l1 return concat(",",$i/text()[1])').value('.','nvarchar(max)'),1,1,'')
from @Table1 as l1
    outer apply (
        select
            tt.l1
        from @Table1 as tt
        where
            tt.l1 in ('shine','revolves') and
            tt.id = l1.id
        for xml path(''), type
    ) as l2(data)
where
    l1.l1 in ('sun','moon','earth')
  

db fiddle demo

Если вы не возражаете сделать это с помощью двойного сканирования / поиска таблицы, вы можете либо использовать ответ @forpas, либо сделать что-то вроде этого:

 with cte_list2 as (
    select tt.l1, tt.id
    from @Table1 as tt
    where
        tt.l1 in ('shine','revolves')
)
select
    l1.id,
    l1.l1,
    l22.cnt as count_of_list2_values,
    stuff(l21.data.value('.', 'nvarchar(max)'),1,1,'') as list2_values
from @Table1 as l1
    outer apply (
        select
            ','   tt.l1
        from cte_list2 as tt
        where
            tt.id = l1.id
        for xml path(''), type
    ) as l21(data)
    outer apply (
        select count(*) as cnt
        from cte_list2 as tt
        where
            tt.id = l1.id
    ) as l22(cnt)
where
    l1.l1 in ('sun','moon','earth')
  

С помощью этого:

 with 
cte as(
  select t1.id, t2.l1
  from table1 t1 left join (
    select * from table1 where l1 in ('shine','revolves')
  ) t2 on t2.id = t1.id
  where t1.l1 in ('sun','moon','earth')
),
cte1 as(
  select 
    c.id, 
    stuff(( select ','   cte.l1 from cte where id = c.id for xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '') col
  from cte c
)

select 
  id, 
  count(col) count_of_list2_values, 
  max(col) list2_values  
from cte1
group by id
  

1-й CTE дает эти результаты:

 id | l1      
-: | :-------
 1 | revolves
 3 | shine   
 5 | null    
10 | shine   
10 | revolves
12 | null    
 6 | revolves
  

и 2 -й оперирует этими результатами, чтобы объединить общие сгруппированные значения l1 .
Наконец, я использую group by id и aggergation по результатам 2-го CTE.

Смотрите демонстрацию
Результаты:

 id | count_of_list2_values | list2_values  
-: | --------------------: | :-------------
 1 |                     1 | revolves      
 3 |                     1 | shine         
 5 |                     0 | null          
 6 |                     1 | revolves      
10 |                     2 | shine,revolves
12 |                     0 | null