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

最新推荐文章于 2024-04-24 13:27:31 发布

最新推荐文章于 2024-04-24 13:27:31 发布 · 239 阅读

文章标签：

#SQL #Go

[网站分类]首页候选区专栏收录该内容

1 篇文章

订阅专栏

本文介绍了一种使用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">2008-08-02</chsdate> 6:45 18:20 zhangsan A

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

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

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

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

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

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

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

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

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

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

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

<chsdate w:st="on" year="2008" month="8" day="6" islunardate="False" isrocdate="False">2008-08-06</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

CREATE VIEW dbo.v_Test

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">AS</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);

SELECT * FROM dbo.v_Test ORDER BY Dept,Emp,AttTime;

DROP VIEW dbo.v_Test;

DROP TABLE AttRecords;

问下这个查询如果用linqtosql直接对AttRecords查询如何才能得到我想要的数据呢？