求一条SQL竖转横的语句?

lishnqing 2013-01-24 11:53:13
有三个字段,第一个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

...全文
318 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
2013-01-25
  • 打赏
  • 举报
回复
 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


lishnqing 2013-01-25
  • 打赏
  • 举报
回复
引用 3 楼 rucypli 的回复:
select sum(case when substring(dayhour,4,2)=21 then rain end) as 21, 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 ……
感谢你的帮助,但是你的语句是求和了,我希望得到的结果是每天一行。
lishnqing 2013-01-25
  • 打赏
  • 举报
回复
引用 1 楼 deathoctopus 的回复:
楼主你的时间顺序是指从今天的21时开始到明天的20时结束? 然后序号的 1 1 2 5 5 5 2 6 8 7 7 6 2 3 这一排没看懂。。
因为有些时次的值为空,所以排列出来没对齐
rucypli 2013-01-25
  • 打赏
  • 举报
回复
select sum(case when substring(dayhour,4,2)=21 then rain end) as 21, 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
ACMAIN_CHM 2013-01-25
  • 打赏
  • 举报
回复
http://blog.csdn.net/acmain_chm/article/details/4283943 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...
deathoctopus 2013-01-25
  • 打赏
  • 举报
回复
楼主你的时间顺序是指从今天的21时开始到明天的20时结束? 然后序号的 1 1 2 5 5 5 2 6 8 7 7 6 2 3 这一排没看懂。。
lishnqing 2013-01-25
  • 打赏
  • 举报
回复
引用 16 楼 WWWWA 的回复:
假设有: 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 ……
谢谢,成功了
WWWWA 2013-01-25
  • 打赏
  • 举报
回复
假设有: 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
WWWWA 2013-01-25
  • 打赏
  • 举报
回复
从21时开始到20时:每1个时间都有记录?
lishnqing 2013-01-25
  • 打赏
  • 举报
回复
引用 13 楼 WWWWA 的回复:
数据都有顺序吗?即从从21时开始到20时,都是按顺序排列的, 从21时开始到20时:第1个时间都有记录?
对,都是按顺序排列的
WWWWA 2013-01-25
  • 打赏
  • 举报
回复
数据都有顺序吗?即从从21时开始到20时,都是按顺序排列的, 从21时开始到20时:第1个时间都有记录?
lishnqing 2013-01-25
  • 打赏
  • 举报
回复
引用 11 楼 deathoctopus 的回复:
时间段的问题感觉还是记录成时间格式比较方面,字段定义成datetime类,每天整点写入 或者利用mysql的函数截取整点时间 如下,具体的使用方式可以参考手册 SQL code ? 1 date_format(now(),'%Y_%c_%e')); 这样在统计时会方便很多 另外楼主的时间记录不分月份和年么,这样岂不是很混乱?
我也想加日期啊,太麻烦了,因为这些数据是经过两次转换才弄出来的。现在成这样了,在这来寻高手帮忙。我的想法是,把每24条记录进行分组,然后转置,就是想不到怎么写。
deathoctopus 2013-01-25
  • 打赏
  • 举报
回复
时间段的问题感觉还是记录成时间格式比较方面,字段定义成datetime类,每天整点写入 或者利用mysql的函数截取整点时间 如下,具体的使用方式可以参考手册
date_format(now(),'%Y_%c_%e'));
这样在统计时会方便很多 另外楼主的时间记录不分月份和年么,这样岂不是很混乱?
2013-01-25
  • 打赏
  • 举报
回复
呵呵 很抱歉 没想到办法。。。dayhour这个字段目前的内容 没想到怎么拆分出上一天的21点到今天的20点。。。感觉你可以重构下表结构来实现
2013-01-25
  • 打赏
  • 举报
回复
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




难道要的是这个?
lishnqing 2013-01-25
  • 打赏
  • 举报
回复
比如下面,我就是不知道这两个问号该是什么? 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 ?;
lishnqing 2013-01-25
  • 打赏
  • 举报
回复
引用 6 楼 rollet 的回复:
SQL code ? 1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980 CREATE TABLE t3 ( num I……
首先感谢6楼,有点接近,但还差一点。第一列为序号即可,希望从头到尾的每24个记录的rain组合成一行,比如1号,应包括31日21时至1日20时。

56,675

社区成员

发帖
与我相关
我的任务
社区描述
MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