sql - 如何获取具有位置的员工每天的最小和最大日期?
问题描述
我的问题与此处发布的问题类似:如何获取员工的每个日期的最小和最大日期?,但有一个额外的要求。如果表有一个额外的位置列,例如:
员工编号 | 约会时间 | 地点 |
---|---|---|
00000001 | 2014/01/14 09:20 | 温迪斯 |
00000001 | 2014/01/14 12:35 | 麦当劳 |
00000001 | 2014/01/14 13:35 | 沃尔玛 |
00000002 | 2014/01/14 09:20 | 阿比斯 |
00000001 | 2014/01/14 22:49 | 汉堡王 |
00000001 | 2014/01/15 09:35 | 索尼克 |
00000001 | 2014/01/15 10:35 | 冰雪皇后 |
00000001 | 2014/01/15 17:35 | 阿比斯 |
00000002 | 2014/01/14 12:34 | 麦当劳 |
00000002 | 2014/01/14 17:34 | 地铁 |
并有一个 SELECT 语句输出:
员工编号 | 约会时间 | 最小 | 最大限度 | 最小位置 | 最大位置 |
---|---|---|---|---|---|
00000001 | 2014/01/14 | 2014/01/14 09:20 | 2014/01/14 22:49 | 温迪斯 | 汉堡王 |
00000001 | 2014/01/15 | 2014/01/15 09:35 | 2014/01/15 17:35 | 索尼克 | 阿比斯 |
00000002 | 2014/01/14 | 2014/01/14 09:20 | 2014/01/14 17:34 | 阿比斯 | 地铁 |
谢谢你的帮助!
解决方案
如果这是你的桌子:
create table MyTable (
Employee_Number int,
DateTime datetime,
Location varchar(20)
)
insert into MyTable values
(00000001, '2014/01/14 09:20:00', 'Wendys'),
(00000001, '2014/01/14 12:35:00', 'McDonalds'),
(00000001, '2014/01/14 13:35:00', 'Walmart'),
(00000002, '2014/01/14 09:20:00', 'Arbys'),
(00000001, '2014/01/14 22:49:00', 'Burger King'),
(00000001, '2014/01/15 09:35:00', 'Sonic'),
(00000001, '2014/01/15 10:35:00', 'Dairy Queen'),
(00000001, '2014/01/15 17:35:00', 'Arbys'),
(00000002, '2014/01/14 12:34:00', 'McDonalds'),
(00000002, '2014/01/14 17:34:00', 'Subway')
这将是您的查询:
with MyResult (Row_num_min, Row_num_max, Employee_Number, DateTime, Location)
as
(
select
ROW_NUMBER() OVER (PARTITION BY Employee_Number, DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime)) order by DateTime) AS ROW_NUM_MIN,
ROW_NUMBER() OVER (PARTITION BY Employee_Number, DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime)) order by DateTime DESC) AS ROW_NUM_MAX,
Employee_Number,
DateTime,
Location
from MyTable
)
select
Employee_Number,
DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime))as DateTime,
MAX(CASE WHEN Row_num_min = 1 THEN DateTime END) AS MIN,
MAX(CASE WHEN Row_num_max = 1 THEN DateTime END) AS MAX,
MAX(CASE WHEN Row_num_min = 1 THEN Location END) AS MIN_Location,
MAX(CASE WHEN Row_num_max = 1 THEN Location END) AS MAX_Location
from MyResult
where row_num_min = 1 or row_num_max = 1
group by
Employee_Number,
DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime))
order by
Employee_Number,
DATEADD(dd, 0, DATEDIFF(dd, 0, DateTime))
这就是结果(与您预期的相同):
| Employee_Number | DateTime | MIN | MAX | MIN_Location | MAX_Location |
|-----------------|----------------------|----------------------|----------------------|--------------|--------------|
| 1 | 2014-01-14T00:00:00Z | 2014-01-14T09:20:00Z | 2014-01-14T22:49:00Z | Wendys | Burger King |
| 1 | 2014-01-15T00:00:00Z | 2014-01-15T09:35:00Z | 2014-01-15T17:35:00Z | Sonic | Arbys |
| 2 | 2014-01-14T00:00:00Z | 2014-01-14T09:20:00Z | 2014-01-14T17:34:00Z | Arbys | Subway |