提问人：user1613245 提问时间：11/7/2023 最后编辑：Thom Auser1613245 更新时间：11/7/2023 访问量：42
对同一表中的 2 个不同条件和列进行 SQL 查询
SQL query for 2 different conditions and columns from same table
问：
我有一个“交易”表，如下所示：
| 编号 | user_id | 量 | transaction_mode | created_date |
|---|---|---|---|---|
| 1 | 1 | 55 | 参加比赛 | 2023-10-07 |
| 2 | 1 | 25 | 为了赢得比赛 | 2023-10-07 |
| 3 | 2 | 33 | 参加比赛 | 2023-10-12 |
| 4 | 2 | 10 | 为了赢得比赛 | 2023-10-12 |
| 5 | 2 | 58 | 参加比赛 | 2023-10-26 |
| 6 | 2 | 22 | 参加比赛 | 2023-10-26 |
| 7 | 1 | 40 | 为了赢得比赛 | 2023-10-26 |
| 8 | 1 | 10 | 为了赢得比赛 | 2023-10-26 |
因此，表中的“参加比赛”模式是客户为参加比赛免费支付了参赛费用。比赛结束后，只有少数人是获胜者，这是“赢得比赛”的模式。因此，我正在尝试计算每天参加比赛的金额以及每天赢得比赛的金额。
因此，对“参加比赛”的查询为：
SELECT SUM(amount) AS entry_amount, created_date AS date
FROM transaction
WHERE transaction_mode = 'Joined the Contest'
GROUP BY CAST(created_date AS date);
赢得比赛的查询是：
SELECT SUM(amount) AS credited_amount, created_date AS date
FROM transaction
WHERE transaction_mode = 'For Winning the Contest'
GROUP BY CAST(created_date AS date);
但是我想像这样将此输出合二为一：
| entry_amount | credited_amount | 日期 |
|---|---|---|
| 55 | 25 | 2023-10-07 |
| 33 | 10 | 2023-10-12 |
| 80 | 50 | 2023-10-26 |
但是我想通过单个 SQL 查询获取此输出，我首先尝试了这个：
SELECT SUM(amount) AS entry_amount, created_date AS date
FROM transaction
WHERE transaction_mode = 'Joined the Contest'
UNION ALL
SELECT SUM(amount) AS credited_amount, created_date AS date
FROM transaction
WHERE transaction_mode = 'For Winning the Contest'
GROUP BY CAST(created_date AS date);
但此 SQL 查询仅返回条目金额，而不返回credited_amount。
答：
1赞
SelVazi
11/7/2023
#1
这可以使用条件聚合来完成：
SELECT SUM(CASE WHEN transaction_mode = 'Joined the Contest' THEN amount END) as entry_amount,
SUM(CASE WHEN transaction_mode = 'For Winning the Contest' THEN amount END) as credited_amount,
created_date as [date]
FROM transactions
GROUP BY created_date
评论
...sum(case when transaction_mode = 'Joined' then amount END), sum(case when transaction_mode = 'Won' then amount END)...