数据排列问题，求SQL语句，在线等！

2007-12-19 05:03:11 88 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

表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

...全文

老杨_sz

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

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 行受影响)

*/