SQL Server：SELECT DISTINCT搭配ORDER BY报错求助

The error you're hitting makes sense once you understand the rule: when using SELECT DISTINCT, every column or expression in your ORDER BY clause has to be included in the select list. This is because DISTINCT filters out duplicate rows, and the database needs to guarantee the sorting value exists for every unique row in your final result set.

In your query, the ORDER BY uses a computed CASE expression that isn't part of your SELECT DISTINCT P1.* list—that's exactly why you're seeing the error. Here are two simple, effective fixes:

Solution 1: Add the Sort Expression to Your Select List #

Include the CASE expression in your select list (with an alias) so it's part of the distinct set, then order by that alias. This works great if you don't mind the extra column in your output:

SELECT DISTINCT 
    P1.*,
    CASE WHEN P1.BASEPRDID = 0 THEN P1.PRDNAME ELSE P2.PRDNAME END AS SortName
FROM T_PRD P1 
LEFT JOIN T_PRD P2 ON P1.baseprdid = P2.prdid 
INNER JOIN T_PRD_NM_VENDOR prdNmVendor ON P1.prdId = prdNmVendor.prdId 
INNER JOIN T_VENDOR_NM vendorNM ON prdNmVendor.vendorNMId = vendorNM.vendorNMId 
INNER JOIN T_NM nm ON vendorNM.NMId = nm.NMId 
INNER JOIN T_PRD_VENDOR prdVendor ON prdVendor.PRDId = P1.PRDId 
INNER JOIN T_VENDOR vendor ON prdVendor.vendorId = vendor.vendorId 
INNER JOIN T_CSTMR_PRD_REF custPrd ON custPrd.ProductId = P1.PRDId 
INNER JOIN T_CSTMR cstmr ON custPrd.ChennelCstrId = cstmr.cstmrid 
WHERE 1 = 1 
AND P1.Lifecycle = 2 
AND P1.AutoCreated = 0 
AND vendor.vendorId = 1 
AND P1.VendorEnfId = 1 
AND cstmr.cstmrid = 2008 
ORDER BY SortName ASC, P1.BASEPRDID;

Solution 2: Use GROUP BY Instead of DISTINCT (Cleaner Output) #

If you want to keep your original output structure (no extra sort column), replace DISTINCT with GROUP BY. Assuming PRDId is the primary key of T_PRD, you only need to group by this key (all other columns in P1 are functionally dependent on it). This lets you use the CASE expression in ORDER BY without modifying your select list:

SELECT P1.*
FROM T_PRD P1 
LEFT JOIN T_PRD P2 ON P1.baseprdid = P2.prdid 
INNER JOIN T_PRD_NM_VENDOR prdNmVendor ON P1.prdId = prdNmVendor.prdId 
INNER JOIN T_VENDOR_NM vendorNM ON prdNmVendor.vendorNMId = vendorNM.vendorNMId 
INNER JOIN T_NM nm ON vendorNM.NMId = nm.NMId 
INNER JOIN T_PRD_VENDOR prdVendor ON prdVendor.PRDId = P1.PRDId 
INNER JOIN T_VENDOR vendor ON prdVendor.vendorId = vendor.vendorId 
INNER JOIN T_CSTMR_PRD_REF custPrd ON custPrd.ProductId = P1.PRDId 
INNER JOIN T_CSTMR cstmr ON custPrd.ChennelCstrId = cstmr.cstmrid 
WHERE 1 = 1 
AND P1.Lifecycle = 2 
AND P1.AutoCreated = 0 
AND vendor.vendorId = 1 
AND P1.VendorEnfId = 1 
AND cstmr.cstmrid = 2008 
GROUP BY P1.PRDId -- Assuming PRDId is T_PRD's primary key
ORDER BY CASE WHEN P1.BASEPRDID = 0 THEN P1.PRDNAME ELSE P2.PRDNAME END ASC, P1.BASEPRDID;

If your database doesn't support grouping by just the primary key (e.g., older SQL Server versions), you'll need to list all columns from P1 in the GROUP BY clause. It's tedious, but it achieves the same result without adding extra columns to your output.

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