只选择某一列的所有记录值均为特定值的唯一记录组。

Question

下面有8条记录：

ID   | Common ID | Reject
-------------------------
AB-1 | AB        | NULL
AB-2 | AB        | YES
AB-3 | AB        | NULL
BB-1 | BB        | YES
BB-2 | BB        | YES
BB-3 | BB        | YES
CB-1 | CB        | YES
CB-2 | CB        | YES
DB-1 | DB        | NULL

我的期望结果是：

ID   | Common ID | Reject
-------------------------
BB-1 | BB        | YES
CB-1 | CB        | YES

当拒绝列对于具有相同公共ID的所有记录均为是时，我只希望获取不同的记录。

- John Evans Solachuk

Answer 1

select min(ID), [Common ID], max(Reject)
from tablename
group by [Common ID]
having count(*) = count(case when Reject = 'YES' then 1 end)

如果一个[Common ID]的行数与YES的数量相同，则返回它！

HAVING子句中的count(*)返回[Common ID]的总行数。case表达式在Reject = Yes时返回1，否则返回null。右侧的count返回case返回非空值（即当Reject为yes时）的行数。当行数相同时，HAVING为真！

编辑：在这种特定情况下，当Reject列的值似乎是YES或NULL时，HAVING可以简化为：

having count(*) = count(Reject)

然而，如果在该列中之后发现其他值（比如NO），这种方法就不再适用了。因此我建议使用原始的HAVING子句！

- jarlh

---

顺便问一下，count(*) = count(case when Reject = 'YES' then 1 end) 的实际值是多少？我不太理解这行代码。你能给我展示每次循环中 count(*) 和对应的 count(case when Reject = 'YES' then 1 end) 的值吗？ - John Evans Solachuk

@Markorie，我在我的回答中添加了解释。（太长了，不适合作为评论。） - jarlh

Answer 2

SELECT MIN(ID), CommonID, MIN(Reject) as Reject
FROM   yourtable
GROUP BY CommonID
HAVING  MIN(ISNULL(Reject, ''))  = MAX(ISNULL(Reject, ''))
AND     MIN(ISNULL(Reject, '')) = 'Yes'

编辑：由于您有NULL值，需要在该列上使用ISNULL()函数。

- Squirrel

Answer 3

在你的WHERE子句中使用一个带有GROUP BY的子查询。

SELECT ID, [Common ID], Reject
FROM yourtable
WHERE (SELECT SUM(LEN(a.Reject)) 
       FROM yourtable a 
       WHERE ID = a.ID 
       GROUP BY a.ID) IS NULL

输出：

SQL Fiddle：

- Matt

Answer 4

使用窗口函数row_number和not exists。有点复杂，但应该可以完成工作：

with cte as(select *, row_number(partition by commonid order by id) rn from table)
select c.* 
from cte c
where c.rn = 1 and 
      not exists(select * from table t 
                 where t.commonid = c.commonid and 
                       (t.reject is null or t.reject <> 'yes'))

- Giorgi Nakeuri

Answer 5

SELECT MIN(ID) as ID, [Common ID], MIN(Reject) as Reject
FROM   Table1
GROUP BY [Common ID]
HAVING  MIN(ISNULL(Reject, '')) = 'YES'

- bmsqldev

Answer 6

这会对你有所帮助

select
(select top 1 ID from YourTable where CommonID= t.CommonID and reject=t.reject)ID,
CommonID,
Reject
from YourTable T
group by
CommonID,reject
having (Reject is not null)

- Pankaj Kumar

Answer 7

试试这个

SELECT MIN(ID) AS ID, ColumnID, MIN(Reject) AS Reject
       FROM tableName
       WHERE Reject='YES' AND ID LIKE '%1'
       GROUP BY ColumnID

- reza.cse08

Answer 8

这是实现您期望结果的最简单方法。

 SELECT ID, [Common ID], Reject
 FROM YOURTABLE
 WHERE REJECT = 'YES'
 GROUP BY ID, COMMON ID

- jelliaes