3表联合查询，同一个人一天里的多条纪录按日期筛选第一条和最后一条后在一行里显示出来，如下

3表联合查询，日期1和日期2是查询条件

select a.卡号，a.工号，a.打卡日期（带时间）,b.部门，c.打卡地点 from a left join b on a.aid=b.bid left join c on a.aid=c.cid 
where a.aid in (select did from d) and ( a.打卡日期 between '" + 日期1 + "'  and  '" + 日期2 + "') 
order by ...

那这样查询的结果是某个人一天可能有1条，2条或以上打卡纪录，那么数据就显示1行，2行或多行，

部门1  0001  吴高  2012-8-1 8:23:25  1食堂
部门1  0001  吴高  2012-8-1 12:15:10  2食堂
部门1  0001  吴高  2012-8-1 20:09:30  3食堂
部门1  0002  刘备  2012-8-1 9:23:40  办公室
部门1  0003  诸葛亮  2012-8-1 7:25:20  1仓库
部门1  0003  诸葛亮  2012-8-1 16:39:10  2仓库


那我要的结果是某人每天的第一条和最后一条纪录，一天里只有一条的就显示为第一条，而且在一行里显示，

部门1  0001  吴高  2012-8-1 8:23:25  1食堂  2012-8-1 20:09:30  3食堂
部门1  0002  刘备  2012-8-1 9:23:40  办公室
部门1  0003  诸葛亮  2012-8-1 7:25:20  1仓库  2012-8-1 16:39:10 2仓库


如上字段不能少，怎么改SQL语句？

3 个解决方案

#1

with t as 
(
select ROW_NUMBER() over(PARTITION by a.工号 order by a.打卡日期 asc) as R1,
ROW_NUMBER() over(PARTITION by a.工号 order by a.打卡日期 asc) as R2,

--*******
--你的原有SQL语句
a.卡号，a.工号，a.打卡日期（带时间）,b.部门，c.打卡地点 from a left join b on a.aid=b.bid left join c on a.aid=c.cid  
where a.aid in (select did from d) and ( a.打卡日期 between '" + 日期1 + "' and '" + 日期2 + "')  
--*******

)
select  ta.*,tb.打卡日期 as 离开日期,tb.打卡地点 as 离开地点
from t ta left join t tb on ta.工号=tb.工号 and tb.r2=1 and ta.打卡日期 <>tb.打卡日期
where ta.r1=1 

#2

--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([col1] VARCHAR(5),[col2] VARCHAR(4),[col3] VARCHAR(6),[col4] DATETIME,[col5] VARCHAR(6))
INSERT [tb]
SELECT '部门1','0001','吴高','2012-8-1 8:23:25','1食堂' UNION ALL
SELECT '部门1','0001','吴高','2012-8-1 12:15:10','2食堂' UNION ALL
SELECT '部门1','0001','吴高','2012-8-1 20:09:30','3食堂' UNION ALL
SELECT '部门1','0002','刘备','2012-8-1 9:23:40','办公室' UNION ALL
SELECT '部门1','0003','诸葛亮','2012-8-1 7:25:20','1仓库' UNION ALL
SELECT '部门1','0003','诸葛亮','2012-8-1 16:39:10','2仓库'
--------------开始查询--------------------------

SELECT [col1],[col2],[col3],
MAX(CASE WHEN row =1 THEN  [col4] ELSE null END ),
MAX(CASE WHEN row =1 THEN  [col5] ELSE null END ),
MAX(CASE WHEN row =2 THEN  [col4] ELSE null END ),
MAX(CASE WHEN row =2 THEN  [col5] ELSE null END )
FROM
(
SELECT *,row=ROW_NUMBER()OVER(PARTITION BY [col1],[col2],[col3] ORDER BY [col4])
FROM
(
--最小的
SELECT *  FROM [tb] t WHERE NOT EXISTS(
SELECT 1 FROM tb WHERE [col1]=t.[col1] AND [col2]=t.[col2] AND [col3]=t.[col3]
AND [col4]<t.[col4] ) 
UNION 
--最大的
SELECT * FROM [tb] t WHERE NOT EXISTS(
SELECT 1 FROM tb WHERE [col1]=t.[col1] AND [col2]=t.[col2] AND [col3]=t.[col3]
AND [col4]>t.[col4] )
) a 
)b 

GROUP BY [col1],[col2],[col3]
----------------结果----------------------------
/* 
col1  col2 col3                                                          
----- ---- ------ ----------------------- ------ ----------------------- ------
部门1   0001 吴高     2012-08-01 08:23:25.000 1食堂    2012-08-01 20:09:30.000 3食堂
部门1   0002 刘备     2012-08-01 09:23:40.000 办公室    NULL                    NULL
部门1   0003 诸葛亮    2012-08-01 07:25:20.000 1仓库    2012-08-01 16:39:10.000 2仓库

*/

#3

if object_id('test') is not null drop table test
go
create table test(部门 nvarchar(20),工号 varchar(5),姓名 nvarchar(10),打卡时间 datetime,打卡地点 nvarchar(30))
go
insert into test
select N'部门1', '0001', N'吴高', '2012-8-1 8:23:25', N'1食堂' union all
select N'部门1', '0001', N'吴高', '2012-8-1 12:15:10', N'2食堂' union all
select N'部门1', '0001', N'吴高', '2012-8-1 20:09:30', N'3食堂' union all
select N'部门1', '0002', N'刘备', '2012-8-1 9:23:40', N'办公室' union all
select N'部门1', '0003', N'诸葛亮', '2012-8-1 7:25:20', N'1仓库' union all
select N'部门1', '0003', N'诸葛亮', '2012-8-1 16:39:10', N'2仓库'
go
;with cte as
(
	select row_number() over(partition by 工号,convert(varchar(10),打卡时间,120) order by 打卡时间) id,* from test
)
select a.部门,a.工号,a.姓名,a.打卡时间,a.打卡地点,b.打卡时间,b.打卡地点 from 
(
	select 部门,工号,姓名,打卡时间,打卡地点 from cte t 
	where t.id=1
) a
left join
(
	select 部门,工号,姓名,打卡时间,打卡地点 from cte t where t.id=(select count(1) id2 from cte 
	where t.工号=工号 group by 部门,工号,姓名) and t.id<>1
) b
on a.工号=b.工号 

/*

(6 row(s) affected)
部门                   工号    姓名         打卡时间                    打卡地点                           打卡时间                    打卡地点
-------------------- ----- ---------- ----------------------- ------------------------------ ----------------------- ------------------------------
部门1                  0001  吴高         2012-08-01 08:23:25.000 1食堂                            2012-08-01 20:09:30.000 3食堂
部门1                  0002  刘备         2012-08-01 09:23:40.000 办公室                            NULL                    NULL
部门1                  0003  诸葛亮        2012-08-01 07:25:20.000 1仓库                            2012-08-01 16:39:10.000 2仓库

(3 row(s) affected)
*/