有三个字段,第一个num(编号);第二个为dayhour(日和时),从21时开始到20时结束;第三个为rain(值),现在想把每24个记录的rain横向排列,即从21时开始到20时,然后依次向下排列,求解答!部分数据如下:
num dayhour rain
100001 31d21h 1
100002 31d22h 2
100003 31d23h
100004 31d24h
100005 01d01h
100006 01d02h
100007 01d03h 5
100008 01d04h
100009 01d05h 5
100010 01d06h 5
100011 01d07h
100012 01d08h 2
100013 01d09h 6
100014 01d10h
100015 01d11h
100016 01d12h 8
100017 01d13h
100018 01d14h 7
100019 01d15h
100020 01d16h
100021 01d17h 7
100022 01d18h
100023 01d19h
100024 01d20h 6
100025 01d21h
100026 01d22h
100027 01d23h 5
100028 01d24h 1
100029 02d01h
100030 02d02h 3
100031 02d03h
100032 02d04h
100033 02d05h
100034 02d06h
100035 02d07h
100036 02d08h
100037 02d09h
100038 02d10h
100039 02d11h
100040 02d12h
100041 02d13h
100042 02d14h
100043 02d15h
100044 02d16h
100045 02d17h
100046 02d18h
100047 02d19h
100048 02d20h 需要如下的结果:
序号 21 22 23 24 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
1 1 2 5 5 5 2 6 8 7 7 6
2
3
num dayhour rain
100001 31d21h 1
100002 31d22h 2
100003 31d23h
100004 31d24h
100005 01d01h
100006 01d02h
100007 01d03h 5
100008 01d04h
100009 01d05h 5
100010 01d06h 5
100011 01d07h
100012 01d08h 2
100013 01d09h 6
100014 01d10h
100015 01d11h
100016 01d12h 8
100017 01d13h
100018 01d14h 7
100019 01d15h
100020 01d16h
100021 01d17h 7
100022 01d18h
100023 01d19h
100024 01d20h 6
100025 01d21h
100026 01d22h
100027 01d23h 5
100028 01d24h 1
100029 02d01h
100030 02d02h 3
100031 02d03h
100032 02d04h
100033 02d05h
100034 02d06h
100035 02d07h
100036 02d08h
100037 02d09h
100038 02d10h
100039 02d11h
100040 02d12h
100041 02d13h
100042 02d14h
100043 02d15h
100044 02d16h
100045 02d17h
100046 02d18h
100047 02d19h
100048 02d20h 需要如下的结果:
序号 21 22 23 24 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20
1 1 2 5 5 5 2 6 8 7 7 6
2
3
MySQL交叉表
在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198现整理解法如下:数据样本: create table tx( id int primary key, c1 c...
sum(case when substring(dayhour,4,2)=22 then rain end) as 22,
...
..
sum(case when substring(dayhour,4,2)=20 then rain end) as 20
from tb
( 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
select ? if(mid(`dayhour`,4,2)=21,`rain`,0) as `21`,
if(mid(`dayhour`,4,2)=22,`rain`,0) as `22`,
if(mid(`dayhour`,4,2)=23,`rain`,0) as `23`,
if(mid(`dayhour`,4,2)=24,`rain`,0) as `24`,
if(mid(`dayhour`,4,2)=1,`rain`,0) as `1`,
if(mid(`dayhour`,4,2)=2,`rain`,0) as `2`,
if(mid(`dayhour`,4,2)=3,`rain`,0) as `3`,
if(mid(`dayhour`,4,2)=4,`rain`,0) as `4`,
if(mid(`dayhour`,4,2)=5,`rain`,0) as `5`,
if(mid(`dayhour`,4,2)=6,`rain`,0) as `6`,
if(mid(`dayhour`,4,2)=7,`rain`,0) as `7`,
if(mid(`dayhour`,4,2)=8,`rain`,0) as `8`,
if(mid(`dayhour`,4,2)=9,`rain`,0) as `9`,
if(mid(`dayhour`,4,2)=10,`rain`,0) as `10`,
if(mid(`dayhour`,4,2)=11,`rain`,0) as `11`,
if(mid(`dayhour`,4,2)=12,`rain`,0) as `12`,
if(mid(`dayhour`,4,2)=13,`rain`,0) as `13`,
if(mid(`dayhour`,4,2)=14,`rain`,0) as `14`,
if(mid(`dayhour`,4,2)=15,`rain`,0) as `15`,
if(mid(`dayhour`,4,2)=16,`rain`,0) as `16`,
if(mid(`dayhour`,4,2)=17,`rain`,0) as `17`,
if(mid(`dayhour`,4,2)=18,`rain`,0) as `18`,
if(mid(`dayhour`,4,2)=19,`rain`,0) as `19`,
if(mid(`dayhour`,4,2)=20,`rain`,0) as `20` from 2006to2012rain
group by ?;
FROM t3,(SELECT @counter:=0)a GROUP BY LEFT(dayhour,2) ORDER BY num ASC难道要的是这个?
date_format(now(),'%Y_%c_%e'));这样在统计时会方便很多另外楼主的时间记录不分月份和年么,这样岂不是很混乱?
SET @num=0;
select bz,
max(if(mid(`dayhour`,4,2)=21,`rain`,0)) as `21`,
max(if(mid(`dayhour`,4,2)=22,`rain`,0)) as `22`,
......
from (SELECT *,CEILING((@num:=@num+1)/24) AS bz FROM t3) a group by bz