常用统计SQL
行列转换
select Student as '姓名',
max(case Subject when '语文' then Score else 0 end) as '语文' ,--如果这个行是“语文”，就选此行作为列
max(case Subject when '英语' then Score else 0 end ) as '英语'
from Scores
group by Student
order by Student
按月统计
在行列转换的基础上，生成1~12月的顺序月份。
WITH A AS(
SELECT Month(CreateAt) [Month],ProcessName,State,DATEDIFF(mi,CreateAt,FinishAt) Minutes FROM BPMInstTasks WHERE ExtYear=@Year AND (@ProcessName is NULL OR ProcessName=@ProcessName) AND CreateAt>=@Date1 AND CreateAt<@Date2
),
AA AS(
SELECT State,[Month],Count(*) Counts,avg(Minutes) AvgMinutes FROM A GROUP BY State,[Month]
),
B AS(
SELECT [Month],
max(case when State='Approved' then Counts else 0 end) Approved,
max(case when State='Rejected' then Counts else 0 end) Rejected,
max(case when State='Running' then Counts else 0 end) Running,
max(case when State='Aborted' then Counts else 0 end) Aborted,
max(case when State='Deleted' then Counts else 0 end) Deleted,
ISNULL(sum(Counts),0) Total,
max(case when State='Approved' then AvgMinutes else 0 end) AvgMinutes
FROM AA GROUP BY [Month]
),
C AS(
SELECT number [Month] FROM MASTER..spt_values WHERE type='P' and number between 1 and 12
),
D AS(
SELECT C.[Month],
ISNULL(B.Approved,0) Approved,
ISNULL(B.Rejected,0) Rejected,
ISNULL(B.Running,0) Running,
ISNULL(B.Aborted,0) Aborted,
ISNULL(B.Deleted,0) Deleted,
ISNULL(B.Total,0) Total,
ISNULL(B.AvgMinutes,0) AvgMinutes
FROM C LEFT JOIN B ON C.[Month]=B.[Month]
)
SELECT * FROM D ORDER BY [Month]
Created with the Personal Edition of HelpNDoc: Create cross-platform Qt Help files
在线技术支持