select name,time from a where name='张三' and date='日期'我想查询出每天每个员工的漏打卡记录(每天最多有四条)
定义四个变量A1,A2,A3,A4 怎样做到,如果没有的话,四个变量为空,如果有一条,将TIME值赋到A1中,有二条分别赋到A1,A2..
以此类推
定义四个变量A1,A2,A3,A4 怎样做到,如果没有的话,四个变量为空,如果有一条,将TIME值赋到A1中,有二条分别赋到A1,A2..
以此类推
解决方案 »
- SQL的文件组中mdf与ndf的区别
- 急求!VB连接SQL采用WINDOWS信任连接时的问题!!
- 重装SQL Server 2005,装好后看不到 SQL Server Manager Stdio ,咋整?设置的时候,选的默认实例,但是 SQL Server 服务在高级选项里面改了盘符,装到D盘的,跟这个有没有关系?
- 请问MS SQL建表问题
- 今天把服务器换到WINDOWS2008上出现的问题,来看看
- 生成表新纪录 adapter.Update(dt) 和 insert 语句 哪个性能高
- 【MSSQL】多主键情况下查询AB两表不同数据
- Sql中如何判断表中某一字段的纪录为空
- 这样的sql语句怎么写?
- 求帮写一条修改SQL语句(在线等待)
- 如何建立分区表带上原来的表结构和数据呢?
- 如何根据给定的两个整数生成一列,从小到大的顺序,并分成两列显示?
insert @t select name,time from a where name='张三' and date='日期'
select * from @T
max(case px when 1 then time end) A1,
max(case px when 2 then time end) A2,
max(case px when 3 then time end) A3,
max(case px when 4 then time end) A4
from
(
select name , convert(varchar(10),time,120) [day] , time , px = (select count(1) from tb where name = t.name and convert(varchar(10),time,120) = convert(varchar(10),t.time,120) and and time < t.time) + 1 from tb t
) m
group by name , [day]
max(case px when 1 then time end) A1,
max(case px when 2 then time end) A2,
max(case px when 3 then time end) A3,
max(case px when 4 then time end) A4
from
(
select name , convert(varchar(10),time,120) [day] , time , px = (select count(1) from tb where name = t.name and convert(varchar(10),time,120) = convert(varchar(10),t.time,120) and time < t.time) + 1 from tb t
) m
group by name , [day]
insert into tb values('A' , '2008-12-10 01:00:00')
insert into tb values('A' , '2008-12-10 02:00:00')
insert into tb values('A' , '2008-12-10 03:00:00')
insert into tb values('A' , '2008-12-10 04:00:00')
insert into tb values('A' , '2008-12-11 11:00:00')
insert into tb values('A' , '2008-12-11 12:00:00')
insert into tb values('A' , '2008-12-11 13:00:00')
insert into tb values('A' , '2008-12-11 14:00:00')
insert into tb values('b' , '2008-12-10 21:00:00')
insert into tb values('b' , '2008-12-10 22:00:00')
insert into tb values('b' , '2008-12-10 23:00:00')
insert into tb values('b' , '2008-12-10 23:50:00')
go
--原始数据
select * from tb
/*
name time
---------- ------------------------------------------------------
A 2008-12-10 01:00:00.000
A 2008-12-10 02:00:00.000
A 2008-12-10 03:00:00.000
A 2008-12-10 04:00:00.000
A 2008-12-11 11:00:00.000
A 2008-12-11 12:00:00.000
A 2008-12-11 13:00:00.000
A 2008-12-11 14:00:00.000
b 2008-12-10 21:00:00.000
b 2008-12-10 22:00:00.000
b 2008-12-10 23:00:00.000
b 2008-12-10 23:50:00.000*/select name , [day] ,
max(case px when 1 then time end) A1,
max(case px when 2 then time end) A2,
max(case px when 3 then time end) A3,
max(case px when 4 then time end) A4
from
(
select name , convert(varchar(10),time,120) [day] , time , px = (select count(1) from tb where name = t.name and convert(varchar(10),time,120) = convert(varchar(10),t.time,120) and time < t.time) + 1 from tb t
) m
group by name , [day]drop table tb/*
name day A1 A2 A3 A4
---------- ---------- ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------ ------------------------------------------------------
A 2008-12-10 2008-12-10 01:00:00.000 2008-12-10 02:00:00.000 2008-12-10 03:00:00.000 2008-12-10 04:00:00.000
b 2008-12-10 2008-12-10 21:00:00.000 2008-12-10 22:00:00.000 2008-12-10 23:00:00.000 2008-12-10 23:50:00.000
A 2008-12-11 2008-12-11 11:00:00.000 2008-12-11 12:00:00.000 2008-12-11 13:00:00.000 2008-12-11 14:00:00.000(所影响的行数为 3 行)
*/