导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

数据排列问题,求SQL语句,在线等!

老杨_sz 比亚迪股份有限公司 运维工程师/系统管理员/网络管理员  2007-12-19 05:03:11
表A

no name date
---------------------------------------
0001 张三 2007-11-01 07:52:13.000
0001 张三 2007-11-01 12:04:26.000
0001 张三 2007-11-01 12:10:10.000
0001 张三 2007-11-01 17:04:33.000
0001 张三 2007-11-02 07:41:44.000
0002 李四 2007-11-02 08:09:56.000
0002 李四 2007-11-02 12:15:48.000
0001 张三 2007-11-02 12:16:59.000
0001 张三 2007-11-02 12:25:32.000
0002 李四 2007-11-02 12:25:59.000
0002 李四 2007-11-02 17:32:18.000
0001 张三 2007-11-02 17:38:54.000
0001 张三 2007-11-03 07:47:25.000
0002 李四 2007-11-03 08:17:10.000
0001 张三 2007-11-03 12:01:46.000
0001 张三 2007-11-03 12:05:07.000
0002 李四 2007-11-03 12:36:57.000
0002 李四 2007-11-03 12:52:22.000
0001 张三 2007-11-03 17:03:12.000
0002 李四 2007-11-03 17:33:51.000

如何通过SQL语句查询到如下结果:

no name date t1 t2 t3 t4 ... t10
---------------------------------------------------------------
0001 张三 2007-11-01 07:52 12:04 12:10 17:04
2007-11-02 07:41 12:16 12:25 17:38
2007-11-03 07:47 12:01 12:05 17:03
0002 李四 2007-11-02 08:09 12:15 12:25 17:32
2007-11-03 08:17 12:36 12:52 17:33
...全文
38 点赞 收藏 2
写回复
2 条回复
切换为时间正序
请发表友善的回复…
发表回复
dawugui 2007-12-19
create table tb(no varchar(10) , name varchar(10) , date datetime)
insert into tb values('0001', '张三', '2007-11-01 07:52:13.000')
insert into tb values('0001', '张三', '2007-11-01 12:04:26.000')
insert into tb values('0001', '张三', '2007-11-01 12:10:10.000')
insert into tb values('0001', '张三', '2007-11-01 17:04:33.000')
insert into tb values('0001', '张三', '2007-11-02 07:41:44.000')
insert into tb values('0002', '李四', '2007-11-02 08:09:56.000')
insert into tb values('0002', '李四', '2007-11-02 12:15:48.000')
insert into tb values('0001', '张三', '2007-11-02 12:16:59.000')
insert into tb values('0001', '张三', '2007-11-02 12:25:32.000')
insert into tb values('0002', '李四', '2007-11-02 12:25:59.000')
insert into tb values('0002', '李四', '2007-11-02 17:32:18.000')
insert into tb values('0001', '张三', '2007-11-02 17:38:54.000')
insert into tb values('0001', '张三', '2007-11-03 07:47:25.000')
insert into tb values('0002', '李四', '2007-11-03 08:17:10.000')
insert into tb values('0001', '张三', '2007-11-03 12:01:46.000')
insert into tb values('0001', '张三', '2007-11-03 12:05:07.000')
insert into tb values('0002', '李四', '2007-11-03 12:36:57.000')
insert into tb values('0002', '李四', '2007-11-03 12:52:22.000')
insert into tb values('0001', '张三', '2007-11-03 17:03:12.000')
insert into tb values('0002', '李四', '2007-11-03 17:33:51.000')
go

