SQL技术问询：如何按count(*)排序并去重返回xid，优先展示yid=100的记录

原数据表 #

需求 #

返回按count(*)排序的xid，排序规则为先展示yid=100的xid，再展示yid≠100的xid，且每个xid仅出现一次。

预期结果 #

1000 (because yid = 100, count(*) = 3 )
1001 (because yid = 100, count(*) = 2 )
1002 (because yid = 100, count(*) = 1 )
1003 (because yid != 100, count(*) = 2 (even yid !=yid)

当前错误SQL（会重复返回xid） #

SELECT * FROM (
SELECT [xid], 
  [yid],
  count(1) as cnt 
FROM [fbfact].[Journal]
where [yid] = 1000 
group by [xid],[yid]
UNION
SELECT [xid],
   [yid],
   count(1) as cnt
FROM [fbfact].[Journal]
where [yid] != 1000
group by [xid],[yid] ) as x

正确SQL实现 #

方案一（带描述文本） #

SELECT 
    xid,
    CONCAT(
        'because yid ', 
        CASE WHEN has_yid_100 = 1 THEN '= 100' ELSE '!= 100' END, 
        ', count(*) = ', 
        CASE WHEN has_yid_100 = 1 THEN cnt_yid_100 ELSE total_cnt END
    ) AS description
FROM (
    SELECT 
        xid,
        -- 标记该xid是否存在yid=100的记录
        MAX(CASE WHEN yid = 100 THEN 1 ELSE 0 END) AS has_yid_100,
        -- 统计该xid下yid=100的记录数
        SUM(CASE WHEN yid = 100 THEN 1 ELSE 0 END) AS cnt_yid_100,
        -- 统计该xid的总记录数
        COUNT(*) AS total_cnt
    FROM [fbfact].[Journal]
    GROUP BY xid
) AS sub
-- 先排yid=100的xid，再按对应count降序
ORDER BY has_yid_100 DESC, 
         CASE WHEN has_yid_100 = 1 THEN cnt_yid_100 ELSE total_cnt END DESC;

方案二（仅返回xid及排序逻辑） #

如果只需要xid，不需要描述文本，可简化为：

SELECT xid
FROM (
    SELECT 
        xid,
        MAX(CASE WHEN yid = 100 THEN 1 ELSE 0 END) AS has_yid_100,
        SUM(CASE WHEN yid = 100 THEN 1 ELSE 0 END) AS cnt_yid_100,
        COUNT(*) AS total_cnt
    FROM [fbfact].[Journal]
    GROUP BY xid
) AS sub
ORDER BY has_yid_100 DESC, 
         CASE WHEN has_yid_100 = 1 THEN cnt_yid_100 ELSE total_cnt END DESC;

逻辑说明 #

1. 内层子查询按xid分组，计算三个核心值：
   • has_yid_100：标记该xid是否存在yid=100的记录（1为存在，0为不存在）
   • cnt_yid_100：统计该xid下yid=100的记录总数
   • total_cnt：统计该xid的所有记录总数
2. 外层查询根据标记值选择对应统计数，并生成符合预期的描述文本
3. 排序时优先展示存在yid=100的xid，再按对应统计数降序排列，确保每个xid唯一

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