iT邦幫忙

1

[SQLite]請問如何抓取各欄位的最大值及其對應的時間

sql sqlite3 max time
forumcr 2020-03-15 19:46:21 ‧ 2052 瀏覽

  • 分享至 

  • xImage

資料如下

DATE|L1|L2|L3|L4
------------- | -------------
2020/03/15 00:15:00|0|0|0|0
2020/03/15 00:30:00|0|0|0|0
2020/03/15 00:45:00|0|0|0|0
2020/03/15 01:00:00|0|0|0|0
2020/03/15 01:15:00|0|0|0|0
2020/03/15 01:30:00|0|0|0|0
2020/03/15 01:45:00|0|0|0|0
2020/03/15 02:00:00|0|0|0|0
2020/03/15 02:15:00|0|0|0|0
2020/03/15 02:30:00|0|0|0|0
2020/03/15 02:45:00|0|0|0|0
2020/03/15 03:00:00|0|0|0|0
2020/03/15 03:15:00|0|0|0|0
2020/03/15 03:30:00|0|0|0|0
2020/03/15 03:45:00|0|0|0|0
2020/03/15 04:00:00|0|0|0|0
2020/03/15 04:15:00|0|0|0|0
2020/03/15 04:30:00|0|0|0|0
2020/03/15 04:45:00|0|0|0|0
2020/03/15 05:00:00|0|0|0|0
2020/03/15 05:15:00|0|0|0|0
2020/03/15 05:30:00|0|0|0|0
2020/03/15 05:45:00|0|0|0|0
2020/03/15 06:00:00|0|0|0|0
2020/03/15 06:15:00|0|0|0|0
2020/03/15 06:30:00|0|0|0|0
2020/03/15 06:45:00|0|0|0|0
2020/03/15 07:00:00|0|0|0|0
2020/03/15 07:15:00|0|0|0|0
2020/03/15 07:30:00|0|0|0|0
2020/03/15 07:45:00|0|0|0|0
2020/03/15 08:00:00|0|0|0|0
2020/03/15 08:15:00|0|0|0|0
2020/03/15 08:30:00|0|0|0|0
2020/03/15 08:45:00|0|0|0|0
2020/03/15 09:00:00|0|0|0|0
2020/03/15 09:15:00|0|0|0|0
2020/03/15 09:30:00|0|0|0|0
2020/03/15 09:45:00|0|0|0|0
2020/03/15 10:00:00|0|0|0|0
2020/03/15 10:15:00|0|0|0|0
2020/03/15 10:30:00|0|0|0|0
2020/03/15 10:45:00|0|0|0|0
2020/03/15 11:00:00|500|0|70|220
2020/03/15 11:15:00|2900|0|406|1276
2020/03/15 11:30:00|3600|0|504|1584
2020/03/15 11:45:00|0|0|0|0
2020/03/15 12:00:00|1600|0|224|7040
2020/03/15 12:15:00|4400|0|616|1936
2020/03/15 12:30:00|7300|0|1022|3212
2020/03/15 12:45:00|14200|0|1988|6248
2020/03/15 13:00:00|6500|0|910|2860
2020/03/15 13:15:00|4800|0|672|2112
2020/03/15 13:30:00|0|0|0|0
2020/03/15 13:45:00|0|0|0|0
2020/03/15 14:00:00|0|0|0|0
2020/03/15 14:15:00|0|0|0|0
2020/03/15 14:30:00|0|0|0|0
2020/03/15 14:45:00|0|0|0|0
2020/03/15 15:00:00|0|0|0|0
2020/03/15 15:15:00|0|0|0|0
2020/03/15 15:30:00|0|0|0|0
2020/03/15 15:45:00|0|0|0|0
2020/03/15 16:00:00|0|0|0|0
2020/03/15 16:15:00|0|0|0|0
2020/03/15 16:30:00|0|0|0|0
2020/03/15 16:45:00|4100|0|5740|1804
2020/03/15 17:00:00|3500|0|490|1540
2020/03/15 17:15:00|0|0|0|0
2020/03/15 17:30:00|0|0|0|0
2020/03/15 17:45:00|0|0|0|0
2020/03/15 18:00:00|1500|0|210|660
2020/03/15 18:15:00|200|0|28|88
2020/03/15 18:30:00|900|0|126|3960
2020/03/15 18:45:00|1600|0|800|704
2020/03/15 19:00:00|0|0|0|0
2020/03/15 19:15:00|0|0|0|0
2020/03/15 19:30:00|0|0|0|0
2020/03/15 19:45:00|0|0|0|0
2020/03/15 20:00:00|0|0|0|0
2020/03/15 20:15:00|0|0|0|0
2020/03/15 20:30:00|0|0|0|0
2020/03/15 20:45:00|0|0|0|0
2020/03/15 21:00:00|0|0|0|0
2020/03/15 21:15:00|0|0|0|0
2020/03/15 21:30:00|0|0|0|0
2020/03/15 21:45:00|0|0|0|0
2020/03/15 22:00:00|0|0|0|0
2020/03/15 22:15:00|0|0|0|0
2020/03/15 22:30:00|0|0|0|0
2020/03/15 22:45:00|0|0|0|0
2020/03/15 23:00:00|0|0|0|0
2020/03/15 23:15:00|0|0|0|0
2020/03/15 23:30:00|0|0|0|0
2020/03/15 23:45:00|0|0|0|0
2020/03/16 00:00:00|0|0|0|0

