SQL Server 基于行的分组

Question

鉴于以下设置:

DECLARE @TemplateInfo table (Name varchar(10), aRow int, aCol int, Value  int)

insert INTO @TemplateInfo (Name, aRow, aCol, Value)
VALUES ('A', 0,0,1),('B', 0,0,2),('A',1,0,2),('B',1,0,1),('C',0,0,1),('C',1,0,1)

如何选择具有 (aRow=0, aCol=0, Value=1) AND (aRow=1, aCol=0, Value=1) 的唯一名称名称值。

我试过这样的查询:

select  Name
from    @TemplateInfo info
where   (info.aRow = 0 and info.aCol = 0 AND Value = 1) 
        or (info.aRow = 1 and info.aCol = 0 AND Value = 1)
GROUP BY Name

但这会返回 A、B 和 C。我如何检查以确保跨行匹配(仅返回 C)

Answer 1

你真的很亲近。由于您想同时评估这两个条件，您可以使用 HAVING :

select  Name
from    @TemplateInfo info
where   (info.aRow = 0 and info.aCol = 0 AND Value = 1) 
        or (info.aRow = 1 and info.aCol = 0 AND Value = 1)
group by Name
HAVING COUNT(*) = 2

尽管这仅在 aRow, aCol, Value 只有一种组合时才有效每个 name .