1. 我有一张表如下：
AttTime(datetime) Emp(nvarchar) Dept(nvarchar)
2008-08-02 6:45 zhangsan A
2008-08-02 18:20 zhangsan A
2008-08-02 11:22 zhangsan A
2008-08-02 18:20 zhangsan A
2008-08-08 7:15 zhangsan A
2008-08-08 19:12 zhangsan A
2008-08-01 6:41 ww A
2008-08-01 8:41 ww A
2008-08-01 17:20 ww A
2008-08-06 7:11 ww A
2008-08-06 18:12 ww A
2008-08-04 8:13 lisi B
2.想要得到数据如下：
即得到指定Dept(A)下的所有Emp(zhangsan,ww)的AttTime记录，并按每个人分组显示，求出每个人每天的最大时间和最小时间，
没有记录的日期用空的补全。
AttDate minAttTime maxAttTime Emp Dept
2008-08-02 6:45 18:20 zhangsan A
2008-08-03 null null zhangsan A
2008-08-04 null null zhangsan A
2008-08-05 null null zhangsan A
2008-08-06 null null zhangsan A
2008-08-07 null null zhangsan A
2008-08-08 7:15 19:12 zhangsan A
2008-08-01 6:41 17:20 ww A
2008-08-02 null null ww A
2008-08-03 null null ww A
2008-08-04 null null ww A
2008-08-05 null null ww A
2008-08-06 7:11 18:12 ww A
3.答案如下：
--> 生成测试数据: @AttRecords
CREATE TABLE AttRecords(AttTime DATETIME,Emp VARCHAR(8),Dept VARCHAR(1))
INSERT INTO AttRecords
SELECT '2008-08-02 6:45','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-02 11:22','zhangsan','A' UNION ALL
SELECT '2008-08-02 18:20','zhangsan','A' UNION ALL
SELECT '2008-08-08 7:15','zhangsan','A' UNION ALL
SELECT '2008-08-08 19:12','zhangsan','A' UNION ALL
SELECT '2008-08-01 6:41','ww','A' UNION ALL
SELECT '2008-08-01 8:41','ww','A' UNION ALL
SELECT '2008-08-01 17:20','ww','A' UNION ALL
SELECT '2008-08-06 7:11','ww','A' UNION ALL
SELECT '2008-08-06 18:12','ww','A' UNION ALL
SELECT '2008-08-04 8:13','lisi','B'
--SQL查询如下:
--在实际中.应该在数据库中建立一个序号表来代替master.dbo.spt_values
GO
CREATE VIEW dbo.v_Test
AS
SELECT
A.Emp,A.Dept,
CONVERT(VARCHAR(10),A.AttTime,120) AS AttTime,
CONVERT(VARCHAR(10),MIN(B.AttTime),108) AS MinAttTime,
CONVERT(VARCHAR(10),MAX(B.AttTime),108) AS MaxAttTime
FROM (SELECT A.ID,B.Dept,B.Emp,DATEADD(day,A.ID,B.MinAttTime) AS AttTime
FROM (SELECT number AS ID FROM master.dbo.spt_values WHERE type = 'p') AS A
CROSS JOIN (SELECT Dept,Emp,MIN(AttTime) AS MinAttTime,MAX(AttTime) AS MaxATtTime
FROM AttRecords WHERE Dept = 'A' GROUP BY Dept,Emp ) AS B
WHERE DATEADD(day,A.ID,B.MinAttTime) <= B.MaxAttTime) AS A
LEFT JOIN AttRecords AS B
ON DATEDIFF(day,B.AttTime,A.AttTime) = 0 AND A.Emp = B.Emp AND A.Dept = B.Dept
GROUP BY A.Dept,A.Emp,CONVERT(VARCHAR(10),A.AttTime,120);
GO
SELECT * FROM dbo.v_Test ORDER BY Dept,Emp,AttTime;
GO
DROP VIEW dbo.v_Test;
DROP TABLE AttRecords;
问下这个查询如果用linqtosql直接对AttRecords查询如何才能得到我想要的数据呢？
本文转自生鱼片博客园博客，原文链接：http://www.cnblogs.com/carysun/archive/2009/08/25/sql-group.html，如需转载请自行联系原作者