时间戳

首页 > 解决方案 > 使用多个 IN 运算符的子查询

sql - 使用多个 IN 运算符的子查询

问题描述

我正在尝试获取列表 1 中的所有 id，并使用列表 1 中的这些 id，我试图获取列表 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')

我怎样才能做到这一点。我知道这应该是一个子查询，有多个。如何实现？

SQL小提琴链接： https ://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle= 7a85dbf51ca5b5d35e87d968c46300bb foo foo

标签： sqlsql-serversql-server-2008

解决方案

有了这个：

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

第一次 CTE 给出了以下结果：

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

并且 2nd 对这些结果进行操作以连接 的公共分组值l1。
最后，我group by id对第二次 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

推荐阅读