我是 MySQL 的新手，我正在努力實作以下目標。
從以下兩個用戶之間的交易表中，撰寫一個查詢以回傳每個用戶的凈值變化，按凈變化遞減排序。
transactions1
-------- ---------- -------- ------------------
| sender | receiver | amount | transaction_date |
-------- ---------- -------- ------------------
| 5 | 2 | 10 | 2-12-20 |
| 1 | 3 | 15 | 2-13-20 |
| 2 | 1 | 20 | 2-13-20 |
| 2 | 3 | 25 | 2-14-20 |
| 3 | 1 | 20 | 2-15-20 |
| 3 | 2 | 15 | 2-15-20 |
| 1 | 4 | 5 | 2-16-20 |
-------- ---------- -------- ------------------
從這里我創建了兩個視圖，一個是發送了一些東西的用戶和發送的總數，另一個是收到了一些東西的用戶和收到的總數。用戶 5 沒有收到任何東西，用戶 4 沒有發送任何東西，所以他們分別不在接收/發送視圖中。
sent
-------- ------------
| sender | total_sent |
-------- ------------
| 1 | 20 |
| 2 | 45 |
| 3 | 35 |
| 5 | 10 |
-------- ------------
received
---------- ----------------
| receiver | total_received |
---------- ----------------
| 1 | 40 |
| 2 | 25 |
| 3 | 40 |
| 4 | 5 |
---------- ----------------
我正在嘗試添加兩個用戶，因此我們有一個完整的用戶串列，并從 total_received 中減去 total_sent，但我沒有得到完整的用戶串列。我的結果中缺少不在兩個表中的用戶，我不確定如何以不同的方式處理這個問題。
SELECT coalesce(sender,receiver) AS 'user',
coalesce(total_received,0) - coalesce(total_sent,0) AS 'net_change'
FROM sent s
JOIN received r
ON s.sender = r.receiver
ORDER BY 2 DESC;
RESULT:
------ ------------
| user | net_change |
------ ------------
| 1 | 20 |
| 3 | 5 |
| 2 | -20 |
------ ------------
任何幫助都受到高度贊賞。
uj5u.com熱心網友回復：
您可以先對已發送和已接收的聚合執行聯合，而不是執行連接，然后對結果聯合執行 group by 以獲取結果。這里的關鍵是 total_sent 求和為負值。
選項 1：在確定之前創建發送和接收的聯合 net_change
SELECT
`user`,
SUM(`change`) as net_change
FROM (
SELECT sender as `user`, total_sent*-1 as `change` FROM sent UNION ALL
SELECT receiver as `user`, total_received as `change` FROM received
) t
GROUP BY
`user`
ORDER BY
SUM(`change`) DESC;
| 用戶 | 凈變化 |
|---|---|
| 1 | 20 |
| 3 | 5 |
| 4 | 5 |
| 5 | -10 |
| 2 | -20 |
選項 2：net_change從初始表中確定
SELECT
`user`,
SUM(`change`) as net_change
FROM (
SELECT sender as `user`, amount*-1 as `change` FROM transactions1 UNION ALL
SELECT receiver as `user`, amount as `change` FROM transactions1
) t
GROUP BY
`user`
ORDER BY
SUM(`change`) DESC;
| 用戶 | 凈變化 |
|---|---|
| 1 | 20 |
| 3 | 5 |
| 4 | 5 |
| 5 | -10 |
| 2 | -20 |
在 DB Fiddle 上查看
讓我知道這是否適合您。
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/330803.html
標籤：mysql
下一篇：在gitpod上登錄mysql