例如,我有2个表:
用户
+----+
| id |
+----+
| 0 |
| 1 |
| 2 |
| 3 |
+----+
付款
+---------+--------+------+
| user_id | amount | case |
+---------+--------+------+
| 0 | 10 | 1 |
| 0 | 1 | 2 |
| 2 | 5 | 1 |
| 2 | 4 | 1 |
| 2 | 5 | 2 |
| 3 | 26 | 2 |
+---------+--------+------+
我正在尝试获得输出:
+---------+---------------------+---------------------+
| user_id | total_amount_case_1 | total_amount_case_2 |
+---------+---------------------+---------------------+
| 0 | 10 | 1 |
| 2 | 9 | 5 |
| 3 | 0 | 26 |
+---------+---------------------+---------------------+
用文字解释我试图选择每个至少有一个付款的用户,并在单独的情况下将其付款金额相加。
我只是可以达到选择每个用户总付款的地步,但不取决于它是什么情况:
SELECT users.id AS user_id, SUM(payments.amount) AS total_amount FROM users LEFT JOIN users.id = payments.user_id GROUP BY user_id ORDER BY user_id;
这给了我这个输出:
+---------+--------------+
| user_id | total_amount |
+---------+--------------+
| 0 | 11 |
| 1 | 0 |
| 2 | 14 |
| 3 | 26 |
+---------+--------------+
你可以使用条件聚合来做到这一点——在聚合函数中使用 case
语句:
SELECT u.id AS user_id,
SUM(case when p.`case` = 1 then p.amount else 0 end) AS total_amount_case1,
SUM(case when p.`case` = 2 then p.amount else 0 end) AS total_amount_case2
FROM users u LEFT JOIN
payments p
on u.id = p.user_id
GROUP BY user_id
ORDER BY user_id;
我还添加了表别名,使查询不那么麻烦。
试试这个
SELECT
u.id AS user_id,
SUM(CASE WHEN p.`case` = 1 THEN p.amount ELSE 0 END) AS total_amount_case_1,
SUM(CASE WHEN p.`case` = 2 THEN p.amount ELSE 0 END) AS total_amount_case_2
FROM
users u
LEFT JOIN payments p ON u.id = p.user_id
GROUP BY u.user_id
ORDER BY u.user_id ;
SELECT users.id As user_id
, Sum(CASE WHEN payments.case = 1 THEN payments.amount ELSE 0 END) As total_amount_case_1
, Sum(CASE WHEN payments.case = 2 THEN payments.amount ELSE 0 END) As total_amount_case_2
FROM users
LEFT
JOIN payments
ON users.id = payments.user_id
GROUP
BY user_id
ORDER
BY user_id;
我会做一些事情:
SELECT users.id
,SUM(CASE WHEN payments.[case] = 1 THEN total_amount_case_1 ELSE 0 END) as total_amount_case_1
,SUM(CASE WHEN payments.[case] = 2 THEN total_amount_case_2 ELSE 0 END) as total_amount_case_2
FROM users INNER JOIN payments
ON users.id = payments.user_id
GROUP BY payments.user_id
SELECT users.id AS user_id,
SUM(case when Payments.Case = 1 then payments.amount else 0 End) AS total_amount_case1,
SUM(case when Payments.Case = 2 then payments.amount else 0 End) AS total_amount_case2
FROM users LEFT JOIN payments on
users.id = payments.user_id
GROUP BY users.id
ORDER BY users.id;