SQL数据聚合与转置技巧-优快云博客

本文介绍了一种使用SQL进行复杂数据聚合的方法，通过左连接及CASE WHEN语句实现不同条件下的计数，并进一步将计数结果进行转置处理，以便于数据展示和分析。此外还展示了如何按客户等级汇总特定原因的数量。

原始数据：

select t1.CarModelPKID,t2.DefeatReason, count(*) as count from RT_CustAllocResult t
left join vm_carinfo t1 on t1.FrameNo = t.FrameNo
left join RT_CustFollowUp t2 on t2.RegNo = t.RegNo
where t1.CarModelPKID is not null and t2.DefeatReason is not null group by t2.DefeatReason,CarModelPKID

转置后的数据：

select t.CarModelPKID,
case DefeatReason when '00' then count end as colReason1,
case DefeatReason when '01' then count  end as colReason2,
case DefeatReason when '02' then count  end as colReason3,
case DefeatReason when '03' then count  end as colReason4,
case DefeatReason when '04' then count  end as colReason5,
case DefeatReason when '05' then count  end as colReason6,
case DefeatReason when '06' then count  end as colReason7,
case DefeatReason when '07' then count  end as colReason8,
case DefeatReason when '08' then count  end as colReason9

from(select t1.CarModelPKID,t2.DefeatReason, count(*) as count from RT_CustAllocResult t
left join vm_carinfo t1 on t1.FrameNo = t.FrameNo
left join RT_CustFollowUp t2 on t2.RegNo = t.RegNo
where t1.CarModelPKID is not null and t2.DefeatReason is not null group by t2.DefeatReason,CarModelPKID) t

数据统计：

select t.CustomerLevel as Item,
sum(case DefeatReason when '00' then count end) as colReason1,
sum(case DefeatReason when '01' then count end) as colReason2,
sum(case DefeatReason when '02' then count end) as colReason3,
sum(case DefeatReason when '03' then count end) as colReason4,
sum(case DefeatReason when '04' then count end) as colReason5,
sum(case DefeatReason when '05' then count end) as colReason6,
sum(case DefeatReason when '06' then count end) as colReason7,
sum(case DefeatReason when '07' then count end) as colReason8,
sum(case DefeatReason when '08' then count end) as colReason9

from(select t.CustomerLevel,t2.DefeatReason, count(*) as count from RT_CustAllocResult t
left join RT_CustFollowUp t2 on t2.RegNo = t.RegNo
where t.CustomerLevel is not null and t2.DefeatReason is not null 
group by t2.DefeatReason,t.CustomerLevel) t  group by t.CustomerLevel

表的行列转置