时间戳

首页 > 解决方案 > SQL Server 计数记录按月和位置按 4 周分组

sql - SQL Server 计数记录按月和位置按 4 周分组

问题描述

我想计算每月 4 周的总行数并按位置分组，结果应如下所示：

location week1 week2 week3 week4
Floor A  10    0     3     4
Floor B  5     2     0     0
...

我已经尝试过通过给定日期获取周数。

CASE WHEN DAY(create_date) >= 1 AND DAY(create_date) < 8 THEN 1 
WHEN DAY(create_date) >= 8 AND DAY(create_date) < 15 THEN 2
WHEN DAY(create_date) >= 15 AND DAY(create_date) < 22 THEN 3
ELSE 4 END AS WeekNumber

它给了我这个结果：

+----+-----------+--------------+------------+
| id | create_by | create_date  | WeekNumber |
+----+-----------+--------------+------------+
|  1 |    555001 | '2018-11-01' |          1 |
|  2 |    555002 | '2018-11-05' |          1 |
|  3 |    555004 | '2018-11-06' |          1 |
|  4 |    555001 | '2018-11-10' |          2 |
|  5 |    555003 | '2018-11-17' |          3 |
|  6 |    555002 | '2018-11-17' |          3 |
|  7 |    555001 | '2018-11-18' |          3 |
|  8 |    555003 | '2018-11-20' |          3 |
|  9 |    555001 | '2018-11-22' |          4 |
| 10 |    555001 | '2018-11-25' |          4 |
+----+-----------+--------------+------------+

我的桌子

tbl_user

+----+----------+----------+
| id | username | location |
+----+----------+----------+
|  1 |   555001 | Floor A  |
|  2 |   555002 | Floor B  |
|  3 |   555003 | Floor C  |
|  4 |   555004 | Floor A  |
|  5 |   555005 | Floor C  |
+----+----------+----------+

tbl_list

+----+-----------+--------------+
| id | create_by | create_date  |
+----+-----------+--------------+
|  1 |    555001 | '2018-11-01' |
|  2 |    555002 | '2018-11-05' |
|  3 |    555004 | '2018-11-06' |
|  4 |    555001 | '2018-11-10' |
|  5 |    555003 | '2018-11-17' |
|  6 |    555002 | '2018-11-17' |
|  7 |    555001 | '2018-11-18' |
|  8 |    555003 | '2018-11-20' |
|  9 |    555001 | '2018-11-22' |
| 10 |    555001 | '2018-11-25' |
+----+-----------+--------------+

标签： sqlsql-server

解决方案

我会简单地使用条件聚合：

select u.location,
       sum(case when day(l.create_date) >= 1 and day(l.create_date) < 8 then 1 else 0
           end) as week1,
       sum(case when day(l.create_date) >= 8 and day(l.create_date) < 15 then 1 else 0
           end) as week2,
       sum(case when day(l.create_date) >= 15 and day(l.create_date) < 22 then 1 else 0
           end) as week3,
       sum(case when day(l.create_date) >= 22 and day(l.create_date) < 29 then 1 else 0
           end) as week4
from tbl_list l join
     tbl_user u
     on l.create_by = u.username
group by u.location;

这似乎比尝试使用简单得多pivot。这个逻辑根本不需要子查询。

推荐阅读