請問如何抓取L1,L2,L3,L4的最大值，及其對應的時間
如
DATE|L1|L2|L3|L4
------------- | -------------
2020/03/15 12:45:00|14200|0|0|0 | **L1的最大值及時間
2020/03/15 00:15:00|0|0|0|0| **L2的最大值及時間
2020/03/15 16:45:00|0|0|5740|0| **L3的最大值及時間
2020/03/15 12:00:00|0|0|0|7040| **L4的最大值及時間

登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

1 個回答

1
paicheng0111
iT邦大師 5 級 ‧ 2020-03-15 20:34:44
最佳解答

Try this

SELECT DATE, L1, L2, L3, L4, '**L1的最大值及時間' AS NOTE FROM table WHERE L1 = (SELECT Max(L1) FROM table) ORDER BY 1 LIMIT 1
UNION ALL
SELECT DATE, L1, L2, L3, L4, '**L2的最大值及時間' FROM table WHERE L2 = (SELECT Max(L2) FROM table) ORDER BY DATE LIMIT 1
UNION ALL
SELECT DATE, L1, L2, L3, L4, '**L3的最大值及時間' FROM table WHERE L3 = (SELECT Max(L3) FROM table) ORDER BY DATE LIMIT 1
UNION ALL
SELECT DATE, L1, L2, L3, L4, '**L4的最大值及時間' FROM table WHERE L4 = (SELECT Max(L4) FROM table) ORDER BY DATE LIMIT 1;

修改如下

SELECT b.DATE, L1, L2, L3, L4 
FROM tb a inner join 
  (SELECT DATE, Max(L1) AS ky FROM tb GROUP BY DATE HAVING L1 = (SELECT Max(L1) FROM tb) ORDER BY 1 LIMIT 1) b
  on a.DATE = b.DATE AND a.L1 = b.ky
UNION all 
SELECT b.DATE, L1, L2, L3, L4 
FROM tb a inner join 
  (SELECT DATE, Max(L2) AS ky FROM tb GROUP BY DATE HAVING L2 = (SELECT Max(L2) FROM tb) ORDER BY 1 LIMIT 1) b
  on a.DATE = b.DATE AND a.L2 = b.ky
UNION all 
SELECT b.DATE, L1, L2, L3, L4 
FROM tb a inner join 
  (SELECT DATE, Max(L3) AS ky FROM tb GROUP BY DATE HAVING L3 = (SELECT Max(L3) FROM tb) ORDER BY 1 LIMIT 1) b
  on a.DATE = b.DATE AND a.L3 = b.ky
UNION all 
SELECT b.DATE, L1, L2, L3, L4 
FROM tb a inner join 
  (SELECT DATE, Max(L4) AS ky FROM tb GROUP BY DATE HAVING L4 = (SELECT Max(L4) FROM tb) ORDER BY 1 LIMIT 1) b
  on a.DATE = b.DATE AND a.L4 = b.ky;

http://sqlfiddle.com/#!7/516fe/15/0

看更多先前的回應...收起先前的回應...
forumcr iT邦新手 5 級 ‧ 2020-03-15 22:19:09 檢舉

我測試後出現 ORDER BY clause should come after UNION ALL not before

forumcr iT邦新手 5 級 ‧ 2020-03-15 22:30:54 檢舉

查詢出來只想如下，第一篇的是加說明
DATE|L1|L2|L3|L4
------------- | -------------
2020/03/15 12:45:00|14200|0|0|0
2020/03/15 00:15:00|0|0|0|0
2020/03/15 16:45:00|0|0|5740|0
2020/03/15 12:00:00|0|0|0|7040

forumcr iT邦新手 5 級 ‧ 2020-03-15 23:11:46 檢舉

再請教，若L2全天皆為0，會列出全部筆數，可否只讓他只列出第一筆

paicheng0111 iT邦大師 5 級 ‧ 2020-03-15 23:33:10 檢舉

