比如这样一张 考勤表 TCId TCName TCDate TCTime
1 aa 2008-01-02 08:03
2 aa 2008-01-02 18:10
3 aa 2008-01-02 18:30
4 bb 2009-10-01 08:00
5 cc 2009-05-04 18:00
6 dd 2008-08-08 12:00
7 dd 2008-08-08 18:30把他们合成 这样的 TCId TCName TCDate 签到时间 签退时间
1 aa 2008-01-02 08:03 18:30
2 bb 2008-01-02 08:00 3 cc 2009-05-04 18:00 4 dd 2008-08-08 12:00 18:30这样的SQL 语句如何写
1 aa 2008-01-02 08:03
2 aa 2008-01-02 18:10
3 aa 2008-01-02 18:30
4 bb 2009-10-01 08:00
5 cc 2009-05-04 18:00
6 dd 2008-08-08 12:00
7 dd 2008-08-08 18:30把他们合成 这样的 TCId TCName TCDate 签到时间 签退时间
1 aa 2008-01-02 08:03 18:30
2 bb 2008-01-02 08:00 3 cc 2009-05-04 18:00 4 dd 2008-08-08 12:00 18:30这样的SQL 语句如何写
tcname,tcdate,
case when min(tctime)>'12:00' then null else min(tctime) end as mintctime,
case when max(tctime)<'12:00' then null else max(tctime) end as maxtctime
FROM
TA
group by tcname,tcdate
order by 1,2
-- Author : HappyFlyStone
-- Date : 2009-10-02
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([TCId] INT,[TCName] NVARCHAR(2),[TCDate] varchar(10),[TCTime] varchar(6))
Go
INSERT INTO ta
SELECT 1,'aa','2008-01-02','08:03' UNION ALL
SELECT 2,'aa','2008-01-02','18:10' UNION ALL
SELECT 3,'aa','2008-01-02','18:30' UNION ALL
SELECT 4,'bb','2009-10-01','08:00' UNION ALL
SELECT 5,'cc','2009-05-04','18:00' UNION ALL
SELECT 6,'dd','2008-08-08','12:00' UNION ALL
SELECT 7,'dd','2008-08-08','18:30'
GO
--Start
SELECT
tcid = row_number() over (order by getdate()),
tcname,tcdate,
case when min(tctime)>'12:00' then null else min(tctime) end as mintctime,
case when max(tctime)<'12:00' then null else max(tctime) end as maxtctime
FROM
TA
group by tcname,tcdate
order by 1,2
--Result:
/*
tcid tcname tcdate mintctime maxtctime
-------------------- ------ ---------- --------- ---------
1 aa 2008-01-02 08:03 18:30
2 dd 2008-08-08 12:00 18:30
3 cc 2009-05-04 NULL 18:00
4 bb 2009-10-01 08:00 NULL(4 行受影响)
*/
--End