請教mysql查詢每月中跨日時段的最大值

mysql日期查詢

forumcr 2023-03-13 17:40:50 ‧ 585 瀏覽

分享至

請教各位先進有關如下資料查詢，想查詢的結果是跨日最大值,
例如：5:00:00到隔天的5:00:00

time v1 v2
2023-03-11 19:00:00 18.50 18.50
2023-03-11 20:00:00 18.50 18.50
2023-03-11 21:00:00 18.50 18.50
2023-03-11 22:00:00 18.50 18.50
2023-03-11 23:00:00 18.50 18.50
2023-03-12 00:00:00 18.50 18.50
2023-03-12 01:00:00 18.50 18.50
2023-03-12 02:00:00 18.50 18.50
2023-03-12 03:00:00 18.50 18.50
2023-03-12 04:00:00 18.50 18.50
2023-03-12 05:00:00 18.50 18.50
2023-03-12 06:00:00 18.50 18.50
2023-03-12 07:00:00 18.50 18.50
2023-03-12 08:00:00 18.50 18.50
2023-03-12 09:00:00 75.75 18.50
2023-03-12 10:00:00 135.75 18.50
2023-03-12 11:00:00 166.75 18.50
2023-03-12 12:00:00 171.25 18.50
2023-03-12 13:00:00 180.00 18.50
2023-03-12 14:00:00 186.75 18.50
2023-03-12 15:00:00 195.25 18.50
2023-03-12 16:00:00 204.50 18.50
2023-03-12 17:00:00 222.25 18.50
2023-03-12 18:00:00 247.00 18.50
2023-03-12 19:00:00 294.00 18.50
2023-03-12 20:00:00 305.50 18.50
2023-03-12 21:00:00 310.25 18.50
2023-03-12 22:00:00 342.00 18.50
2023-03-12 23:00:00 342.00 18.50
2023-03-13 00:00:00 342.00 18.50
2023-03-13 01:00:00 342.00 18.50
2023-03-13 02:00:00 342.00 18.50
2023-03-13 03:00:00 342.00 18.50
2023-03-13 04:00:00 342.00 18.50
2023-03-13 05:00:00 358.25 18.50
2023-03-13 06:00:00 418.25 53.75
2023-03-13 07:00:00 478.25 113.75
2023-03-13 08:00:00 538.00 173.50
2023-03-13 09:00:00 598.00 233.50
2023-03-13 10:00:00 658.00 293.50
2023-03-13 11:00:00 718.00 353.50
2023-03-13 12:00:00 778.00 413.50
2023-03-13 13:00:00 838.00 473.50
2023-03-13 14:00:00 897.75 533.25
2023-03-13 15:00:00 957.50 593.00
2023-03-13 16:00:00 1017.50 653.00
2023-03-13 17:00:00 1077.50 713.00
2023-03-13 18:00:00 1086.00 721.50

想得到的結果為
2023-03-11 18.50 18.50
2023-03-12 358.25 18.50
2023-03-13 1086.00 721.50

SELECT
	k_day.time, 
	MAX(k_day.v1), 
	MAX(k_day.v2)
FROM
	k_day
WHERE
	time(time) > '05:00:00' and time(DATE_ADD(time,INTERVAL 1 day))<'06:00:00'
GROUP BY
	day(time)

上述程式碼查出來結果
2023-03-11 18.50 18.50
2023-03-12 342.00 18.50
2023-03-13 1086.00 721.50
查詢出來不是，能否請先進們協助，謝謝

player iT邦大師 1 級 ‧ 2023-03-13 19:40:24 檢舉

時間先位移
可能比較好算
先把各自的時間位移各自移到一天內
在用群組取各自天數的最大值
最後再把時間調正回來

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

rogeryao

iT邦超人 8 級 ‧ 2023-03-13 20:04:31

最佳解答

CREATE TABLE K_DAY (
V0 DATETIME,
V1 FLOAT,
V2 FLOAT);

INSERT INTO K_DAY VALUES
('2023-03-11 19:00:00',18.50,18.50),
('2023-03-11 20:00:00',18.50,18.50),
('2023-03-11 21:00:00',18.50,18.50),
('2023-03-11 22:00:00',18.50,18.50),
('2023-03-11 23:00:00',18.50,18.50),
('2023-03-12 00:00:00',18.50,18.50),
('2023-03-12 01:00:00',18.50,18.50),
('2023-03-12 02:00:00',18.50,18.50),
('2023-03-12 03:00:00',18.50,18.50),
('2023-03-12 04:00:00',18.50,18.50),
('2023-03-12 05:00:00',18.50,18.50),
('2023-03-12 06:00:00',18.50,18.50),
('2023-03-12 07:00:00',18.50,18.50),
('2023-03-12 08:00:00',18.50,18.50),
('2023-03-12 09:00:00',75.75,18.50),
('2023-03-12 10:00:00',135.75,18.50),
('2023-03-12 11:00:00',166.75,18.50),
('2023-03-12 12:00:00',171.25,18.50),
('2023-03-12 13:00:00',180.00,18.50),
('2023-03-12 14:00:00',186.75,18.50),
('2023-03-12 15:00:00',195.25,18.50),
('2023-03-12 16:00:00',204.50,18.50),
('2023-03-12 17:00:00',222.25,18.50),
('2023-03-12 18:00:00',247.00,18.50),
('2023-03-12 19:00:00',294.00,18.50),
('2023-03-12 20:00:00',305.50,18.50),
('2023-03-12 21:00:00',310.25,18.50),
('2023-03-12 22:00:00',342.00,18.50),
('2023-03-12 23:00:00',342.00,18.50),
('2023-03-13 00:00:00',342.00,18.50),
('2023-03-13 01:00:00',342.00,18.50),
('2023-03-13 02:00:00',342.00,18.50),
('2023-03-13 03:00:00',342.00,18.50),
('2023-03-13 04:00:00',342.00,18.50),
('2023-03-13 05:00:00',358.25,18.50),
('2023-03-13 06:00:00',418.25,53.75),
('2023-03-13 07:00:00',478.25,113.75),
('2023-03-13 08:00:00',538.00,173.50),
('2023-03-13 09:00:00',598.00,233.50),
('2023-03-13 10:00:00',658.00,293.50),
('2023-03-13 11:00:00',718.00,353.50),
('2023-03-13 12:00:00',778.00,413.50),
('2023-03-13 13:00:00',838.00,473.50),
('2023-03-13 14:00:00',897.75,533.25),
('2023-03-13 15:00:00',957.50,593.00),
('2023-03-13 16:00:00',1017.50,653.00),
('2023-03-13 17:00:00',1077.50,713.00),
('2023-03-13 18:00:00',1086.00,721.50);

SELECT DATE_FORMAT(DATE_SUB(V0,INTERVAL 6 HOUR),'%Y-%m-%d') AS V0_NEW,
MAX(V1) AS V1_NEW,MAX(V2) AS V2_NEW
FROM K_DAY
GROUP BY DATE_FORMAT(DATE_SUB(V0,INTERVAL 6 HOUR),'%Y-%m-%d')

Demo