T-SQL Group与一个where子句 [英] T-SQL Group by with a where clause

问题描述

  Masterid CC CLA DES NLCLA NLDES 
 ---------------------------- --------- 
 53006141 CN 0 0 1 1 
 53006141 US 1 1 1 1 
 53006141 UK 1 1 0 0 
 53006142 US 1 1 0 0 
 53006142 UK 1 1 0 0 
 53006143 CN 0 0 1 1 
 53006143 US 1 1 0 0 
 53006143 UK 1 1 0 0 
  
 
 
 
从上面的数据我需要产生一个列表
 
 
 
 
一个列表的 MasterIds ，其中 CC = US 或 CC = CN 和 NLCLA = 1 和 NLDES = 1  
 
 
 
 
 
输出应该是 
 
  53006141 
 53006143 
  
在MasterID下必须同时存在CN和US。
 
 
 
请帮助我在SQL中执行此操作？     您可以通过添加 W HERE 子句将返回包含 US 或 CN 的行：
 $ b'b 
 
 从yourtable $ b $中选择不同的Masterid 
其中cc（'US'，'CN'）
和NLCLA = 1 
 AND NLDES = 1 
  
请参阅 SQL小提琴演示 
 
 
 
如果您希望结果包含 CN 和 US ，那么您可以使用： 
 
 <$ p $ （'US'，'CN'）
和NLCLA = 1 
 AND NLDES = 1 $ b中选择Masterid 
 $ b group by masterid 
 having count（distinct cc）= 2 
  

请参阅 SQL小提琴演示。

另一种方法是使用 EXISTS 来获取MasterIds列表，其中 US 和 CN 。然后您将其他过滤器放在 WHERE 子句中，而不是放在子查询中。

 中选择明显的masterid 
 where存在（从yourtable t2中选择Masterid 
 
其中cc（'US'，'CN'）
和t1.masterid = t2.masterid 
 group by masterid 
有count（distinct cc）= 2）
和NLCLA = 1 
和NLDES = 1; 
  

请参阅带演示的SQL小提琴

 Masterid    CC  CLA DES NLCLA   NLDES
 -------------------------------------
 53006141    CN  0   0   1       1
 53006141    US  1   1   1       1
 53006141    UK  1   1   0       0
 53006142    US  1   1   0       0
 53006142    UK  1   1   0       0
 53006143    CN  0   0   1       1
 53006143    US  1   1   0       0
 53006143    UK  1   1   0       0

From the above data I need to produce

• a list of MasterIds where there is CC = US or CC = CN and NLCLA = 1 and NLDES = 1

The output should be

53006141
53006143

There has to be both CN and US under a MasterID.

Can someone help me to do this in SQL please?

解决方案

You can do this by adding a WHERE clause which will return rows with either US or CN:

select distinct Masterid
from yourtable
where cc in ('US', 'CN')
  and NLCLA = 1
  AND NLDES = 1

See SQL Fiddle with Demo

If you want the result to include both the CN and US, then you can use:

select Masterid
from yourtable
where cc in ('US', 'CN')
  and NLCLA = 1
  AND NLDES = 1
group by masterid
having count(distinct cc) = 2

See SQL Fiddle with Demo.

Another way that this could be done is using an EXISTS to get the list of MasterIds with both the US and CN. You then place the other filters in the WHERE clause and not in the subquery.

select distinct masterid
from yourtable t1
where exists (select Masterid
              from yourtable t2
              where cc in ('US', 'CN')
                and t1.masterid = t2.masterid
              group by masterid
              having count(distinct cc) = 2)
  and NLCLA = 1
  and NLDES = 1;

See SQL Fiddle with Demo

这篇关于T-SQL Group与一个where子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！