查看完整版本: mysql過濾條件加總問題

論壇 › 電腦程式設計 › mysql過濾條件加總問題

ayabreay1 發表於 2022-11-18 06:33 PM

mysql過濾條件加總問題

想請教各位大大，我要如何從以下表下sql抓取最後10筆00:20的keyboard的數量加總?


日期時間欄位數量
2022/11/1700:03keyboard88
2022/11/1700:03mouse77
2022/11/1700:03trumpet66
2022/11/1700:03screen55
2022/11/1700:20keyboard99
2022/11/1700:20mouse66
2022/11/1700:20trumpet55
2022/11/1700:20screen33
2022/11/1700:40keyboard22
2022/11/1700:40mouse11
2022/11/1700:40trumpet33
2022/11/1700:40screen44
2022/11/1700:55keyboard55
2022/11/1700:55mouse66
2022/11/1700:55trumpet77
2022/11/1700:55screen88
2022/11/1800:03keyboard88
2022/11/1800:03mouse77
2022/11/1800:03trumpet66
2022/11/1800:03screen55
2022/11/1800:20keyboard99
2022/11/1800:20mouse66
2022/11/1800:20trumpet55
2022/11/1800:20screen33
2022/11/1800:40keyboard22
2022/11/1800:40mouse11
2022/11/1800:40trumpet33
2022/11/1800:40screen44
2022/11/1800:55keyboard55
2022/11/1800:55mouse66
2022/11/1800:55trumpet77
2022/11/1800:55screen88
2022/11/1900:03keyboard88
2022/11/1900:03mouse77
2022/11/1900:03trumpet66
2022/11/1900:03screen55
2022/11/1900:20keyboard99
2022/11/1900:20mouse66
2022/11/1900:20trumpet55
2022/11/1900:20screen33
2022/11/1900:40keyboard22
2022/11/1900:40mouse11
2022/11/1900:40trumpet33
2022/11/1900:40screen44
2022/11/1900:55keyboard55
2022/11/1900:55mouse66
2022/11/1900:55trumpet77
2022/11/1900:55screen88
2022/11/2000:03keyboard88
2022/11/2000:03mouse77
2022/11/2000:03trumpet66
2022/11/2000:03screen55
2022/11/2000:20keyboard99
2022/11/2000:20mouse66
2022/11/2000:20trumpet55
2022/11/2000:20screen33
2022/11/2000:40keyboard22
2022/11/2000:40mouse11
2022/11/2000:40trumpet33
2022/11/2000:40screen44
2022/11/2000:55keyboard55
2022/11/2000:55mouse66
2022/11/2000:55trumpet77
2022/11/2000:55screen88


...<div class='locked'><em>瀏覽完整內容，請先 <a href='member.php?mod=register'>註冊</a> 或 <a href='javascript:;' onclick="lsSubmit()">登入會員</a></em></div><div></div>

yku 發表於 2023-2-8 11:04 AM

本帖最後由 yku 於 2023-2-8 11:05 AM 編輯

