伊莉討論區

標題: mysql過濾條件加總問題 [打印本頁]

---

作者: ayabreay1    時間: 2022-11-18 06:33 PM     標題: mysql過濾條件加總問題

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

日期	時間	欄位	數量
2022/11/17	00:03	keyboard	88
2022/11/17	00:03	mouse	77
2022/11/17	00:03	trumpet	66
2022/11/17	00:03	screen	55
2022/11/17	00:20	keyboard	99
2022/11/17	00:20	mouse	66
2022/11/17	00:20	trumpet	55
2022/11/17	00:20	screen	33
2022/11/17	00:40	keyboard	22
2022/11/17	00:40	mouse	11
2022/11/17	00:40	trumpet	33
2022/11/17	00:40	screen	44
2022/11/17	00:55	keyboard	55
2022/11/17	00:55	mouse	66
2022/11/17	00:55	trumpet	77
2022/11/17	00:55	screen	88
2022/11/18	00:03	keyboard	88
2022/11/18	00:03	mouse	77
2022/11/18	00:03	trumpet	66
2022/11/18	00:03	screen	55
2022/11/18	00:20	keyboard	99
2022/11/18	00:20	mouse	66
2022/11/18	00:20	trumpet	55
2022/11/18	00:20	screen	33
2022/11/18	00:40	keyboard	22
2022/11/18	00:40	mouse	11
2022/11/18	00:40	trumpet	33
2022/11/18	00:40	screen	44
2022/11/18	00:55	keyboard	55
2022/11/18	00:55	mouse	66
2022/11/18	00:55	trumpet	77
2022/11/18	00:55	screen	88
2022/11/19	00:03	keyboard	88
2022/11/19	00:03	mouse	77
2022/11/19	00:03	trumpet	66
2022/11/19	00:03	screen	55
2022/11/19	00:20	keyboard	99
2022/11/19	00:20	mouse	66
2022/11/19	00:20	trumpet	55
2022/11/19	00:20	screen	33
2022/11/19	00:40	keyboard	22
2022/11/19	00:40	mouse	11
2022/11/19	00:40	trumpet	33
2022/11/19	00:40	screen	44
2022/11/19	00:55	keyboard	55
2022/11/19	00:55	mouse	66
2022/11/19	00:55	trumpet	77
2022/11/19	00:55	screen	88
2022/11/20	00:03	keyboard	88
2022/11/20	00:03	mouse	77
2022/11/20	00:03	trumpet	66
2022/11/20	00:03	screen	55
2022/11/20	00:20	keyboard	99
2022/11/20	00:20	mouse	66
2022/11/20	00:20	trumpet	55
2022/11/20	00:20	screen	33
2022/11/20	00:40	keyboard	22
2022/11/20	00:40	mouse	11
2022/11/20	00:40	trumpet	33
2022/11/20	00:40	screen	44
2022/11/20	00:55	keyboard	55
2022/11/20	00:55	mouse	66
2022/11/20	00:55	trumpet	77
2022/11/20	00:55	screen	88

---

作者: yku    時間: 2023-2-8 11:04 AM

1. select sum(ret2.a4) as sum_tot
   
