56,675
社区成员
发帖
与我相关
我的任务
分享
CREATE TABLE t3
( num INT PRIMARY KEY,
dayhour VARCHAR(20),
rain INT );
INSERT INTO t3 VALUES
(100001,'31d21h',1),
(100002,'31d22h',2),
(100003,'31d23h',(NULL)),
(100004,'31d24h',(NULL)),
(100005,'01d01h',(NULL)),
(100006,'01d02h',(NULL)),
(100007,'01d03h',5),
(100008,'01d04h',(NULL)),
(100009,'01d05h',5),
(100010,'01d06h',5),
(100011,'01d07h',(NULL)),
(100012,'01d08h',2),
(100013,'01d09h',6),
(100014,'01d10h',(NULL)),
(100015,'01d11h',(NULL)),
(100016,'01d12h',8),
(100017,'01d13h',(NULL)),
(100018,'01d14h',7),
(100019,'01d15h',(NULL)),
(100020,'01d16h',(NULL)),
(100021,'01d17h',7),
(100022,'01d18h',(NULL)),
(100023,'01d19h',(NULL)),
(100024,'01d20h',6),
(100025,'01d21h',(NULL)),
(100026,'01d22h',(NULL)),
(100027,'01d23h',5),
(100028,'01d24h',1),
(100029,'02d01h',(NULL)),
(100030,'02d02h',3),
(100031,'02d03h',(NULL)),
(100032,'02d04h',(NULL)),
(100033,'02d05h',(NULL)),
(100034,'02d06h',(NULL)),
(100035,'02d07h',(NULL)),
(100036,'02d08h',(NULL)),
(100037,'02d09h',(NULL)),
(100038,'02d10h',(NULL)),
(100039,'02d11h',(NULL)),
(100040,'02d12h',(NULL)),
(100041,'02d13h',(NULL)),
(100042,'02d14h',(NULL)),
(100043,'02d15h',(NULL)),
(100044,'02d16h',(NULL)),
(100045,'02d17h',(NULL)),
(100046,'02d18h',(NULL)),
(100047,'02d19h',(NULL)),
(100048,'02d20h',(NULL))
SELECT LEFT(dayhour,2)AS 天数, SUM(IF(SUBSTR(dayhour,4,2)='01',rain,(NULL)))AS 1h,
SUM(IF(SUBSTR(dayhour,4,2)='02',rain,(NULL)))AS 2h,
SUM(IF(SUBSTR(dayhour,4,2)='03',rain,(NULL)))AS 3h,
SUM(IF(SUBSTR(dayhour,4,2)='04',rain,(NULL)))AS 4h,
SUM(IF(SUBSTR(dayhour,4,2)='05',rain,(NULL)))AS 5h,
SUM(IF(SUBSTR(dayhour,4,2)='06',rain,(NULL)))AS 6h,
SUM(IF(SUBSTR(dayhour,4,2)='07',rain,(NULL)))AS 7h,
SUM(IF(SUBSTR(dayhour,4,2)='08',rain,(NULL)))AS 8h,
SUM(IF(SUBSTR(dayhour,4,2)='09',rain,(NULL)))AS 9h,
SUM(IF(SUBSTR(dayhour,4,2)='10',rain,(NULL)))AS 10h,
SUM(IF(SUBSTR(dayhour,4,2)='11',rain,(NULL)))AS 11h,
SUM(IF(SUBSTR(dayhour,4,2)='12',rain,(NULL)))AS 12h,
SUM(IF(SUBSTR(dayhour,4,2)='13',rain,(NULL)))AS 13h,
SUM(IF(SUBSTR(dayhour,4,2)='14',rain,(NULL)))AS 14h,
SUM(IF(SUBSTR(dayhour,4,2)='15',rain,(NULL)))AS 15h,
SUM(IF(SUBSTR(dayhour,4,2)='16',rain,(NULL)))AS 16h,
SUM(IF(SUBSTR(dayhour,4,2)='17',rain,(NULL)))AS 17h,
SUM(IF(SUBSTR(dayhour,4,2)='18',rain,(NULL)))AS 18h,
SUM(IF(SUBSTR(dayhour,4,2)='19',rain,(NULL)))AS 19h,
SUM(IF(SUBSTR(dayhour,4,2)='20',rain,(NULL)))AS 20h,
SUM(IF(SUBSTR(dayhour,4,2)='21',rain,(NULL)))AS 21h,
SUM(IF(SUBSTR(dayhour,4,2)='22',rain,(NULL)))AS 22h,
SUM(IF(SUBSTR(dayhour,4,2)='23',rain,(NULL)))AS 23h,
SUM(IF(SUBSTR(dayhour,4,2)='24',rain,(NULL)))AS 24h
FROM t3 GROUP BY LEFT(dayhour,2) ORDER BY num ASC
date_format(now(),'%Y_%c_%e'));
这样在统计时会方便很多
另外楼主的时间记录不分月份和年么,这样岂不是很混乱?SELECT @counter:=@counter+1 AS 序号, LEFT(dayhour,2) AS 天数 , GROUP_CONCAT(rain) AS 拼接
FROM t3,(SELECT @counter:=0)a GROUP BY LEFT(dayhour,2) ORDER BY num ASC