SQL查询语句问题

[wp_wuming]

现有考勤数据表字段如下
工号 日期 打卡时间 打卡类型（1为上班卡，2为下班卡） 状态
001 2014-05-06 08:30 1 正常
001 2014-05-06 17:36 2 正常
我如何查询得到如下表这样的信息
工号 日期 上班打卡时间 下班打卡时间 状态
001 2014-05-06 08:30 17:36 正常
就是把上班打卡时间和下班打卡时间放在同一行，以前是没打一次卡一条记录

[wp_wuming]

引用 4 楼 DBAXMMDS 的回复:

--创建表
if object_id('tempdb..#a','U') is not null
drop table #a ;
go
create table #a 
(
workID char(3),
Date1 DATE,
time1 time(0),
WorkType tinyint,
WorkState char(4)
)
--插入数据
insert into #a (WorkID,Date1,Time1,WorkType,WorkState)
      values ('001','2014-05-06','08:30',1,'正常'),
 ('001','2014-05-06','17:36',2,'正常');
 --语句
 select a.workid as [工号],a.date1 as [日期],a.time1 as [上班打卡时间],b.time1[下班打卡时间],a.workstate as [状态]
  from #a  as a inner join #a as b on a.workID = b.workID and a.date1 = b.date1 and a.Worktype = b.worktype+1 and b.workState ='正常'
 where a.workState = '正常'

 --结果展示
 /*
工号   日期         上班打卡时间   下班打卡时间           状态
---- ---------- ---------------- ----------------         ----
001  2014-05-06 17:36:00         08:30:00                 正常

(1 行受影响)


 */

好像不对吧

[發糞塗牆]

加case when 判断

[app01188]

--创建表 if object_id('tempdb..#a1','U') is not null drop table #a1 ; go create table #a1 ( workID char(3), Date1 DATE, time1 time(0), WorkType tinyint, WorkState char(8) ) --插入数据 insert into #a1(WorkID,Date1,Time1,WorkType,WorkState) values ('001','2014-05-05','08:30',1,'正常'), ('001','2014-05-06','08:30',1,'正常'), ('001','2014-05-07','08:30',1,'正常'), ('001','2014-05-08','08:30',1,'正常'), ('001','2014-05-05','18:30',2,'正常'), ('001','2014-05-06','18:30',2,'正常'), -- ('001','2014-05-07','18:30',2,'正常'), 漏打卡 ('001','2014-05-08','17:36',2,'不正常'); -- 迟到 -- 创建上班日期表 if object_id('tempdb..#a2','U1') is not null drop table #a2 ; go create table #a2 ( Date1 DATE) insert into #a2(Date1) values ('2014-05-05'),('2014-05-06'),('2014-05-07'),('2014-05-08'); --语句 select a.workid as [工号],c.date1 as [日期],a.time1 as [上班打卡时间],b.time1[下班打卡时间] ,case when a.workstate='正常' and b.WorkState='正常' then '正常' else '不正常' end as [状态] from #a2 as c left join (select * from #a1 where worktype=1) a on a.Date1 = c.Date1 left join (select * from #a1 where worktype=2) b on b.Date1 = c.Date1 and a.Date1=b.Date1 where a.WorkID ='001' and b.workID='001'

[wp_wuming]

引用 7 楼 app01188 的回复:

--创建表 if object_id('tempdb..#a1','U') is not null drop table #a1 ; go create table #a1 ( workID char(3), Date1 DATE, time1 time(0), WorkType tinyint, WorkState char(8) ) --插入数据 insert into #a1(WorkID,Date1,Time1,WorkType,WorkState) values ('001','2014-05-05','08:30',1,'正常'), ('001','2014-05-06','08:30',1,'正常'), ('001','2014-05-07','08:30',1,'正常'), ('001','2014-05-08','08:30',1,'正常'), ('001','2014-05-05','18:30',2,'正常'), ('001','2014-05-06','18:30',2,'正常'), -- ('001','2014-05-07','18:30',2,'正常'), 漏打卡 ('001','2014-05-08','17:36',2,'不正常'); -- 迟到 -- 创建上班日期表 if object_id('tempdb..#a2','U1') is not null drop table #a2 ; go create table #a2 ( Date1 DATE) insert into #a2(Date1) values ('2014-05-05'),('2014-05-06'),('2014-05-07'),('2014-05-08'); --语句 select a.workid as [工号],c.date1 as [日期],a.time1 as [上班打卡时间],b.time1[下班打卡时间] ,case when a.workstate='正常' and b.WorkState='正常' then '正常' else '不正常' end as [状态] from #a2 as c left join (select * from #a1 where worktype=1) a on a.Date1 = c.Date1 left join (select * from #a1 where worktype=2) b on b.Date1 = c.Date1 and a.Date1=b.Date1 where a.WorkID ='001' and b.workID='001'

5月7日的怎么没数据，不是只漏打了下班卡吗

