SQL Server不同，选择所有行

Question

我在这个主题上看到了很多问题和答案，但我很难理解我在做什么错误，下面的查询获取我需要的所有信息，但是如果它有多行，当r_num是相同的，并且每个r_num的r_order是相同的，那么对于每个r_num我只有一个结果，现在虽然这个调用给了我错误，有人请向我解释Distinct是如何工作的，我该如何使用它在此查询实现我的目标是什么？

SELECT DISTINCT po_num, r_num, r_order, vendor, order_date, received_by, received_date FROM(
    SELECT p.id as po_num, r.id as r_num, r.rec_order as r_order, v.name as vendor, p.order_date, r.received_by, r.received_date 
    FROM Parts.dbo.po as p INNER JOIN 
    Parts.dbo.vendor as v ON v.id = p.vendor__id INNER JOIN 
    Parts.dbo.receipts as r ON r.id = 'R-'+CAST(p.id AS varchar) 
    GROUP BY p.id, r.id, r.rec_order, v.name, p.order_date, r.received_by, r.received_date) as tbl 
GROUP BY r_num, r_order 

以下是错误我得到

Msg 8120, Level 16, State 1, Line 1 Column 'tbl.po_num' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Answer 1

除非你在做聚合，否则你不需要同时拥有DISTINCT和GROUP BY。

SELECT DISTINCT po_num, r_num, r_order, vendor, order_date, received_by, received_date 
FROM 
    (
    SELECT p.id as po_num, r.id as r_num, r.rec_order as r_order, v.name as vendor, p.order_date, r.received_by, r.received_date 
    FROM Parts.dbo.po as p INNER JOIN 
    Parts.dbo.vendor as v ON v.id = p.vendor__id INNER JOIN 
    Parts.dbo.receipts as r ON r.id = 'R-'+CAST(p.id AS varchar) 
    GROUP BY p.id, r.id, r.rec_order, v.name, p.order_date, r.received_by, r.received_date 
) as tbl 

如果你想获得不同r_num和r_order值，则需要任何一组由/只选择两列：

SELECT r_num, r_order 
FROM 
    (
    SELECT p.id as po_num, r.id as r_num, r.rec_order as r_order, v.name as vendor, p.order_date, r.received_by, r.received_date 
    FROM Parts.dbo.po as p INNER JOIN 
    Parts.dbo.vendor as v ON v.id = p.vendor__id INNER JOIN 
    Parts.dbo.receipts as r ON r.id = 'R-'+CAST(p.id AS varchar) 
    GROUP BY p.id, r.id, r.rec_order, v.name, p.order_date, r.received_by, r.received_date 
) as tbl 
GROUP BY r_num, r_order 

或者，如果你的数据可以处理它，使用额外列上的聚合：

SELECT MAX(po_num) AS po_num, r_num, r_order, MAX(vendor) AS vendor -- etc. 
FROM 
    (
    SELECT p.id as po_num, r.id as r_num, r.rec_order as r_order, v.name as vendor, p.order_date, r.received_by, r.received_date 
    FROM Parts.dbo.po as p INNER JOIN 
    Parts.dbo.vendor as v ON v.id = p.vendor__id INNER JOIN 
    Parts.dbo.receipts as r ON r.id = 'R-'+CAST(p.id AS varchar) 
    GROUP BY p.id, r.id, r.rec_order, v.name, p.order_date, r.received_by, r.received_date 
) as tbl 
GROUP BY r_num, r_order