sql语句

SELECT
sum( CASE WHEN time BETWEEN '2021-07-15 00:00:00' AND '2021-07-15 00:30:00' THEN 1 ELSE 0 END ) AS '1',
sum( CASE WHEN time BETWEEN '2021-07-15 00:30:00' AND '2021-07-15 01:00:00' THEN 1 ELSE 0 END ) AS '2',
sum( CASE WHEN time BETWEEN '2021-07-15 01:00:00' AND '2021-07-15 01:30:00' THEN 1 ELSE 0 END ) AS '3',
sum( CASE WHEN time BETWEEN '2021-07-15 01:30:00' AND '2021-07-15 02:00:00' THEN 1 ELSE 0 END ) AS '4',
sum( CASE WHEN time BETWEEN '2021-07-15 02:00:00' AND '2021-07-15 02:30:00' THEN 1 ELSE 0 END ) AS '5',
sum( CASE WHEN time BETWEEN '2021-07-15 02:30:00' AND '2021-07-15 03:00:00' THEN 1 ELSE 0 END ) AS '6',
sum( CASE WHEN time BETWEEN '2021-07-15 03:00:00' AND '2021-07-15 03:30:00' THEN 1 ELSE 0 END ) AS '7',
sum( CASE WHEN time BETWEEN '2021-07-15 03:30:00' AND '2021-07-15 04:00:00' THEN 1 ELSE 0 END ) AS '8',
sum( CASE WHEN time BETWEEN '2021-07-15 04:00:00' AND '2021-07-15 04:30:00' THEN 1 ELSE 0 END ) AS '9',
sum( CASE WHEN time BETWEEN '2021-07-15 04:30:00' AND '2021-07-15 05:00:00' THEN 1 ELSE 0 END ) AS '10',
sum( CASE WHEN time BETWEEN '2021-07-15 05:00:00' AND '2021-07-15 05:30:00' THEN 1 ELSE 0 END ) AS '11',
sum( CASE WHEN time BETWEEN '2021-07-15 05:30:00' AND '2021-07-15 06:00:00' THEN 1 ELSE 0 END ) AS '12',
sum( CASE WHEN time BETWEEN '2021-07-15 06:00:00' AND '2021-07-15 06:30:00' THEN 1 ELSE 0 END ) AS '13',
sum( CASE WHEN time BETWEEN '2021-07-15 06:30:00' AND '2021-07-15 07:00:00' THEN 1 ELSE 0 END ) AS '14',
sum( CASE WHEN time BETWEEN '2021-07-15 07:00:00' AND '2021-07-15 07:30:00' THEN 1 ELSE 0 END ) AS '15',
sum( CASE WHEN time BETWEEN '2021-07-15 07:30:00' AND '2021-07-15 08:00:00' THEN 1 ELSE 0 END ) AS '16',
sum( CASE WHEN time BETWEEN '2021-07-15 08:00:00' AND '2021-07-15 08:30:00' THEN 1 ELSE 0 END ) AS '17',
sum( CASE WHEN time BETWEEN '2021-07-15 08:30:00' AND '2021-07-15 09:00:00' THEN 1 ELSE 0 END ) AS '18',
sum( CASE WHEN time BETWEEN '2021-07-15 09:00:00' AND '2021-07-15 09:30:00' THEN 1 ELSE 0 END ) AS '19',
sum( CASE WHEN time BETWEEN '2021-07-15 09:30:00' AND '2021-07-15 10:00:00' THEN 1 ELSE 0 END ) AS '20',
sum( CASE WHEN time BETWEEN '2021-07-15 10:00:00' AND '2021-07-15 10:30:00' THEN 1 ELSE 0 END ) AS '21',
sum( CASE WHEN time BETWEEN '2021-07-15 10:30:00' AND '2021-07-15 11:00:00' THEN 1 ELSE 0 END ) AS '22',
sum( CASE WHEN time BETWEEN '2021-07-15 11:00:00' AND '2021-07-15 11:30:00' THEN 1 ELSE 0 END ) AS '23',
sum( CASE WHEN time BETWEEN '2021-07-15 11:30:00' AND '2021-07-15 12:00:00' THEN 1 ELSE 0 END ) AS '24',
sum( CASE WHEN time BETWEEN '2021-07-15 12:00:00' AND '2021-07-15 12:30:00' THEN 1 ELSE 0 END ) AS '25',
sum( CASE WHEN time BETWEEN '2021-07-15 12:30:00' AND '2021-07-15 13:00:00' THEN 1 ELSE 0 END ) AS '26',
sum( CASE WHEN time BETWEEN '2021-07-15 13:00:00' AND '2021-07-15 13:30:00' THEN 1 ELSE 0 END ) AS '27',
sum( CASE WHEN time BETWEEN '2021-07-15 13:30:00' AND '2021-07-15 14:00:00' THEN 1 ELSE 0 END ) AS '28',
sum( CASE WHEN time BETWEEN '2021-07-15 14:00:00' AND '2021-07-15 14:30:00' THEN 1 ELSE 0 END ) AS '29',
sum( CASE WHEN time BETWEEN '2021-07-15 14:30:00' AND '2021-07-15 15:00:00' THEN 1 ELSE 0 END ) AS '30',
sum( CASE WHEN time BETWEEN '2021-07-15 15:00:00' AND '2021-07-15 15:30:00' THEN 1 ELSE 0 END ) AS '31',
sum( CASE WHEN time BETWEEN '2021-07-15 15:30:00' AND '2021-07-15 16:00:00' THEN 1 ELSE 0 END ) AS '32',
sum( CASE WHEN time BETWEEN '2021-07-15 16:00:00' AND '2021-07-15 16:30:00' THEN 1 ELSE 0 END ) AS '33',
sum( CASE WHEN time BETWEEN '2021-07-15 16:30:00' AND '2021-07-15 17:00:00' THEN 1 ELSE 0 END ) AS '34',
sum( CASE WHEN time BETWEEN '2021-07-15 17:00:00' AND '2021-07-15 17:30:00' THEN 1 ELSE 0 END ) AS '35',
sum( CASE WHEN time BETWEEN '2021-07-15 17:30:00' AND '2021-07-15 18:00:00' THEN 1 ELSE 0 END ) AS '36',
sum( CASE WHEN time BETWEEN '2021-07-15 18:00:00' AND '2021-07-15 18:30:00' THEN 1 ELSE 0 END ) AS '37',
sum( CASE WHEN time BETWEEN '2021-07-15 18:30:00' AND '2021-07-15 19:00:00' THEN 1 ELSE 0 END ) AS '38',
sum( CASE WHEN time BETWEEN '2021-07-15 19:00:00' AND '2021-07-15 19:30:00' THEN 1 ELSE 0 END ) AS '39',
sum( CASE WHEN time BETWEEN '2021-07-15 19:30:00' AND '2021-07-15 20:00:00' THEN 1 ELSE 0 END ) AS '40',
sum( CASE WHEN time BETWEEN '2021-07-15 20:00:00' AND '2021-07-15 20:30:00' THEN 1 ELSE 0 END ) AS '41',
sum( CASE WHEN time BETWEEN '2021-07-15 20:30:00' AND '2021-07-15 21:00:00' THEN 1 ELSE 0 END ) AS '42',
sum( CASE WHEN time BETWEEN '2021-07-15 21:00:00' AND '2021-07-15 21:30:00' THEN 1 ELSE 0 END ) AS '43',
sum( CASE WHEN time BETWEEN '2021-07-15 21:30:00' AND '2021-07-15 22:00:00' THEN 1 ELSE 0 END ) AS '44',
sum( CASE WHEN time BETWEEN '2021-07-15 22:00:00' AND '2021-07-15 22:30:00' THEN 1 ELSE 0 END ) AS '45',
sum( CASE WHEN time BETWEEN '2021-07-15 22:30:00' AND '2021-07-15 23:00:00' THEN 1 ELSE 0 END ) AS '46',
sum( CASE WHEN time BETWEEN '2021-07-15 23:00:00' AND '2021-07-15 23:30:00' THEN 1 ELSE 0 END ) AS '47',
sum( CASE WHEN time BETWEEN '2021-07-15 23:30:00' AND '2021-07-15 24:00:00' THEN 1 ELSE 0 END ) AS '48'

from potential_aaa111_60km_k11_11_21

 

SELECT
*
FROM
potential_aaa111_60km_k11_11_21
WHERE
potential1 = (SELECT MAX(potential1) FROM potential_aaa111_60km_k11_11_21 WHERE time like '2021-07-16%')
LIMIT 1;

 

 

SELECT
TIMESTAMPDIFF(MINUTE ,'2021-07-16',time) as min,potential1,time
FROM
potential_aaa111_60km_k11_11_21
WHERE
potential1 = (SELECT MAX(potential1) FROM potential_aaa111_60km_k11_11_21 WHERE time like '2021-07-16%')
LIMIT 1;