sql - 错误 : ORDER BY items must appear in the select list if SELECT DISTINCT is specified

Question

我收到错误:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

对于此查询:

SELECT DISTINCT 
    [Theme].[Name], [ThemeType].[Type] 
FROM 
    [Theme] 
LEFT OUTER JOIN 
    [ThemeType] ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
JOIN 
    [ProductTheme] ON [ProductTheme].[ThemeId]=[Theme].[PK_Theme]
WHERE 
    ProductTheme.ProductID LIKE '%' 
    AND ProductTheme.ThemeId = Theme.PK_Theme 
    AND COALESCE([THEME].[THEMETYPEID], 'null') LIKE '%[0-9]%' 
ORDER BY 
    CASE WHEN [THEMETYPE].[TYPE] IS NULL 
           THEN 0 
           ELSE 1 
    END, [THEMETYPE].[TYPE]

我已经调查过了，没有定义案例似乎是一个常见问题，但我确实这样做了。我做错了什么？

Answer 1

不要使用 SELECT DISTINCT，而是使用 GROUP BY(匹配注释中指定的排序顺序):

SELECT 
    [Theme].[Name], [ThemeType].[Type] 
FROM 
    [Theme] 
LEFT OUTER JOIN 
    [ThemeType] ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
JOIN 
    [ProductTheme] ON [ProductTheme].[ThemeId]=[Theme].[PK_Theme]
WHERE 
    ProductTheme.ProductID LIKE '%' 
    AND ProductTheme.ThemeId = Theme.PK_Theme 
    AND COALESCE([THEME].[THEMETYPEID], 'null') LIKE '%[0-9]%' 
GROUP BY
    [Theme].[Name], [ThemeType].[Type] 
ORDER BY 
    CASE WHEN [ThemeType].[Type] IS NULL 
           THEN 0 
           ELSE 1 
    END, [Theme].[Name]