如何查询2022年12月与2023年1月有支付记录的唯一用户信息？

现有两张MySQL表name和payment，需要获取所有在2022年12月或2023年1月有支付记录的唯一用户信息，包含对应月份的支付数据，但你写的查询没得到预期结果，下面是问题分析和正确的SQL写法。

表结构与数据 #

name表 #

SELECT * FROM name;
+-----+-----------+
| nid | person    |
+-----+-----------+
| 1   | Root      |
| 2   | Alex      |
| 3   | Mark      |
| 4   | Frank     |
| 5   | Christina |
| 6   | Kery      |
| 7   | Mikel     |
| 8   | Jams      |
| 9   | Lee       |
| 10  | Carlos    |
+-----+-----------+

payment表 #

SELECT * FROM payment;
+-----+-----+------+-------+---------+
| pid | nid | year | month | payment |
+-----+-----+------+-------+---------+
| 1   | 1   | 2023 | 1     | 10      |
| 2   | 2   | 2023 | 1     | 20      |
| 3   | 3   | 2023 | 1     | 20      |
| 4   | 4   | 2023 | 1     | 30      |
| 5   | 5   | 2023 | 1     | 15      |
| 6   | 1   | 2023 | 2     | 10      |
| 7   | 2   | 2023 | 2     | 20      |
| 8   | 6   | 2023 | 2     | 20      |
| 9   | 8   | 2023 | 2     | 20      |
| 10  | 9   | 2023 | 2     | 20      |
| 11  | 10  | 2023 | 2     | 50      |
| 12  | 2   | 2022 | 12    | 20      |
| 13  | 3   | 2022 | 12    | 20      |
| 14  | 4   | 2022 | 12    | 30      |
| 15  | 8   | 2022 | 12    | 20      |
| 16  | 9   | 2022 | 12    | 20      |
| 17  | 10  | 2022 | 12    | 50      |
+-----+-----+------+-------+---------+

原查询的问题 #

你写的SQL有几个问题导致结果不符合预期：

• 表名写错了：FROM person里的person应该是name
• 用逗号连接name和payment会产生笛卡尔积，导致数据重复混乱
• 两个子查询都没选nid字段，根本没法和用户表关联
• WHERE name.nid=payment.nid这个条件会过滤掉只有其中一个月份记录的用户（比如只有2022年12月记录的Jams）

正确的SQL写法 #

这里提供两种可行的写法，都能得到你要的结果：

写法一：LEFT JOIN筛选后的子查询 #

这种方式逻辑清晰，适合新手理解：

SELECT 
    n.person AS name,
    jan.year AS yearJan,
    jan.month AS Jan,
    jan.payment AS paymentJan,
    dec.year AS yearDec,
    dec.month AS Dec,
    dec.payment AS paymentDec
FROM name n
LEFT JOIN (
    -- 筛选2023年1月的支付记录，保留nid用于关联
    SELECT nid, year, month, payment 
    FROM payment 
    WHERE year = '2023' AND month = '1'
) jan ON n.nid = jan.nid
LEFT JOIN (
    -- 筛选2022年12月的支付记录，保留nid用于关联
    SELECT nid, year, month, payment 
    FROM payment 
    WHERE year = '2022' AND month = '12'
) dec ON n.nid = dec.nid
-- 只保留至少有一个月份记录的用户
WHERE jan.nid IS NOT NULL OR dec.nid IS NOT NULL
ORDER BY name;

写法二：条件聚合（更简洁高效） #

用CASE语句聚合指定月份的数据，避免多次JOIN：

SELECT 
    n.person AS name,
    MAX(CASE WHEN p.year='2023' AND p.month='1' THEN p.year END) AS yearJan,
    MAX(CASE WHEN p.year='2023' AND p.month='1' THEN p.month END) AS Jan,
    MAX(CASE WHEN p.year='2023' AND p.month='1' THEN p.payment END) AS paymentJan,
    MAX(CASE WHEN p.year='2022' AND p.month='12' THEN p.month END) AS Dec,
    MAX(CASE WHEN p.year='2022' AND p.month='12' THEN p.year END) AS yearDec,
    MAX(CASE WHEN p.year='2022' AND p.month='12' THEN p.payment END) AS paymentDec
FROM name n
JOIN payment p ON n.nid = p.nid
-- 先过滤出目标月份的记录
WHERE (p.year='2023' AND p.month='1') OR (p.year='2022' AND p.month='12')
-- 按用户分组，聚合每个用户的月份数据
GROUP BY n.nid, n.person
ORDER BY name;

预期执行结果 #

+-----------+---------+------+-----------+-----+-------+------------+
| name      | yearJan | Jan  | paymentJan| Dec | yearDec| paymentDec|
+-----------+---------+------+-----------+-----+-------+------------+
| Alex      | 2023    | 1    | 20        | 12  | 2022  | 20         |
| Carlos    | NULL    | NULL | NULL      | 12  | 2022  | 50         |
| Christina | 2023    | 1    | 15        | NULL| NULL  | NULL       |
| Frank     | 2023    | 1    | 30        | 12  | 2022  | 30         |
| Jams      | NULL    | NULL | NULL      | 12  | 2022  | 20         |
| Lee       | NULL    | NULL | NULL      | 12  | 2022  | 20         |
| Mark      | 2023    | 1    | 20        | 12  | 2022  | 20         |
| Root      | 2023    | 1    | 10        | NULL| NULL  | NULL       |
+-----------+---------+------+-----------+-----+-------+------------+

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