精妙SQL语句收集

来源:互联网 发布:矩阵论教材 知乎 编辑:程序博客网 时间:2024/05/17 06:21

select
LogTime = Convert(char(10),LogTime,111),
UPPER(LTRIM(ClientUserName)),
COMPANY,
DEPT_ID,
[NAME],
ProcessingTime =sum( ProcessingTime),
H_00 = sum(Case  datepart(Hour,LogTime)
when 0 then  ProcessingTime else 0 end),
H_01 = sum(Case  datepart(Hour,LogTime)
when 1 then  ProcessingTime else 0 end),
H_02 = sum(Case  datepart(Hour,LogTime)
when 2 then  ProcessingTime else 0 end),
H_03 = sum(Case  datepart(Hour,LogTime)
when 3 then  ProcessingTime else 0 end),
H_04 = sum(Case  datepart(Hour,LogTime)
when 4 then  ProcessingTime else 0 end),
H_05 = sum(Case  datepart(Hour,LogTime)
when 5 then  ProcessingTime else 0 end),
H_06 = sum(Case  datepart(Hour,LogTime)
when 6 then  ProcessingTime else 0 end),
H_07 = sum(Case  datepart(Hour,LogTime)
when 7 then  ProcessingTime else 0 end),
H_08 = sum(Case  datepart(Hour,LogTime)
when 8 then  ProcessingTime else 0 end),
H_09 = sum(Case  datepart(Hour,LogTime)
when 9 then  ProcessingTime else 0 end),
H_10 = sum(Case  datepart(Hour,LogTime)
when 10 then  ProcessingTime else 0 end),
H_11 = sum(Case  datepart(Hour,LogTime)
when 11 then  ProcessingTime else 0 end),
H_12 = sum(Case  datepart(Hour,LogTime)
when 12 then  ProcessingTime else 0 end),
H_13 = sum(Case  datepart(Hour,LogTime)
when 13 then  ProcessingTime else 0 end),
H_14 = sum(Case  datepart(Hour,LogTime)
when 14 then  ProcessingTime else 0 end),
H_15 = sum(Case  datepart(Hour,LogTime)
when 15 then  ProcessingTime else 0 end),
H_16 = sum(Case  datepart(Hour,LogTime)
when 16 then  ProcessingTime else 0 end),
H_17 = sum(Case  datepart(Hour,LogTime)
when 17 then  ProcessingTime else 0 end),
H_18 = sum(Case  datepart(Hour,LogTime)
when 18 then  ProcessingTime else 0 end),
H_19 = sum(Case  datepart(Hour,LogTime)
when 19 then  ProcessingTime else 0 end),
H_20 = sum(Case  datepart(Hour,LogTime)
when 20 then  ProcessingTime else 0 end),
H_21 = sum(Case  datepart(Hour,LogTime)
when 21 then  ProcessingTime else 0 end),
H_22 = sum(Case  datepart(Hour,LogTime)
when 22 then  ProcessingTime else 0 end),
H_23 = sum(Case  datepart(Hour,LogTime)
when 23 then  ProcessingTime else 0 end)
from webproxylog A,MSP_USER B
where  --Convert(char(10),LogTime,111) > @VAR_DATE  ---AND    (ClientUserName LIKE 'UNIMICRON%')
--and
 ClientUserName <> 'anonymous' and a.ClientUserName=b.NETWORK_ACC
group by Convert(char(10),LogTime,111),UPPER(LTRIM(ClientUserName)),COMPANY,DEPT_ID,NAME;

原创粉丝点击