本文提供了一个SQL查询案例，展示了如何从订单表中按十分钟为单位的时间段分组统计数据，包括下单数、票张数、支付订单数等指标，并通过CASE WHEN语句实现总计行的计算。
select
--Sdate '时间/十分钟',
case grouping(Sdate)
when 0 then Sdate
when 1 then '总计'
end as '时间/十分钟',
count (distinct orderDeputyID) as '下单数',
sum (ticketCount) as '票张数',
count ( distinct payorderDeputyID ) '支付订单数',
sum (payticketCount) '支付票单数',
count (distinct orderDeputyID) -(count ( distinct payorderDeputyID )) '未支付订单数',
sum (ticketCount) - sum (payticketCount) '未支付票单数'
from (
select substring(CONVERT(varchar(100), dateTime, 120),1,15) as Sdate
,orderDeputyID
,ticketCount
,case when orderPayment=1 then orderDeputyID
else null
end as payorderDeputyID,
case when orderPayment=1 then ticketCount
else 0
end as payticketCount
from [order] where sessionId =XXXX and orderType=9
)T group by Sdate with Rollup
扫一扫
1211
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
