我在資料庫中有一個表，如下所示：
| 跨日期 | 參考 | 總價 |
|---|---|---|
| 2022-11-03 | 003 | 200 |
| 2022-11-05 | 003 | 200 |
| 2022-11-08 | 002 | 200 |
| 2022-10-16 | 001 | 150 |
我嘗試了一些編碼行，下面是其中之一
SELECT YEAR(transDate) as year,
MONTH(transDate) as month,
SUM(DISTINCT total_price) as sum_of_total_price
FROM table
GROUP BY YEAR(transDate), MONTH(transDate)
ORDER BY YEAR(transDate), MONTH(transDate) ASC
我想total_price對每個 ref 003,002 和 001 求和。但對于 ref:003，我只想取 200，只是因為重復值。
我想按年份和日期顯示的最終結果如下所示。
| 年/日期 | sum_of_total_price |
|---|---|
| 2022-11 | 400 |
| 2022-10 | 150 |
非常感謝您的幫助。tq
uj5u.com熱心網友回復：
使用子查詢來獲取不同的值，然后在外部查詢上進行聚合。
select mt.transDate,
sum(mt.total_price) as sum_of_total_price
from (select transDate,
ref,
total_price
from my_table
group by transDate,ref,total_price
) as mt
group by transDate;
https://dbfiddle.uk/dDNDFBjv
編輯. OP更改了問題的邏輯，我將留下以前的答案并在下面添加更新的查詢：
select transDate ,
sum(mt.total_price) as sum_of_total_price
from ( select DATE_FORMAT(transDate, '%Y-%m') as transDate ,
ref,
total_price
from my_table
group by DATE_FORMAT(transDate, '%Y-%m'),ref,total_price
) as mt
group by transDate;
https://dbfiddle.uk/t5ydbLTV
Edit2 , Emulate ROW_NUMBER , 全部歸功于Sebastian Brosch
select DATE_FORMAT(transDate, '%Y-%m') as transDate ,
sum(mt.total_price) as sum_of_total_price
from ( SELECT *,
IF(@prev <> ref, @rn:=0,@rn), @prev:=ref, @rn:=@rn 1 AS rn
FROM my_table, (SELECT @rn:=0) rn, (SELECT @prev:='') prev
ORDER BY transDate ASC
) mt
WHERE rn = 1
group by DATE_FORMAT(transDate, '%Y-%m');
https://dbfiddle.uk/6aASUamZ
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/533156.html
標籤：mysql