iT邦幫忙

1

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

資料如下

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的最大值及時間

1 個回答

1
paicheng0111
iT邦高手 1 級 ‧ 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,會列出全部筆數,可否只讓他只列出第一筆

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,就正常了

已經修改答案

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

真是太感謝您了~~

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

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

我要發表回答

立即登入回答