如何编写SQL查询筛选同一userclass与category下的多支付日期记录

原始支付数据表格 #

需求与现有问题 #

需要筛选出**同一userclass且同一category下存在多个不同paydate**的记录（即PayID为90、91、94、95的条目）。目前使用以下SQL仅能获取全部数据，无法完成筛选：

SELECT
  p.payID,
  p.userclass,
  pc.category,
  p.paydate
FROM
  pay p 
    INNER JOIN paycategory pc
       ON p.categoryID = pc.categoryID

期望输出结果 #

解决方案 #

方法1：使用窗口函数（高效简洁） #

利用COUNT(DISTINCT paydate)窗口函数，计算每个userclass+category分组下的不同支付日期数量，筛选数量大于1的记录：

SELECT
  payID,
  userclass,
  category,
  paydate
FROM (
  SELECT
    p.payID,
    p.userclass,
    pc.category,
    p.paydate,
    COUNT(DISTINCT p.paydate) OVER (PARTITION BY p.userclass, pc.category) AS date_count
  FROM
    pay p 
    INNER JOIN paycategory pc ON p.categoryID = pc.categoryID
) AS subquery
WHERE date_count > 1;

方法2：使用GROUP BY子查询关联 #

先找出符合条件的userclass+category组合，再关联原表获取对应记录，适合不支持窗口函数的旧版SQL数据库：

SELECT
  p.payID,
  p.userclass,
  pc.category,
  p.paydate
FROM
  pay p 
  INNER JOIN paycategory pc ON p.categoryID = pc.categoryID
  INNER JOIN (
    SELECT
      p.userclass,
      pc.category
    FROM
      pay p 
      INNER JOIN paycategory pc ON p.categoryID = pc.categoryID
    GROUP BY
      p.userclass, pc.category
    HAVING
      COUNT(DISTINCT p.paydate) > 1
  ) AS valid_groups ON p.userclass = valid_groups.userclass AND pc.category = valid_groups.category;

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