LIMIT 1只會取一筆，但是要先排序來定義何謂第一筆。

forumcr iT邦新手 5 級 ‧ 2020-03-15 23:36:41 檢舉

很奇怪的是，我有2020/03/13,2020/03/14,2020/03/15這3天的日期，L2全是0，查詢出來的結果，會全部列出，我用WHERE去限制日期，查詢後也是全部列出，我搞不懂@@

forumcr iT邦新手 5 級 ‧ 2020-03-15 23:44:46 檢舉 
SELECT
	DATE,
	L1,
	L2,
	L3,
	L4, 
	'1' AS NOTE 
FROM
	table1 
WHERE
	L1 = ( SELECT Max( L1 ) FROM table1 WHERE DATE like '2020/03/15%' ORDER BY 1 LIMIT 1 ) union all
SELECT
	DATE,
	L1,
	L2,
	L3,
	L4, 
	'2' AS NOTE 
FROM
	table1 
WHERE
	L2 = ( SELECT Max( L2 ) FROM table1 WHERE DATE like '2020/03/15%' ORDER BY 1 LIMIT 1)	 union all
SELECT
	DATE,
	L1,
	L2,
	L3,
	L4, 
	'3' AS NOTE 
FROM
	table1 
WHERE
	L3 = ( SELECT Max( L3 ) FROM table1 WHERE DATE like '2020/03/15%' ORDER BY 1 LIMIT 1 )	union all
SELECT
	DATE,
	L1,
	L2,
	L3,
	L4, 
	'4' AS NOTE 
FROM
	table1 
WHERE
	L4 = ( SELECT Max( L4 ) FROM table1 WHERE DATE like '2020/03/15%' ORDER BY 1 LIMIT 1 )

