如何改进这个SQL查询？

Question

我今天遇到了一个有趣的SQL问题，虽然我想出了一个可行的解决方案，但我怀疑它不是最好或最高效的答案。我请教专家们-帮助我学习并改进我的查询！RDBMS是SQL Server 2008 R2，查询是SSRS报告的一部分，将运行大约10万行。

基本上，我有一个ID列表，这些ID可能与多个值相关联，这些值为Yes，No或其他字符串。对于ID x，如果任何值为Yes，则x应为Yes，如果它们全部为No，则应为No，如果它们包含除yes和no之外的任何其他值，请显示该值。我只想返回每个ID的1行，没有重复。

简化版本和测试用例:

DECLARE @tempTable table ( ID int, Val varchar(1) )

INSERT INTO @tempTable ( ID, Val ) VALUES ( 10, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 11, 'N')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 11, 'N')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 12, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 12, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 12, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 13, 'N')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 14, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 14, 'N')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 15, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 16, 'Y')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 17, 'F')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 18, 'P')


SELECT DISTINCT t.ID, COALESCE(t2.Val, t3.Val, t4.Val)
FROM @tempTable t
LEFT JOIN
(
    SELECT ID, Val
    FROM @tempTable
    WHERE Val = 'Y'
) t2 ON t.ID = t2.ID
LEFT JOIN
(
    SELECT 
    ID, Val FROM @tempTable
    WHERE Val = 'N'
) t3 ON t.ID = t3.ID
LEFT JOIN
(
    SELECT ID, Val
    FROM @tempTable
    WHERE Val <> 'Y' AND Val <> 'N'
) t4 ON t.ID = t4.ID

非常感谢预先的帮助。

- eSamuel

---

如果您有这样的行：10=>Y; 10=>N; 10=>F; 10=>S; 10=>P，那么应该发生什么？在这种情况下应该显示什么？ - trailmax

如果有任何“是”值，答案应该是“是”，因此10 = Y。 - eSamuel

如果相同的 ID 可以有除 Y 或 N 以外的两个不同值，那怎么办？这样不是就违背了你们的无重复要求吗？ - Aaron Anodide

@Gabriel 这正是我想找出的。最近我也遇到了类似的问题，我向需要报告的用户提出了问题。他们花了很长时间才回复，最后取消了任务 -) 因此需要仔细考虑这个问题。 - trailmax

在OP的代码中，如果一个ID有一个“N”和一个“P”，那么将返回一个“N”。这似乎违反了“所有N按顺序为N”的规则。尝试添加以下行到样例中进行验证：INSERT INTO @tempTable (ID, Val) VALUES (18, 'N')。 - 8kb

Answer 1

让我们先解决一个更简单的问题：对于每个id，获取字母表中最后一个Val。如果Y和N是唯一的值，则此方法有效。查询语句也更简单：

SELECT t.ID, MAX(t.Val) FROM t GROUP BY t.ID;

因此，将您的情况简化为简单情况。 如果您的数据库支持，则使用枚举，或者将值代码拆分到另一个具有排序列的表中（在这种情况下，您可以将1用于“是”，2用于“否”，而999用于所有其他可能的值，并且您希望 最小 ）。 然后

SELECT ID, c.Val FROM
     (SELECT t.ID, MIN(codes.collation) AS mx
      FROM t join codes on t.Val = codes.Val GROUP BY t.ID) AS q
JOIN codes c ON mx=c.collation;

这里的代码有两列，Val和Collation。

如果你的值是按照你想要的顺序排序的，你也可以使用CTE类型的查询来实现此操作。这种方法只需要连接一个小的查找表，应该比3个自连接快得多。

WITH q AS (SELECT t.id, t.Val, ROW_NUMBER() AS r FROM t JOIN codes ON t.Val=codes.Val 
    PARTITION BY t.id ORDER BY codes.collation)
SELECT q.id, q.Val WHERE r=1;            

- Andrew Lazarus

Answer 2

我会将它改为以下内容，以使其更易于阅读：

SELECT DISTINCT t.ID, COALESCE(t2.Val, t3.Val, t4.Val)
FROM @tempTable t
LEFT JOIN @tempTable t2 ON t.ID = t2.ID and t2.Val = 'Y'
LEFT JOIN @tempTable t3 ON t.ID = t3.ID and  t3.Val = 'N'
LEFT JOIN @tempTable t4 ON t.ID = t4.ID and t4.Val <> 'Y' AND t4.Val <> 'N'

这个示例与您的示例产生相同的结果。

我还查看了两者的执行计划，它们完全相同，我怀疑您不会看到任何性能差异。

- Abe Miessler

Answer 3

试试这个：

;WITH a AS ( 
SELECT
  ID,
  SUM(CASE Val WHEN 'Y' THEN 1 ELSE 0 END) AS y,
  SUM(CASE Val WHEN 'N' THEN 0 ELSE 1 END) AS n,
  MIN(CASE WHEN Val IN ('Y','N') THEN NULL ELSE Val END) AS first_other
FROM @tempTable
GROUP BY ID
) 
SELECT
  ID,
  CASE WHEN y > 0 THEN 'Y' WHEN n = 0 THEN 'N' ELSE first_other END AS Val
FROM a 

• 如果有任何'Y'值，则y的总和将大于0
• 如果所有的值都是'N'，则n的总和将为零
• 如有需要，请获取第一个非'Y'或'N'字符
• 在这种情况下，只需通过一遍表即可确定结果

- 8kb

---

啊，我喜欢在 WITH 前面加上分号... 我总是会遇到那个错误 :) - Aaron Anodide

Answer 4

我正在阅读您的规范，如下所示：

1. 如果任何ID为Y，则为Y
2. 如果所有ID都为N，则为N
3. 否则显示值（除Y或N之外的其他值）

根据（1），删除行。

delete from @tempTable
where not Val='Y' and ID in (
    select distinct ID
    from @tempTable
    where Val='Y'
)

选择distinct以消除每个（2）中的多个N。

select distinct * from @tempTable

将各种“其他”值分组以获得每个ID的单行。

SELECT A.Id, AllVals = 
    SubString(
        (SELECT ', ' + B.Val 
         FROM C as B 
         WHERE A.Id = B.Id 
         FOR XML PATH ( '' ) ), 3, 1000) 
FROM C as A 
GROUP BY Id

完整可运行的查询：

declare @tempTable table (ID int, Val char(1))
INSERT INTO @tempTable ( ID, Val ) VALUES ( 10, 'Y') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 11, 'N') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 11, 'N') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 12, 'Y') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 12, 'Y') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 12, 'Y') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 13, 'N') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 14, 'Y') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 14, 'N') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 15, 'Y') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 16, 'Y') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 17, 'F') 
INSERT INTO @tempTable ( ID, Val ) VALUES ( 18, 'P')
INSERT INTO @tempTable ( ID, Val ) VALUES ( 18, 'F')
delete from @tempTable
where not Val='Y' and ID in (
    select distinct ID
    from @tempTable
    where Val='Y'
);
WITH C as (select distinct * from @tempTable)
SELECT A.Id, AllVals = 
    SubString(
        (SELECT ', ' + B.Val 
         FROM C as B 
         WHERE A.Id = B.Id 
         FOR XML PATH ( '' ) ), 3, 1000) 
FROM C as A 
GROUP BY Id

输出：

Id  AllVals
10  Y
11  N
12  Y
13  N
14  Y
15  Y
16  Y
17  F
18  F, P

- Aaron Anodide