如何改善此SQL查询？

Question

今天，我遇到了一个有趣的SQL问题，尽管我想出了一个行之有效的解决方案，但我怀疑这是最佳还是最有效的答案。在这里，我请专家-
帮助我学习一些知识并改善查询条件！RDBMS是SQL Server 2008 R2，查询是SSRS报告的一部分，该报告将针对约100,000行运行。

本质上，我有一个ID列表，该ID可能具有多个与之关联的值，这些值是Yes，No或其他字符串。对于ID
x，如果任何值是“是”，则x应该是“是”，如果它们全都是“否”，则它应该是“否”，如果它们包含任何其他值但是和否，则显示该值。我只想为每个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

提前致谢。

Answer 1

让我们回答一个更简单的问题：对于每个id，获取字母中最后一个的Val。如果Y和N是唯一的值，这将起作用。而且查询要简单得多：

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

因此，将您的情况简化为简单的情况。使用枚举（如果数据库支持），或者将值代码分成带有排序规则列的另一个表（在这种情况下，Y可以有1个，N可以有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和Collat​​ion。

您也可以使用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;