Sql Server交叉选项卡查询
Sql Server交叉选项卡查询
提问于 2015-02-25 08:14:01
我有一个数据，其中包含日期/时间和其他类似的字段
ASSIGN ASSIGN_DATE OFFICER
ASSIGN-1 2013-07-17 19:37:09.000 Admin
ASSIGN-2 2013-07-17 19:37:09.000 Admin
ASSIGN-3 2013-07-17 19:37:09.000 Admin
ASSIGN-4 2013-07-17 10:40:15.000 TESTER
ASSIGN-5 2013-07-17 19:37:09.000 Admin
ASSIGN-5 2013-07-17 19:50:00.000 Admin
ASSIGN-6 2013-07-17 10:40:15.000 TESTER
ASSIGN-7 2013-07-17 10:40:15.000 TESTER我试着用sum对sum -1进行分配-7
SELECT OFFICER,
sum(case when [ASSIGN] = 'ASSIGN-1' then 1 else 0 end) ASS1,
sum(case when [ASSIGN] = 'ASSIGN-2' then 1 else 0 end) ASS2,
sum(case when [ASSIGN] = 'ASSIGN-3' then 1 else 0 end) ASS3,
sum(case when [ASSIGN] = 'ASSIGN-4' then 1 else 0 end) ASS4,
sum(case when [ASSIGN] = 'ASSIGN-5' then 1 else 0 end) ASS5,
sum(case when [ASSIGN] = 'ASSIGN-6' then 1 else 0 end) ASS6,
sum(case when [ASSIGN] = 'ASSIGN-7' then 1 else 0 end) ASS7
FROM [BizView_Dev2].[dbo].[PROBLEM_ASSIGN_LOG]
GROUP BY [ASSIGN_DATE],OFFICER这是一项工作，只是分配的总和
OFFICER ASS1 ASS2 ASS3 ASS4 ASS5 ASS6 ASS7
Admin 1 1 1 0 1 0 0
TESTER 4 0 0 1 0 1 1我希望按日期设置使用officer和Assign_date (仅使用时间00 - 24)的交叉选项卡，并计算它们有多少是这样分配的。
OFFICER ASS1 ASS2 ASS3 ASS4 ASS5 ASS6 ASS7 Total
Admin
00 0 0 0 0 0 0 0 0
01 0 0 0 0 0 0 0 0
02 0 0 0 0 0 0 0 0
03 0 0 0 0 0 0 0 0
04 0 0 0 0 0 0 0 0
05 0 0 0 0 0 0 0 0
06 0 0 0 0 0 0 0 0
07 0 0 0 0 0 0 0 0
08 0 0 0 0 0 0 0 0
09 0 0 0 0 0 0 0 0
10 0 0 0 0 0 0 0 0
11 0 0 0 0 0 0 0 0
12 0 0 0 0 0 0 0 0
13 0 0 0 0 0 0 0 0
14 0 0 0 0 0 0 0 0
15 0 0 0 0 0 0 0 0
16 0 0 0 0 0 0 0 0
17 0 0 0 0 0 0 0 0
18 0 0 0 0 0 0 0 0
19 1 1 1 0 2 0 0 5
20 0 0 0 0 0 0 0 0
21 0 0 0 0 0 0 0 0
22 0 0 0 0 0 0 0 0
23 0 0 0 0 0 0 0 0
24 0 0 0 0 0 0 0 0
TESTER
00 0 0 0 0 0 0 0 0
01 0 0 0 0 0 0 0 0
02 0 0 0 0 0 0 0 0
03 0 0 0 0 0 0 0 0
04 0 0 0 0 0 0 0 0
05 0 0 0 0 0 0 0 0
06 0 0 0 0 0 0 0 0
07 0 0 0 0 0 0 0 0
08 0 0 0 0 0 0 0 0
09 0 0 0 0 0 0 0 0
10 0 0 0 1 0 1 1 3
11 0 0 0 0 0 0 0 0
12 0 0 0 0 0 0 0 0
13 0 0 0 0 0 0 0 0
14 0 0 0 0 0 0 0 0
15 0 0 0 0 0 0 0 0
16 0 0 0 0 0 0 0 0
17 0 0 0 0 0 0 0 0
18 0 0 0 0 0 0 0 0
19 0 0 0 0 0 0 0 0
20 0 0 0 0 0 0 0 0
21 0 0 0 0 0 0 0 0
22 0 0 0 0 0 0 0 0
23 0 0 0 0 0 0 0 0
24 0 0 0 0 0 0 0 0回答 1
Stack Overflow用户
回答已采纳
发布于 2015-02-25 09:23:57
也许这不是最有效的解决方案，但效果很好：
测试数据：
CREATE TABLE #Test
(
ASSIGN VARCHAR(255)
, ASSIGN_DATE DATETIME2
, OFFICER VARCHAR(255)
);
INSERT INTO #Test
(ASSIGN, ASSIGN_DATE, OFFICER)
VALUES
('ASSIGN-1', '2013-07-17 19:37:09.000', 'Admin')
, ('ASSIGN-2', '2013-07-17 19:37:09.000', 'Admin')
, ('ASSIGN-3', '2013-07-17 19:37:09.000', 'Admin')
, ('ASSIGN-4', '2013-07-17 10:40:15.000', 'TESTER')
, ('ASSIGN-5', '2013-07-17 19:37:09.000', 'Admin')
, ('ASSIGN-5', '2013-07-17 19:50:00.000', 'Admin')
, ('ASSIGN-6', '2013-07-17 10:40:15.000', 'TESTER')
, ('ASSIGN-7', '2013-07-17 10:40:15.000', 'TESTER');实际查询：
;WITH TableName
AS (
SELECT OFFICER
, D.[Hour]
, SUM(CASE WHEN T.[ASSIGN] = 'ASSIGN-1' THEN 1 ELSE 0 END) ASS1
, SUM(CASE WHEN T.[ASSIGN] = 'ASSIGN-2' THEN 1 ELSE 0 END) ASS2
, SUM(CASE WHEN T.[ASSIGN] = 'ASSIGN-3' THEN 1 ELSE 0 END) ASS3
, SUM(CASE WHEN T.[ASSIGN] = 'ASSIGN-4' THEN 1 ELSE 0 END) ASS4
, SUM(CASE WHEN T.[ASSIGN] = 'ASSIGN-5' THEN 1 ELSE 0 END) ASS5
, SUM(CASE WHEN T.[ASSIGN] = 'ASSIGN-6' THEN 1 ELSE 0 END) ASS6
, SUM(CASE WHEN T.[ASSIGN] = 'ASSIGN-7' THEN 1 ELSE 0 END) ASS7
FROM #Test AS T
CROSS APPLY (SELECT DATEPART(HOUR, [ASSIGN_DATE])) AS D([Hour])
GROUP BY [Hour], OFFICER
)
SELECT DISTINCT T.[OFFICER]
, D.[Hour]
, COALESCE(ASS1, 0) AS ASS1
, COALESCE(ASS2, 0) AS ASS2
, COALESCE(ASS3, 0) AS ASS3
, COALESCE(ASS4, 0) AS ASS4
, COALESCE(ASS5, 0) AS ASS5
, COALESCE(ASS6, 0) AS ASS6
, COALESCE(ASS7, 0) AS ASS7
, COALESCE(ASS1, 0) + COALESCE(ASS2, 0) + COALESCE(ASS3, 0) + COALESCE(ASS4, 0) + COALESCE(ASS5, 0) + COALESCE(ASS6, 0) + COALESCE(ASS7, 0) AS Total
FROM #Test AS T
CROSS JOIN (VALUES(0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17),(18),(19),(20),(21),(22),(23)) AS D([Hour])
LEFT JOIN TableName AS TN
ON TN.[OFFICER] = T.[OFFICER]
AND TN.[Hour] = D.[Hour]
ORDER BY T.[OFFICER]
, D.[Hour];页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/28714149
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号