[wp_wuming]

引用 6 楼 DBA_Huangzj 的回复:

加case when 判断

引用 2 楼 HEROWANG 的回复:

--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
 go
 create table [ta] ([工号] varchar(3),[日期] datetime,[打卡时间] varchar(10),[打卡类型] varchar(4),[状态] sql_variant)
insert into [ta]
select '001','2014-05-06', '08:30',1,'正常' union all
select '001','2014-05-06', '17:36',2,'正常'

select 工号,日期,上班打卡时间=max(case when 打卡类型=1 then 打卡时间 end),
                下班打卡时间=max(case when 打卡类型=2 then 打卡时间 end)
          
from ta t
group by 工号,日期

001	2014-05-06 00:00:00.000	08:30	17:36

能帮忙写一下判断吗

[發糞塗牆]

我假设8点30为上班时间 ，17：30为下班时间

--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
 go
 create table [ta] ([工号] varchar(3),[日期] datetime,[打卡时间] varchar(10),[打卡类型] varchar(4),[状态] NVARCHAR(10))
insert into [ta]
select '001','2014-05-06', '08:30',1,N'正常' union all
select '001','2014-05-06', '17:36',2,N'正常' UNION ALL 
select '002','2014-05-06', '08:50',1,N'正常' union all
select '002','2014-05-06', '17:36',2,N'正常'
SELECT *,CASE WHEN 上班打卡时间>'08:30' AND 下班打卡时间>='17:30' THEN N'迟到' ELSE N'正常' END [状态]
FROM (
SELECT  工号 ,
        日期 ,
        上班打卡时间 = MAX(CASE WHEN 打卡类型 = 1 THEN 打卡时间
                     END) ,
        下班打卡时间 = MAX(CASE WHEN 打卡类型 = 2 THEN 打卡时间
                     END)
    
FROM    ta t
GROUP BY 工号 ,
        日期 )a




/*
工号   日期                      上班打卡时间     下班打卡时间     状态
---- ----------------------- ---------- ---------- ----
001  2014-05-06 00:00:00.000 08:30      17:36      正常
002  2014-05-06 00:00:00.000 08:50      17:36      迟到
*/

[xdashewan]

请使用表自联，一个子查询查上班，另一个查下班，自联条件是工号和日期

[让你望见影子的墙]

--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
 go
 create table [ta] ([工号] varchar(3),[日期] datetime,[打卡时间] varchar(10),[打卡类型] varchar(4),[状态] sql_variant)
insert into [ta]
select '001','2014-05-06', '08:30',1,'正常' union all
select '001','2014-05-06', '17:36',2,'正常'

select 工号,日期,上班打卡时间=max(case when 打卡类型=1 then 打卡时间 end),
                下班打卡时间=max(case when 打卡类型=2 then 打卡时间 end)
          
from ta t
group by 工号,日期

001	2014-05-06 00:00:00.000	08:30	17:36

[wp_wuming]

引用 2 楼 HEROWANG 的回复:

--> 测试数据: [ta]
if object_id('[ta]') is not null drop table [ta]
 go
 create table [ta] ([工号] varchar(3),[日期] datetime,[打卡时间] varchar(10),[打卡类型] varchar(4),[状态] sql_variant)
insert into [ta]
select '001','2014-05-06', '08:30',1,'正常' union all
select '001','2014-05-06', '17:36',2,'正常'

select 工号,日期,上班打卡时间=max(case when 打卡类型=1 then 打卡时间 end),
                下班打卡时间=max(case when 打卡类型=2 then 打卡时间 end)
          
from ta t
group by 工号,日期

001	2014-05-06 00:00:00.000	08:30	17:36

非常感谢，要是我再加个状态呢，加入上班打卡和下班打卡都正常则查询出来的结果状态显示正常，如果上班迟到下班正常则状态显示迟到 这样要怎么写啊

[极品老土豆]

--创建表
if object_id('tempdb..#a','U') is not null
drop table #a ;
go
create table #a 
(
workID char(3),
Date1 DATE,
time1 time(0),
WorkType tinyint,
WorkState char(4)
)
--插入数据
insert into #a (WorkID,Date1,Time1,WorkType,WorkState)
      values ('001','2014-05-06','08:30',1,'正常'),
 ('001','2014-05-06','17:36',2,'正常');
 --语句
 select a.workid as [工号],a.date1 as [日期],a.time1 as [上班打卡时间],b.time1[下班打卡时间],a.workstate as [状态]
  from #a  as a inner join #a as b on a.workID = b.workID and a.date1 = b.date1 and a.Worktype = b.worktype+1 and b.workState ='正常'
 where a.workState = '正常'

 --结果展示
 /*
工号   日期         上班打卡时间   下班打卡时间           状态
---- ---------- ---------------- ----------------         ----
001  2014-05-06 17:36:00         08:30:00                 正常

(1 行受影响)


 */