带 where 子句的 T-SQL Group by

 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 or CC = CN and NLCLA = 1 and NLDES = 1

输出应该是

53006141
53006143

MasterID 下必须同时有 CN 和 US。

有人可以帮我用 SQL 来做这个吗？

您可以通过添加一个来做到这一点WHERE子句将返回行US or CN:

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

See SQL 摆弄演示 http://sqlfiddle.com/#!3/4758e/4

如果您希望结果同时包含CN and US，那么你可以使用：

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 摆弄演示 http://sqlfiddle.com/#!3/968a1/2.

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

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 摆弄演示 http://sqlfiddle.com/#!3/968a1/9