查詢後得到
DATE|L1|L2|L3|L4|NOTE
------------- | -------------
2020/03/14 14:30:00|13600|0|1904|5984|2
2020/03/14 15:00:00|9100|0|1274|4004|2
2020/03/14 15:15:00|11100|0|1554|4884|2
2020/03/14 15:30:00|9100|0|1274|4004|2
2020/03/14 15:45:00|7500|0|1050|3300|2
2020/03/14 16:00:00|11300|0|1582|4972|2
2020/03/14 16:15:00|18000|0|2520|7920|2
2020/03/14 16:30:00|12900|0|1806|5676|2
2020/03/14 16:45:00|18000|0|2520|7920|2
2020/03/14 17:00:00|18001|0|2520|7920|2
2020/03/14 17:15:00|18002|0|2520|7920|2
2020/03/14 17:30:00|18003|0|2520|7920|2
2020/03/14 17:45:00|18004|0|2520|7920|2
2020/03/14 18:00:00|3300|0|462|1452|2
2020/03/15 11:00:00|500|0|70|220|2
2020/03/15 11:15:00|2900|0|406|1276|2
2020/03/15 11:30:00|3600|0|504|1584|2
2020/03/15 12:00:00|1600|0|224|7040|2
2020/03/15 12:15:00|4400|0|616|1936|2
2020/03/15 12:30:00|7300|0|1022|3212|2
2020/03/15 12:45:00|14200|0|1988|6248|2
2020/03/15 13:00:00|6500|0|910|2860|2
2020/03/15 13:15:00|4800|0|672|2112|2
2020/03/15 16:45:00|4100|0|5740|1804|2
2020/03/15 17:00:00|3500|0|490|1540|2
2020/03/15 18:00:00|1500|0|210|660|2
2020/03/15 18:15:00|200|0|28|88|2
2020/03/15 00:15:00|0|0|0|0|2
2020/03/15 00:30:00|0|0|0|0|2
2020/03/15 00:45:00|0|0|0|0|2
2020/03/15 01:00:00|0|0|0|0|2
2020/03/15 01:15:00|0|0|0|0|2
2020/03/15 01:30:00|0|0|0|0|2
2020/03/15 01:45:00|0|0|0|0|2
2020/03/15 02:00:00|0|0|0|0|2
2020/03/15 02:15:00|0|0|0|0|2
2020/03/15 02:30:00|0|0|0|0|2
2020/03/15 02:45:00|0|0|0|0|2
2020/03/15 03:00:00|0|0|0|0|2
2020/03/15 03:15:00|0|0|0|0|2
2020/03/15 03:30:00|0|0|0|0|2
2020/03/15 03:45:00|0|0|0|0|2
2020/03/15 04:00:00|0|0|0|0|2
2020/03/15 04:15:00|0|0|0|0|2
2020/03/15 04:30:00|0|0|0|0|2
2020/03/15 04:45:00|0|0|0|0|2
2020/03/15 05:00:00|0|0|0|0|2
2020/03/15 05:15:00|0|0|0|0|2
2020/03/15 05:30:00|0|0|0|0|2
2020/03/15 05:45:00|0|0|0|0|2
2020/03/15 06:00:00|0|0|0|0|2
2020/03/15 06:15:00|0|0|0|0|2
2020/03/15 06:30:00|0|0|0|0|2
2020/03/15 06:45:00|0|0|0|0|2
2020/03/15 07:00:00|0|0|0|0|2
2020/03/15 07:15:00|0|0|0|0|2
2020/03/15 07:30:00|0|0|0|0|2
2020/03/15 07:45:00|0|0|0|0|2
2020/03/15 08:00:00|0|0|0|0|2
2020/03/15 08:15:00|0|0|0|0|2
2020/03/15 08:30:00|0|0|0|0|2
2020/03/15 08:45:00|0|0|0|0|2
2020/03/15 09:00:00|0|0|0|0|2
2020/03/15 09:15:00|0|0|0|0|2
2020/03/15 09:30:00|0|0|0|0|2
2020/03/15 09:45:00|0|0|0|0|2
2020/03/15 10:00:00|0|0|0|0|2
2020/03/15 10:15:00|0|0|0|0|2
2020/03/15 10:30:00|0|0|0|0|2
2020/03/15 10:45:00|0|0|0|0|2
2020/03/15 11:45:00|0|0|0|0|2
2020/03/15 13:30:00|0|0|0|0|2
2020/03/15 13:45:00|0|0|0|0|2
2020/03/15 14:00:00|0|0|0|0|2
2020/03/15 14:15:00|0|0|0|0|2
2020/03/15 14:30:00|0|0|0|0|2
2020/03/15 14:45:00|0|0|0|0|2
2020/03/15 15:00:00|0|0|0|0|2
2020/03/15 15:15:00|0|0|0|0|2
2020/03/15 15:30:00|0|0|0|0|2
2020/03/15 15:45:00|0|0|0|0|2
2020/03/15 16:00:00|0|0|0|0|2
2020/03/15 16:15:00|0|0|0|0|2
2020/03/15 16:30:00|0|0|0|0|2
2020/03/15 17:15:00|0|0|0|0|2
2020/03/15 17:30:00|0|0|0|0|2
2020/03/15 17:45:00|0|0|0|0|2
2020/03/15 19:30:00|0|0|0|0|2
2020/03/15 19:45:00|0|0|0|0|2
2020/03/15 20:00:00|0|0|0|0|2
2020/03/15 20:15:00|0|0|0|0|2
2020/03/15 20:30:00|0|0|0|0|2
2020/03/15 20:45:00|0|0|0|0|2
2020/03/15 21:00:00|0|0|0|0|2
2020/03/15 21:15:00|0|0|0|0|2
2020/03/15 21:30:00|0|0|0|0|2
2020/03/15 21:45:00|0|0|0|0|2
2020/03/15 22:00:00|0|0|0|0|2
2020/03/15 22:15:00|0|0|0|0|2
2020/03/15 22:30:00|0|0|0|0|2
2020/03/15 22:45:00|0|0|0|0|2
2020/03/15 23:00:00|0|0|0|0|2
2020/03/15 23:15:00|0|0|0|0|2
2020/03/15 23:30:00|0|0|0|0|2
2020/03/15 23:45:00|0|0|0|0|2
2020/03/15 00:00:00|0|0|0|0|2
2020/03/15 18:30:00|900|0|126|3960|2
2020/03/16 00:00:00|0|0|0|0|2
2020/03/15 18:45:00|2200|0|308|968|2
2020/03/15 19:00:00|600|0|84|264|2
2020/03/15 19:15:00|1900|0|266|836|2
2020/03/15 16:45:00|4100|0|5740|1804|3
2020/03/15 12:00:00|1600|0|224|7040|4

請問為什麼會這樣？

forumcr iT邦新手 5 級 ‧ 2020-03-15 23:47:13 檢舉

L1,L3,L4是正常的，L2若全為0則會如上，L2若隨便key1筆不為0，就正常了

paicheng0111 iT邦大師 5 級 ‧ 2020-03-16 09:28:30 檢舉

已經修改答案

forumcr iT邦新手 5 級 ‧ 2020-03-16 14:29:53 檢舉

真是太感謝您了~~

再請教一下，我加了其他兩天的日期，請問要如何只選到我要的那一天
http://sqlfiddle.com/#!7/ed171/1/0

paicheng0111 iT邦大師 5 級 ‧ 2020-03-16 20:53:01 檢舉

Put WHERE DATE ='2020/03/xx' in the appropriate position.

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