select sum(ret2.a4) as sum_tot
from(
select ret.*
from (
select '2022/11/17' as a1,'00:03' as a2,'keyboard' as  a3,'88' as a4 union all
select '2022/11/17' as a1,'00:03' as a2,'mouse' as  a3,'77' as a4 union all
select '2022/11/17' as a1,'00:03' as a2,'trumpet' as  a3,'66' as a4 union all
select '2022/11/17' as a1,'00:03' as a2,'screen' as  a3,'55' as a4 union all
select '2022/11/17' as a1,'00:20' as a2,'keyboard' as  a3,'99' as a4 union all
select '2022/11/17' as a1,'00:20' as a2,'mouse' as  a3,'66' as a4 union all
select '2022/11/17' as a1,'00:20' as a2,'trumpet' as  a3,'55' as a4 union all
select '2022/11/17' as a1,'00:20' as a2,'screen' as  a3,'33' as a4 union all
select '2022/11/17' as a1,'00:40' as a2,'keyboard' as  a3,'22' as a4 union all
select '2022/11/17' as a1,'00:40' as a2,'mouse' as  a3,'11' as a4 union all
select '2022/11/17' as a1,'00:40' as a2,'trumpet' as  a3,'33' as a4 union all
select '2022/11/17' as a1,'00:40' as a2,'screen' as  a3,'44' as a4 union all
select '2022/11/17' as a1,'00:55' as a2,'keyboard' as  a3,'55' as a4 union all
select '2022/11/17' as a1,'00:55' as a2,'mouse' as  a3,'66' as a4 union all
select '2022/11/17' as a1,'00:55' as a2,'trumpet' as  a3,'77' as a4 union all
select '2022/11/17' as a1,'00:55' as a2,'screen' as  a3,'88' as a4 union all
select '2022/11/18' as a1,'00:03' as a2,'keyboard' as  a3,'88' as a4 union all
select '2022/11/18' as a1,'00:03' as a2,'mouse' as  a3,'77' as a4 union all
select '2022/11/18' as a1,'00:03' as a2,'trumpet' as  a3,'66' as a4 union all
select '2022/11/18' as a1,'00:03' as a2,'screen' as  a3,'55' as a4 union all
select '2022/11/18' as a1,'00:20' as a2,'keyboard' as  a3,'99' as a4 union all
select '2022/11/18' as a1,'00:20' as a2,'mouse' as  a3,'66' as a4 union all
select '2022/11/18' as a1,'00:20' as a2,'trumpet' as  a3,'55' as a4 union all
select '2022/11/18' as a1,'00:20' as a2,'screen' as  a3,'33' as a4 union all
select '2022/11/18' as a1,'00:40' as a2,'keyboard' as  a3,'22' as a4 union all
select '2022/11/18' as a1,'00:40' as a2,'mouse' as  a3,'11' as a4 union all
select '2022/11/18' as a1,'00:40' as a2,'trumpet' as  a3,'33' as a4 union all
select '2022/11/18' as a1,'00:40' as a2,'screen' as  a3,'44' as a4 union all
select '2022/11/18' as a1,'00:55' as a2,'keyboard' as  a3,'55' as a4 union all
select '2022/11/18' as a1,'00:55' as a2,'mouse' as  a3,'66' as a4 union all
select '2022/11/18' as a1,'00:55' as a2,'trumpet' as  a3,'77' as a4 union all
select '2022/11/18' as a1,'00:55' as a2,'screen' as  a3,'88' as a4 union all
select '2022/11/19' as a1,'00:03' as a2,'keyboard' as  a3,'88' as a4 union all
select '2022/11/19' as a1,'00:03' as a2,'mouse' as  a3,'77' as a4 union all
select '2022/11/19' as a1,'00:03' as a2,'trumpet' as  a3,'66' as a4 union all
select '2022/11/19' as a1,'00:03' as a2,'screen' as  a3,'55' as a4 union all
select '2022/11/19' as a1,'00:20' as a2,'keyboard' as  a3,'99' as a4 union all
select '2022/11/19' as a1,'00:20' as a2,'mouse' as  a3,'66' as a4 union all
select '2022/11/19' as a1,'00:20' as a2,'trumpet' as  a3,'55' as a4 union all
select '2022/11/19' as a1,'00:20' as a2,'screen' as  a3,'33' as a4 union all
select '2022/11/19' as a1,'00:40' as a2,'keyboard' as  a3,'22' as a4 union all
select '2022/11/19' as a1,'00:40' as a2,'mouse' as  a3,'11' as a4 union all
select '2022/11/19' as a1,'00:40' as a2,'trumpet' as  a3,'33' as a4 union all
select '2022/11/19' as a1,'00:40' as a2,'screen' as  a3,'44' as a4 union all
select '2022/11/19' as a1,'00:55' as a2,'keyboard' as  a3,'55' as a4 union all
select '2022/11/19' as a1,'00:55' as a2,'mouse' as  a3,'66' as a4 union all
select '2022/11/19' as a1,'00:55' as a2,'trumpet' as  a3,'77' as a4 union all
select '2022/11/19' as a1,'00:55' as a2,'screen' as  a3,'88' as a4 union all
select '2022/11/20' as a1,'00:03' as a2,'keyboard' as  a3,'88' as a4 union all
select '2022/11/20' as a1,'00:03' as a2,'mouse' as  a3,'77' as a4 union all
select '2022/11/20' as a1,'00:03' as a2,'trumpet' as  a3,'66' as a4 union all
select '2022/11/20' as a1,'00:03' as a2,'screen' as  a3,'55' as a4 union all
select '2022/11/20' as a1,'00:20' as a2,'keyboard' as  a3,'99' as a4 union all
select '2022/11/20' as a1,'00:20' as a2,'mouse' as  a3,'66' as a4 union all
select '2022/11/20' as a1,'00:20' as a2,'trumpet' as  a3,'55' as a4 union all
select '2022/11/20' as a1,'00:20' as a2,'screen' as  a3,'33' as a4 union all
select '2022/11/20' as a1,'00:40' as a2,'keyboard' as  a3,'22' as a4 union all
select '2022/11/20' as a1,'00:40' as a2,'mouse' as  a3,'11' as a4 union all
select '2022/11/20' as a1,'00:40' as a2,'trumpet' as  a3,'33' as a4 union all
select '2022/11/20' as a1,'00:40' as a2,'screen' as  a3,'44' as a4 union all
select '2022/11/20' as a1,'00:55' as a2,'keyboard' as  a3,'55' as a4 union all
select '2022/11/20' as a1,'00:55' as a2,'mouse' as  a3,'66' as a4 union all
select '2022/11/20' as a1,'00:55' as a2,'trumpet' as  a3,'77' as a4 union all
select '2022/11/20' as a1,'00:55' as a2,'screen' as  a3,'88' as a4 )ret
where ret.a2 = '00:20' order by a1 desc limit 10  ) ret2
where a3 = 'keyboard'
...<div class='locked'><em>瀏覽完整內容，請先 <a href='member.php?mod=register'>註冊</a> 或 <a href='javascript:;' onclick="lsSubmit()">登入會員</a></em></div>