基于组返回ID值

Question

对于表t1

+----+------+ 
| ID | Flag | 
+----+------+ 
| 1 | 1 | 
| 1 | 0 | 
| 1 | 0 | 
| 2 | 0 | 
| 2 | 0 | 
| 2 | 0 | 
| 3 | 1 | 
| 3 | 1 | 
| 3 | 0 | 
+----+------+ 

我需要提取的样本子集ID的基础上以两种不同方式的标志值：

1）所有的ID没有1的中旗。

+----+ 
    | ID | 
    +----+ 
    | 2 | 
    +----+ 

2）所有ID在Flag中有多个1。

+----+ 
    | ID | 
    +----+ 
    | 3 | 
    +----+ 

尝试查询不是有用因此排除实现，这是使用的情况下的聚集

Answer 1

的一个简单方法。例如：

DECLARE @T TABLE (ID INT, Flag BIT); 
INSERT @T VALUES (1, 1), (1, 0), (1, 0), (2, 0), (2, 0), (2, 0), (3, 1), (3, 1), (3, 0); 

SELECT ID 
FROM @T 
GROUP BY ID 
HAVING COUNT(CASE WHEN Flag = 1 THEN 1 END) = 0; -- No flag 1. 

SELECT ID 
FROM @T 
GROUP BY ID 
HAVING COUNT(CASE WHEN Flag = 1 THEN 1 END) > 1; -- More than one flag 1. 

Answer 2

使用以下查询检查标志计数总数。

1）

SELECT id, 
     SUM(flag) cnt 
    FROM t1 
GROUP BY id 
HAVING SUM(flag) = 0 

结果

id cnt 
2 0 

2）

SELECT id, 
     SUM(flag) cnt 
    FROM t1 
GROUP BY id 
HAVING SUM(flag) > 1 

结果

id cnt 
3 2 

Answer 3

这是一个代码特例位，由于标志为数字和一个1或0

SELECT ID, 
    CASE 
     WHEN Ones = 0 
      Then 'No active flags' 
     When Ones > 1 
      Then 'Multiple Active flags' 
    END 
FROM (
    SELECT ID, 
     Sum(Cast(Flag as int)) as Ones 
    FROM t1 
    GROUP BY ID 
    ) as Src 
WHERE Ones != 1 

Answer 4

只要你的标志是数字和只有1和0值，那么你可以使用下面的两个查询：

select ID from t1 group by ID having sum(cast(flag as int)) = 0; 

和

select ID from t1 group by ID having sum(cast(flag as int)) > 1;