使用INNER JOIN和ORDER BY newid()时SELECT DISTINCT失效求助

你的问题核心是直接对所有符合条件的葡萄酒随机排序取前4，而同一个酒庄可能有多款酒满足筛选条件，所以很容易抽到同一家的酒。要实现“4款来自不同酒庄的随机葡萄酒”，得先随机选出4个符合要求的酒庄，再从每个酒庄里挑一款随机的葡萄酒，具体做法如下：

方法一：CTE+ROW_NUMBER()实现 #

WITH RandomWineries AS (
    -- 第一步：随机筛选出4个符合条件的唯一酒庄ID
    SELECT TOP(4) a.ID_Winery
    FROM Wineries a
    INNER JOIN Wines w ON a.ID_Winery = w.ID_Winery
    WHERE w.appellation = 'Lorem Ipsum'
        AND a.weblogin != ''
        AND w.delete_wine != 1
        AND a.Winery = 1
        AND a.WebSiteUrl != ''
        AND a.okWinery = 'yes'
        AND w.Description_brief LIKE '%Lorem Ipsum%'
    GROUP BY a.ID_Winery
    ORDER BY NEWID()
),
WineryRandomWine AS (
    -- 第二步：给每个选中酒庄的葡萄酒随机排序，取第一款
    SELECT 
        w.ID_Winery, 
        w.appellation, 
        w.delete_wine, 
        w.Description_brief, 
        w.wine_vintage,
        a.weblogin, 
        a.Winery, 
        a.WebSiteUrl,
        ROW_NUMBER() OVER (PARTITION BY w.ID_Winery ORDER BY NEWID()) AS rn
    FROM Wines w
    INNER JOIN Wineries a ON w.ID_Winery = a.ID_Winery
    INNER JOIN RandomWineries rw ON w.ID_Winery = rw.ID_Winery
    WHERE w.appellation = 'Lorem Ipsum'
        AND a.weblogin != ''
        AND w.delete_wine != 1
        AND a.Winery = 1
        AND a.WebSiteUrl != ''
        AND a.okWinery = 'yes'
        AND w.Description_brief LIKE '%Lorem Ipsum%'
)
-- 第三步：提取每个酒庄的第一款随机酒
SELECT 
    ID_Winery, 
    appellation, 
    delete_wine, 
    Description_brief, 
    wine_vintage,
    weblogin, 
    Winery, 
    WebSiteUrl
FROM WineryRandomWine
WHERE rn = 1

方法二：嵌套子查询实现 #

如果不想用CTE，也可以用嵌套子查询完成：

SELECT 
    w.ID_Winery, 
    w.appellation, 
    w.delete_wine, 
    w.Description_brief, 
    w.wine_vintage,
    a.weblogin, 
    a.Winery, 
    a.WebSiteUrl
FROM (
    -- 先筛选4个随机合规酒庄
    SELECT TOP(4) ID_Winery
    FROM Wineries
    WHERE Winery = 1
        AND weblogin != ''
        AND WebSiteUrl != ''
        AND okWinery = 'yes'
        AND EXISTS (
            -- 确保该酒庄有符合条件的葡萄酒
            SELECT 1 FROM Wines 
            WHERE ID_Winery = Wineries.ID_Winery
                AND appellation = 'Lorem Ipsum'
                AND delete_wine != 1
                AND Description_brief LIKE '%Lorem Ipsum%'
        )
    ORDER BY NEWID()
) AS RandomWineries
INNER JOIN (
    -- 给每个酒庄的酒随机排序，取第一款
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY ID_Winery ORDER BY NEWID()) AS rn
    FROM Wines
    WHERE appellation = 'Lorem Ipsum'
        AND delete_wine != 1
        AND Description_brief LIKE '%Lorem Ipsum%'
) AS w ON RandomWineries.ID_Winery = w.ID_Winery AND w.rn = 1
INNER JOIN Wineries a ON w.ID_Winery = a.ID_Winery

为什么你之前的方法无效？ #

• 加DISTINCT没用：不同葡萄酒的字段（比如年份、简介）存在差异，整行数据是唯一的，DISTINCT无法过滤同酒庄的重复项。
• 直接GROUP BY酒庄ID：GROUP BY要求非聚合字段必须包含在分组条件里，无法同时保留单款葡萄酒的详细信息，不符合需求。

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