如何修改SQL查询仅获取唯一ID？求Distinct实现方案

现有数据集 #

Order_Table #

SELECT *
INTO Order_Table
FROM (VALUES
   (1,          456,       'repair',       'House'),
   (2,          456,        'paint',       'House'),
   (3,          678,        'repair',      'Fence'),
   (4,          789,        'repair',      'House'),
   (5,          789,        'paint',       'House'),
   (6,          789,        'repair',      'Fence'),
   (7,          789,        'paint',       'Fence')
   )
v (OrderNum,    CustomerNum, OrderDesc,   Structure)

Veg_Table #

SELECT *
INTO Veg_Table
FROM (VALUES
   (1,       '12/01/2020'),
   (2,       '12/02/2020'),
   (3,       '12/03/2020'),
   (4,       '12/04/2020'),
   (5,       '12/05/2020'),
   (6,       '12/06/2020'),
   (7,       '12/07/2020'),
   (1,       '12/10/2020'),
   (2,       '12/11/2020'),
   (3,       '12/12/2020')
   )
v (ID,   MyDate)

原查询语句 #

Select Distinct CTE.ID, *
From (
Select *
From Order_Table as Hist
Inner Join Veg_Table As Veg
On Hist.OrderNum = Veg.ID) as CTE

问题说明 #

上述查询返回重复的ID，尝试过Where In (Select Distinct ID From Event_View)也无效。期望得到每个ID唯一的结果集，如下：

OrderNum    CustomerNum    OrderDesc    Structure   ID    MyDate
1           456            repair       House       1     12/1/2020
2           456            paint        House       2     12/2/2020
3           678            repair       Fence       3     12/3/2020
4           789            repair       House       4     12/4/2020
5           789            paint        House       5     12/5/2020
6           789            repair       Fence       6     12/6/2020
7           789            paint        Fence       7     12/7/2020

已知可以用Row_Number() Over (Partition By ID)实现，但希望用更简单的DISTINCT方案解决，请问该如何修改查询？

解决方案 #

问题出在SELECT DISTINCT CTE.ID, *的写法上：*会包含所有字段，包括重复的MyDate值，而DISTINCT是对整行所有字段去重，不是只针对ID。因为同一个ID对应不同的MyDate，所以整行是不同的，DISTINCT无法消除这些重复ID的行。

要实现需求，你需要先对Veg_Table按ID去重（只保留每个ID对应的一条MyDate，比如最早的），再和Order_Table关联，具体方案如下：

方案1：PostgreSQL适用（用DISTINCT ON去重） #

SELECT Hist.*, Veg.ID, Veg.MyDate
FROM Order_Table AS Hist
INNER JOIN (
    SELECT DISTINCT ON (ID) ID, MyDate
    FROM Veg_Table
    ORDER BY ID, MyDate -- 保留最早的日期，可根据需求调整排序规则
) AS Veg ON Hist.OrderNum = Veg.ID

方案2：通用SQL（用GROUP BY去重） #

如果是SQL Server等不支持DISTINCT ON的数据库，改用GROUP BY聚合取每个ID的目标日期：

SELECT Hist.*, Veg.ID, Veg.MyDate
FROM Order_Table AS Hist
INNER JOIN (
    SELECT ID, MIN(MyDate) AS MyDate -- 取最早日期，也可换MAX取最晚
    FROM Veg_Table
    GROUP BY ID
) AS Veg ON Hist.OrderNum = Veg.ID

方案3：直接关联后用GROUP BY替代DISTINCT #

也可以直接在关联后通过分组聚合实现唯一ID的结果：

SELECT 
    Hist.OrderNum, 
    Hist.CustomerNum, 
    Hist.OrderDesc, 
    Hist.Structure, 
    Veg.ID, 
    MIN(Veg.MyDate) AS MyDate
FROM Order_Table AS Hist
INNER JOIN Veg_Table AS Veg ON Hist.OrderNum = Veg.ID
GROUP BY Hist.OrderNum, Hist.CustomerNum, Hist.OrderDesc, Hist.Structure, Veg.ID

核心逻辑是：必须先消除Veg_Table中同一个ID的多条记录，或者通过聚合函数将同一个ID的多条记录合并为一条，这样关联后才不会出现重复ID的行，最终得到符合预期的唯一ID结果集。

内容的提问来源于stack exchange，提问作者ASH