如何处理重复在SQL的UNION ALL SELECT语句
问题描述：
举个例子，如果有：中如何处理重复在SQL的UNION ALL SELECT语句
SELECT '[email protected]' AS Email, 2 AS Somenumber, 3 AS Number
UNION ALL
SELECT '[email protected]' AS Email, 2 AS Somenumber, 5 AS Number
UNION ALL
SELECT '[email protected]' AS Email, 1 AS Somenumber, 6 AS Number
代替：
我想：
答
SELECT Email, Somenumber, Number
FROM (
SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY Email ORDER BY Number DESC)
FROM (
VALUES
('[email protected]', 2, 3),
('[email protected]', 2, 5),
('[email protected]', 1, 6)
) t(Email, Somenumber, Number)
) t
WHERE RowNum = 1
输出 -
Email Somenumber Number
------- ----------- -----------
[email protected] 2 5
[email protected] 1 6
答
它看起来像你在每个电子邮件后一行金正日。你可以这样做：
; with all_rows as
(
... your union query here ...
)
, with numbered_rows as
(
select row_number() over (partition by email order by somenumber) as rn
, *
from all_rows
)
select email
, somenumber
, number
from numbered_rows
WHERE rn = 1
答
通常当我需要解决这样的问题，我将与以上只是我的脑海Devart解决方案相同的方式 工作，但这里给出更好的执行计划的替代解决方案
select Email, max(Somenumber), max(Number) from (
SELECT '[email protected]' AS Email, 2 AS Somenumber, 3 AS Number
UNION ALL
SELECT '[email protected]' AS Email, 2 AS Somenumber, 5 AS Number
UNION ALL
SELECT '[email protected]' AS Email, 1 AS Somenumber, 6 AS Number
) A
group by Email
我不明白这个问题。只是不要在“union all”中包含第一行。 –
假设这是样本数据，您如何决定要保留哪些“重复”，“number = 3”还是“number = 5”？ – HoneyBadger
这一切都取决于电子邮件地址，我想只返回没有电子邮件重复的行 – Wicky