--静态SQL,指一个人每天的时间数量确定,如最多为10个。
select no , name , convert(varchar(10),date,120) date,
max(case px when 1 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T1',
max(case px when 2 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T2',
max(case px when 3 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T3',
max(case px when 4 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T4',
max(case px when 5 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T5',
max(case px when 6 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T6',
max(case px when 7 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T7',
max(case px when 8 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T8',
max(case px when 9 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T9',
max(case px when 10 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T10'
from
(
select * , px = (select count(1) from tb where no = a.no and name = a.name and convert(varchar(10),date,120) = convert(varchar(10),a.date,120) and date < a.date) + 1 from tb a
) t
group by no , name , convert(varchar(10),date,120)
/*
no name date T1 T2 T3 T4 T5 T6 T7 T8 T9 T10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0001 张三 2007-11-01 07:52 12:04 12:10 17:04
0001 张三 2007-11-02 07:41 12:16 12:25 17:38
0001 张三 2007-11-03 07:47 12:01 12:05 17:03
0002 李四 2007-11-02 08:09 12:15 12:25 17:32
0002 李四 2007-11-03 08:17 12:36 12:52 17:33

(5 行受影响)
*/

--动态SQL,指一个人每天的时间数量不确定
declare @sql varchar(8000)
set @sql = 'select no , name , convert(varchar(10),date,120) date'
select @sql = @sql + ' , max(case px when ''' + cast(px as varchar) + ''' then substring(convert(varchar(20),date,120),12,5) else '' '' end) [T' + cast(px as varchar) + ']'
from (select distinct px from (select * , px = (select count(1) from tb where no = a.no and name = a.name and convert(varchar(10),date,120) = convert(varchar(10),a.date,120) and date < a.date) + 1 from tb a) t) as a
set @sql = @sql + ' from (select * , px = (select count(1) from tb where no = a.no and name = a.name and convert(varchar(10),date,120) = convert(varchar(10),a.date,120) and date < a.date) + 1 from tb a) t group by no , name , convert(varchar(10),date,120)'
exec(@sql)
/*
no name date T1 T2 T3 T4
---------- ---------- ---------- ---------- ---------- ---------- ----------
0001 张三 2007-11-01 07:52 12:04 12:10 17:04
0001 张三 2007-11-02 07:41 12:16 12:25 17:38
0001 张三 2007-11-03 07:47 12:01 12:05 17:03
0002 李四 2007-11-02 08:09 12:15 12:25 17:32
0002 李四 2007-11-03 08:17 12:36 12:52 17:33

(5 行受影响)
*/

drop table tb

回复
dawugui 2007-12-19
create table tb(no varchar(10) , name varchar(10) , date datetime)
insert into tb values('0001', '张三', '2007-11-01 07:52:13.000')
insert into tb values('0001', '张三', '2007-11-01 12:04:26.000')
insert into tb values('0001', '张三', '2007-11-01 12:10:10.000')
insert into tb values('0001', '张三', '2007-11-01 17:04:33.000')
insert into tb values('0001', '张三', '2007-11-02 07:41:44.000')
insert into tb values('0002', '李四', '2007-11-02 08:09:56.000')
insert into tb values('0002', '李四', '2007-11-02 12:15:48.000')
insert into tb values('0001', '张三', '2007-11-02 12:16:59.000')
insert into tb values('0001', '张三', '2007-11-02 12:25:32.000')
insert into tb values('0002', '李四', '2007-11-02 12:25:59.000')
insert into tb values('0002', '李四', '2007-11-02 17:32:18.000')
insert into tb values('0001', '张三', '2007-11-02 17:38:54.000')
insert into tb values('0001', '张三', '2007-11-03 07:47:25.000')
insert into tb values('0002', '李四', '2007-11-03 08:17:10.000')
insert into tb values('0001', '张三', '2007-11-03 12:01:46.000')
insert into tb values('0001', '张三', '2007-11-03 12:05:07.000')
insert into tb values('0002', '李四', '2007-11-03 12:36:57.000')
insert into tb values('0002', '李四', '2007-11-03 12:52:22.000')
insert into tb values('0001', '张三', '2007-11-03 17:03:12.000')
insert into tb values('0002', '李四', '2007-11-03 17:33:51.000')
go
select no , name , convert(varchar(10),date,120) date,
max(case px when 1 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T1',
max(case px when 2 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T2',
max(case px when 3 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T3',
max(case px when 4 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T4',
max(case px when 5 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T5',
max(case px when 6 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T6',
max(case px when 7 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T7',
max(case px when 8 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T8',
max(case px when 9 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T9',
max(case px when 10 then substring(convert(varchar(20),date,120),12,5) else '' end) 'T10'
from
(
select * , px = (select count(1) from tb where no = a.no and name = a.name and convert(varchar(10),date,120) = convert(varchar(10),a.date,120) and date < a.date) + 1 from tb a
) t
group by no , name , convert(varchar(10),date,120)

drop table tb

/*
no name date T1 T2 T3 T4 T5 T6 T7 T8 T9 T10
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
0001 张三 2007-11-01 07:52 12:04 12:10 17:04
0001 张三 2007-11-02 07:41 12:16 12:25 17:38
0001 张三 2007-11-03 07:47 12:01 12:05 17:03
0002 李四 2007-11-02 08:09 12:15 12:25 17:32
0002 李四 2007-11-03 08:17 12:36 12:52 17:33

(5 行受影响)
*/
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告