SQL多次计数与动态列在同一行上 [英] SQL Multiple count on same row with dynamic column
问题描述
我需要更改按周期在同一行上显示用户计数(ScheduleID)的视图.现在，周期表"的内容可以增加并包含3个以上的周期.
I need to alter view that show user count(ScheduleID) by period on same row. Now the Period table content can grow and contain more than 3 periods.
实际的SQL是:
SELECT r.Code,
SUM(CASE WHEN s.PeriodID=1 THEN 1 ELSE 0 END) AS PeriodID1,
SUM(CASE WHEN s.PeriodID=2 THEN 1 ELSE 0 END) AS PeriodID2,
SUM(CASE WHEN s.PeriodID=3 THEN 1 ELSE 0 END) AS PeriodID3,
SUM(CASE WHEN s.PeriodID IN (1,2,3) THEN 1 ELSE 0 END) AS Total
FROM Schedules s
JOIN Periods p ON p.PeriodID = s.PeriodID
JOIN Resources r ON r.ResourceID = s.ResourceID
GROUP BY r.Code;
示例数据: 餐桌时间表
Example data: Table Schedules
ScheduleID(int) ResourceID(int) ResourceCode(varchar 4) PeriodID(int)
1 1 AA 1
2 1 AA 3
3 1 AA 3
4 2 BB 1
5 3 CC 1
6 1 AA 1
7 3 CC 2
8 3 CC 3
9 2 BB 1
10 2 BB 2
11 2 BB 3
12 1 AA 3
餐桌期间
PeriodID(int) Code (varchar 4)
1 P1
2 P2
3 P3
4 P4
5 P5
6 P6
7 P7
8 P8
我需要的结果是:
ResourceCode PeriodID1 PeriodID2 PeriodID3 ... PeriodID8 TOTAL
AA 2 0 3 0 5
BB 2 1 1 0 4
CC 1 1 1 0 3
期间"表的内容现在是动态的.
The Periods table content is now dynamic.
数据库版本为Microsoft SQL 2008
The database version is an Microsoft SQL 2008
我想知道是否可以在不创建存储过程的情况下执行此操作……并在这样的一个查询中执行此操作:
I like to know if is possible to do that without create stored procedure...and doing this in one query like this:
SELECT *
FROM (
SELECT R.Code, P.PeriodID, COUNT(S.ScheduleID) AS RPCount
FROM Schedules S INNER JOIN Periods P ON S.PeriodID = P.PeriodID
JOIN Resources R ON S.ResourceID = R.ResourceID
WHERE S.ResourceID is not null
GROUP BY R.Code, P.PeriodID
) as data
PIVOT
(
SUM(RPCount)
--FOR PeriodID IN ([1],[2],[3])
FOR PeriodID IN (SELECT PeriodID From Periods)
)AS pvt
ORDER BY Code
推荐答案
由于您使用的是SQL Server，因此可以实现PIVOT函数，并且如果周期数未知，则需要使用动态SQL:
Since you are using SQL Server then you can implement the PIVOT function and if you have an unknown number of period values, then you will need to use dynamic SQL:
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',' + QUOTENAME('PeriodId'+cast(periodid as varchar(10)))
from Periods
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT resourcecode, ' + @cols + ' , Total
from
(
select s.resourcecode,
''PeriodId''+cast(p.periodid as varchar(10)) period,
count(*) over(partition by s.resourcecode) Total
from periods p
left join schedules s
on p.periodid = s.periodid
) x
pivot
(
count(period)
for period in (' + @cols + ')
) p
where resourcecode is not null
order by resourcecode'
execute(@query)
请参见带演示的SQL提琴.得到的结果是:
See SQL Fiddle with Demo. This gives a result:
| RESOURCECODE | PERIODID1 | PERIODID2 | PERIODID3 | PERIODID4 | PERIODID5 | PERIODID6 | PERIODID7 | PERIODID8 | TOTAL |
------------------------------------------------------------------------------------------------------------------------
| AA | 2 | 0 | 3 | 0 | 0 | 0 | 0 | 0 | 5 |
| BB | 2 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 4 |
| CC | 1 | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 3 |
基于您先前用MySQL标记的问题，我假设您正在使用MySQL作为数据库.如果是这样，则您将没有PIVOT函数，因此必须使用带有CASE表达式的聚合函数将数据行转换为列.
Based on your previous question that was tagged with MySQL, I am assuming you are using MySQL as the database. If so, then you do not have a PIVOT function so you will have to use an aggregate function with a CASE expression to transform the rows of data into columns.
如果您知道列值，则可以对查询进行硬编码:
If your column values are known, then you can hard-code the query:
select resourcecode,
sum(case when period = 'PeriodId1' then 1 else 0 end) PeriodId1,
sum(case when period = 'PeriodId2' then 1 else 0 end) PeriodId2,
sum(case when period = 'PeriodId3' then 1 else 0 end) PeriodId3,
sum(case when period = 'PeriodId4' then 1 else 0 end) PeriodId4,
sum(case when period = 'PeriodId5' then 1 else 0 end) PeriodId5,
sum(case when period = 'PeriodId6' then 1 else 0 end) PeriodId6,
sum(case when period = 'PeriodId7' then 1 else 0 end) PeriodId7,
sum(case when period = 'PeriodId8' then 1 else 0 end) PeriodId8,
count(*) Total
from
(
select concat('PeriodId', p.periodid) Period,
s.resourcecode
from periods p
left join schedules s
on p.periodid = s.periodid
) d
where resourcecode is not null
group by resourcecode;
请参见带演示的SQL提琴.但是，如果这些值是未知的或动态的，那么您将需要使用准备好的语句来生成要执行的sql字符串:
See SQL Fiddle with Demo. But if the values will be unknown or dynamic then you will need to use a prepared statement to generate a sql string to execute:
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'sum(CASE WHEN period = ''',
concat('PeriodId', periodid),
''' THEN 1 else 0 END) AS `',
concat('PeriodId', periodid), '`'
)
) INTO @sql
FROM periods;
SET @sql
= CONCAT('SELECT resourcecode, ', @sql, ' , count(*) Total
from
(
select concat(''PeriodId'', p.periodid) Period,
s.resourcecode
from periods p
left join schedules s
on p.periodid = s.periodid
) d
where resourcecode is not null
group by resourcecode');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
请参见 SQL小提琴与演示.
See SQL Fiddle with Demo.
这篇关于SQL多次计数与动态列在同一行上的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