2. from(
   
3. select ret.*
   
4. from (
   
5. select '2022/11/17' as a1,'00:03' as a2,'keyboard' as  a3,'88' as a4 union all
   
6. select '2022/11/17' as a1,'00:03' as a2,'mouse' as  a3,'77' as a4 union all
   
7. select '2022/11/17' as a1,'00:03' as a2,'trumpet' as  a3,'66' as a4 union all
   
8. select '2022/11/17' as a1,'00:03' as a2,'screen' as  a3,'55' as a4 union all
   
9. select '2022/11/17' as a1,'00:20' as a2,'keyboard' as  a3,'99' as a4 union all
   
10. select '2022/11/17' as a1,'00:20' as a2,'mouse' as  a3,'66' as a4 union all
    
11. select '2022/11/17' as a1,'00:20' as a2,'trumpet' as  a3,'55' as a4 union all
    
12. select '2022/11/17' as a1,'00:20' as a2,'screen' as  a3,'33' as a4 union all
    
13. select '2022/11/17' as a1,'00:40' as a2,'keyboard' as  a3,'22' as a4 union all
    
14. select '2022/11/17' as a1,'00:40' as a2,'mouse' as  a3,'11' as a4 union all
    
15. select '2022/11/17' as a1,'00:40' as a2,'trumpet' as  a3,'33' as a4 union all
    
16. select '2022/11/17' as a1,'00:40' as a2,'screen' as  a3,'44' as a4 union all
    
17. select '2022/11/17' as a1,'00:55' as a2,'keyboard' as  a3,'55' as a4 union all
    
18. select '2022/11/17' as a1,'00:55' as a2,'mouse' as  a3,'66' as a4 union all
    
19. select '2022/11/17' as a1,'00:55' as a2,'trumpet' as  a3,'77' as a4 union all
    
20. select '2022/11/17' as a1,'00:55' as a2,'screen' as  a3,'88' as a4 union all
    
21. select '2022/11/18' as a1,'00:03' as a2,'keyboard' as  a3,'88' as a4 union all
    
22. select '2022/11/18' as a1,'00:03' as a2,'mouse' as  a3,'77' as a4 union all
    
23. select '2022/11/18' as a1,'00:03' as a2,'trumpet' as  a3,'66' as a4 union all
    
24. select '2022/11/18' as a1,'00:03' as a2,'screen' as  a3,'55' as a4 union all
    
25. select '2022/11/18' as a1,'00:20' as a2,'keyboard' as  a3,'99' as a4 union all
    
26. select '2022/11/18' as a1,'00:20' as a2,'mouse' as  a3,'66' as a4 union all
    
27. select '2022/11/18' as a1,'00:20' as a2,'trumpet' as  a3,'55' as a4 union all
    
28. select '2022/11/18' as a1,'00:20' as a2,'screen' as  a3,'33' as a4 union all
    
29. select '2022/11/18' as a1,'00:40' as a2,'keyboard' as  a3,'22' as a4 union all
    
30. select '2022/11/18' as a1,'00:40' as a2,'mouse' as  a3,'11' as a4 union all
    
31. select '2022/11/18' as a1,'00:40' as a2,'trumpet' as  a3,'33' as a4 union all
    
32. select '2022/11/18' as a1,'00:40' as a2,'screen' as  a3,'44' as a4 union all
    
33. select '2022/11/18' as a1,'00:55' as a2,'keyboard' as  a3,'55' as a4 union all
    
34. select '2022/11/18' as a1,'00:55' as a2,'mouse' as  a3,'66' as a4 union all
    
35. select '2022/11/18' as a1,'00:55' as a2,'trumpet' as  a3,'77' as a4 union all
    
36. select '2022/11/18' as a1,'00:55' as a2,'screen' as  a3,'88' as a4 union all
    
37. select '2022/11/19' as a1,'00:03' as a2,'keyboard' as  a3,'88' as a4 union all
    
38. select '2022/11/19' as a1,'00:03' as a2,'mouse' as  a3,'77' as a4 union all
    
39. select '2022/11/19' as a1,'00:03' as a2,'trumpet' as  a3,'66' as a4 union all
    
40. select '2022/11/19' as a1,'00:03' as a2,'screen' as  a3,'55' as a4 union all
    
41. select '2022/11/19' as a1,'00:20' as a2,'keyboard' as  a3,'99' as a4 union all
    
42. select '2022/11/19' as a1,'00:20' as a2,'mouse' as  a3,'66' as a4 union all
    
43. select '2022/11/19' as a1,'00:20' as a2,'trumpet' as  a3,'55' as a4 union all
    
44. select '2022/11/19' as a1,'00:20' as a2,'screen' as  a3,'33' as a4 union all
    
45. select '2022/11/19' as a1,'00:40' as a2,'keyboard' as  a3,'22' as a4 union all
    
46. select '2022/11/19' as a1,'00:40' as a2,'mouse' as  a3,'11' as a4 union all
    
47. select '2022/11/19' as a1,'00:40' as a2,'trumpet' as  a3,'33' as a4 union all
    
48. select '2022/11/19' as a1,'00:40' as a2,'screen' as  a3,'44' as a4 union all
    
49. select '2022/11/19' as a1,'00:55' as a2,'keyboard' as  a3,'55' as a4 union all
    
50. select '2022/11/19' as a1,'00:55' as a2,'mouse' as  a3,'66' as a4 union all
    
51. select '2022/11/19' as a1,'00:55' as a2,'trumpet' as  a3,'77' as a4 union all
    
52. select '2022/11/19' as a1,'00:55' as a2,'screen' as  a3,'88' as a4 union all
    
53. select '2022/11/20' as a1,'00:03' as a2,'keyboard' as  a3,'88' as a4 union all
    
54. select '2022/11/20' as a1,'00:03' as a2,'mouse' as  a3,'77' as a4 union all
    
55. select '2022/11/20' as a1,'00:03' as a2,'trumpet' as  a3,'66' as a4 union all
    
56. select '2022/11/20' as a1,'00:03' as a2,'screen' as  a3,'55' as a4 union all
    
57. select '2022/11/20' as a1,'00:20' as a2,'keyboard' as  a3,'99' as a4 union all
    
58. select '2022/11/20' as a1,'00:20' as a2,'mouse' as  a3,'66' as a4 union all
    
59. select '2022/11/20' as a1,'00:20' as a2,'trumpet' as  a3,'55' as a4 union all
    
60. select '2022/11/20' as a1,'00:20' as a2,'screen' as  a3,'33' as a4 union all
    
61. select '2022/11/20' as a1,'00:40' as a2,'keyboard' as  a3,'22' as a4 union all
    
62. select '2022/11/20' as a1,'00:40' as a2,'mouse' as  a3,'11' as a4 union all
    
63. select '2022/11/20' as a1,'00:40' as a2,'trumpet' as  a3,'33' as a4 union all
    
64. select '2022/11/20' as a1,'00:40' as a2,'screen' as  a3,'44' as a4 union all
    
65. select '2022/11/20' as a1,'00:55' as a2,'keyboard' as  a3,'55' as a4 union all
    
66. select '2022/11/20' as a1,'00:55' as a2,'mouse' as  a3,'66' as a4 union all
    
67. select '2022/11/20' as a1,'00:55' as a2,'trumpet' as  a3,'77' as a4 union all
    
68. select '2022/11/20' as a1,'00:55' as a2,'screen' as  a3,'88' as a4 )ret
    
69. where ret.a2 = '00:20' order by a1 desc limit 10  ) ret2
    
70. where a3 = 'keyboard'

複製代碼

---

歡迎光臨 伊莉討論區 (http://www.llnm0w0.eyny.com/)

Powered by Discuz!