sql 怎么写 ！！！！！！！ 20+70

[hailiu123]

http://topic.csdn.net/u/20101006/14/6146b3c8-aee4-4154-8fe2-0feb4ca6e27e.html

[claro]

90?

[claro]

帮顶，linux下无法测试数据。

[siegebaoniu]

挺难的！

[hao1hao2hao3]

见另外一个帖子的回复。用的是SQL2005及以上的写法。

[「已注销」]

drop table #tab
create table #tab
(
	id int identity,
	time datetime,
	userid varchar(10)
)

insert into #tab select  '2010-9-28 14:12:04', '001'
insert into #tab select  '2010-9-28 14:12:22', '002'
insert into #tab select  '2010-9-28 14:12:23', '001'
insert into #tab select  '2010-9-28 16:16:06', '001'
insert into #tab select  '2010-9-28 21:12:04', '002'
insert into #tab select  '2010-9-28 13:12:04', '001'

select userid 人员 ,convert(varchar(10),time,120) 日期,
max(case when row=1 then substring(convert(varchar(22),[time],121),12,8) else '' end) AS  一次时间,
max(case when row=2 then substring(convert(varchar(22),[time],121),12,8) else '' end) AS  二次时间,
max(case when row=3 then substring(convert(varchar(22),[time],121),12,8) else '' end) AS  三次时间,
max(case when row=4 then substring(convert(varchar(22),[time],121),12,8) else '' end) AS  四次时间
from(
	select userid,time ,row_number()over(partition by userid order by getdate()) row
	from #tab)M
GROUP BY userid,convert(varchar(10),time,120)

人员         日期         一次时间             二次时间             三次时间             四次时间
---------- ---------- ---------------- ---------------- ---------------- ----------------
001        2010-09-28 14:12:04         14:12:23         16:16:06         13:12:04
002        2010-09-28 21:12:04         14:12:22                          

(2 row(s) affected)

[闹铃]

use tempdb;
create table dbo.tb
(
  id int identity(1,1),
  dtime datetime,
  userid varchar(5)
)
insert into dbo.tb
select '2010-9-29 14:12:04','001' union all
select '2010-9-29 14:12:22','002' union all
select '2010-9-29 14:12:23','001' union all
select '2010-9-29 16:16:06','001' union all
select '2010-9-29 21:12:04','002' union all
select '2010-9-29 13:12:04','001';

select userid,convert(char(10),min(dtime),120)as data,
	   MAX(case when row=1 then CONVERT(char(12),dtime,114)else '' end)as onetime,
	   MAX(case when row=2 then CONVERT(char(12),dtime,114)else '' end)as twotime,
	   MAX(case when row=3 then CONVERT(char(12),dtime,114)else '' end)as threetime,
	   MAX(case when row=4 then CONVERT(char(12),dtime,114)else '' end)as fourtime
from (select *,
      ROW_NUMBER()over(partition by userid order by dtime)as row
      from dbo.tb)as t
group by userid;
/**
userid data       onetime      twotime      threetime    fourtime
------ ---------- ------------ ------------ ------------ ------------
001    2010-09-29 13:12:04:000 14:12:04:000 14:12:23:000 16:16:06:000
002    2010-09-29 14:12:22:000 21:12:04:000              

(2 行受影响)
**/

[「已注销」]

declare @sql nvarchar(max)
set @sql=N''
set @sql= @sql + N'select userid 人员 ,convert(varchar(10),time,120) 日期,'+char(10)
set @sql= @sql + N'max(case when row=1 then substring(convert(varchar(22),[time],121),12,8) else '''' end) AS  一次时间,'+char(10)
set @sql= @sql + N'max(case when row=2 then substring(convert(varchar(22),[time],121),12,8) else '''' end) AS  二次时间,'+char(10)
set @sql= @sql + N'max(case when row=3 then substring(convert(varchar(22),[time],121),12,8) else '''' end) AS  三次时间,'+char(10)
set @sql= @sql + N'max(case when row=4 then substring(convert(varchar(22),[time],121),12,8) else '''' end) AS  四次时间'+char(10)
set @sql= @sql + N'from('+char(10)
set @sql= @sql + N'    select userid,time ,row_number()over(partition by userid order by getdate()) row'+char(10)
set @sql= @sql + N'    from #tab )M '+char(10)
set @sql= @sql + N'GROUP BY userid,convert(varchar(10),time,120)'

----print @sql
exec(@sql)

[闹铃]

--上一种方法　不适合同一userid有多个不同时间的情况，稍做修改如下
use tempdb;
create table dbo.tb
(
  id int identity(1,1),
  dtime datetime,
  userid varchar(5)
)
insert into dbo.tb
select '2010-9-27 14:12:04','001' union all
select '2010-9-27 14:12:22','002' union all
select '2010-9-27 14:12:23','001' union all
select '2010-9-27 16:16:06','001' union all
select '2010-9-27 21:12:04','002' union all
select '2010-9-27 13:12:04','001' union all
select '2010-9-28 14:12:04','001' union all
select '2010-9-28 14:12:22','002' union all
select '2010-9-28 14:12:23','001' union all
select '2010-9-28 16:16:06','001' union all
select '2010-9-28 21:12:04','002' union all
select '2010-9-28 13:12:04','001' union all
select '2010-9-29 14:12:04','001' union all
select '2010-9-29 14:12:22','002' union all
select '2010-9-29 14:12:23','001' union all
select '2010-9-29 16:16:06','001' union all
select '2010-9-29 21:12:04','002' union all
select '2010-9-29 13:12:04','001' ;

select userid,convert(char(10),min(dtime),120)as data,
	   MAX(case when row=1 then CONVERT(char(12),dtime,114)else '' end)as onetime,
	   MAX(case when row=2 then CONVERT(char(12),dtime,114)else '' end)as twotime,
	   MAX(case when row=3 then CONVERT(char(12),dtime,114)else '' end)as threetime,
	   MAX(case when row=4 then CONVERT(char(12),dtime,114)else '' end)as fourtime
from (select *,
      ROW_NUMBER()over(partition by userid,day(dtime) order by dtime)as row
      from dbo.tb)as t
group by userid,DAY(dtime)
order by userid;
/**
userid data       onetime      twotime      threetime    fourtime
------ ---------- ------------ ------------ ------------ ------------
001    2010-09-27 13:12:04:000 14:12:04:000 14:12:23:000 16:16:06:000
001    2010-09-28 13:12:04:000 14:12:04:000 14:12:23:000 16:16:06:000
001    2010-09-29 13:12:04:000 14:12:04:000 14:12:23:000 16:16:06:000
002    2010-09-27 14:12:22:000 21:12:04:000              
002    2010-09-28 14:12:22:000 21:12:04:000              
002    2010-09-29 14:12:22:000 21:12:04:000              

(6 行受影响)
**/

[wstkkan]