一个关于时间的SQL分组排序问题

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

<chsdate w:st="on" year="2008" month="8" day="2" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-02</span></chsdate>    6:45        18:20      zhangsan  A

<chsdate w:st="on" year="2008" month="8" day="3" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-03</span></chsdate>    null          null      zhangsan  A

<chsdate w:st="on" year="2008" month="8" day="4" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-04</span></chsdate>    null          null      zhangsan  A

<chsdate w:st="on" year="2008" month="8" day="5" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-05</span></chsdate>    null          null      zhangsan  A

<chsdate w:st="on" year="2008" month="8" day="6" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-06</span></chsdate>    null          null      zhangsan  A

<chsdate w:st="on" year="2008" month="8" day="7" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-07</span></chsdate>    null          null      zhangsan  A

<chsdate w:st="on" year="2008" month="8" day="8" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-08</span></chsdate>    7:15          19:12    zhangsan  A

 

<chsdate w:st="on" year="2008" month="8" day="1" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-01</span></chsdate>    6:41          17:20      ww      A

<chsdate w:st="on" year="2008" month="8" day="2" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-02</span></chsdate>    null          null      ww      A

<chsdate w:st="on" year="2008" month="8" day="3" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-03</span></chsdate>    null          null      ww      A

<chsdate w:st="on" year="2008" month="8" day="4" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-04</span></chsdate>    null          null      ww      A

<chsdate w:st="on" year="2008" month="8" day="5" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-05</span></chsdate>    null          null        ww      A

<chsdate w:st="on" year="2008" month="8" day="6" islunardate="False" isrocdate="False"><span lang="EN-US">2008-08-06</span></chsdate>    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 <place w:st="on"><city w:st="on">AttRecords</city><state w:st="on"><span style="color: blue">AS</span></state></place> 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查询如何才能得到我想要的数据呢？