34,575
社区成员
发帖
与我相关
我的任务
分享
select a.Platform as 城市,COUNT(DISTINCT(a.Invoice))as 票数,
(case when datediff(HH,a.Fahuoqrdate,a.RkArrivedRDCTime)<=b.shixiao then '达标' else '不达标' end) as 结果 from
Orders a join Platform_Ganxianshixiao b
on a.Platform=b.Platform
where RkArrivedRDCTime is not null and convert(char(10),Fahuoqrdate,20) BETWEEN '2011-12-01' and '2011-12-17'
group by a.Platform,a.Fahuoqrdate,a.RkArrivedRDCTime,b.shixiao
得了的结果
/*
城市 票数 结果
北京 9 达标
北京 24 达标
北京 8 达标
北京 5 达标
惠阳 5 达标
惠阳 22 达标
惠阳 4 达标
惠阳 1 不达标
惠阳 20 达标
惠阳 1 不达标
上海 11 不达标
上海 1 不达标
上海 1 不达标
上海 14 达标
上海 8 达标
上海 10 达标
深圳 2 达标
深圳 3 达标
深圳 3 达标
深圳 18 达标
深圳 1 不达标
深圳 5 达标 */
想修改上面的SQL语句成下面的结果,
/*
城市 不达标 达标 总计 达标率
北京 0 46 46 100%
惠阳 2 51 53 96%
上海 13 32 45 71%
深圳 1 31 32 97% */
with cte as
(
select a.Platform as 城市,COUNT(DISTINCT(a.Invoice))as 票数,
(case when datediff(HH,a.Fahuoqrdate,a.RkArrivedRDCTime)<=b.shixiao then '达标' else '不达标' end) as 结果 from
Orders a join Platform_Ganxianshixiao b
on a.Platform=b.Platform
where RkArrivedRDCTime is not null and convert(char(10),Fahuoqrdate,20) BETWEEN '2011-12-01' and '2011-12-17'
group by a.Platform,a.Fahuoqrdate,a.RkArrivedRDCTime,b.shixiao
)
select 城市,
达标=sum(case when 结果='达标' then 票数 else 0 end),
不达标=sum(case when 结果='不达标' then 票数 else 0 end),
总计=sum(标数),
达标率=ltrim(cast(sum(case when 结果='达标' then 票数 else 0 end)*1.0/sum(票数)*100 as numeric(18,2)))+'%'
from cte group by 城市
达标率=ltrim(cast(sum(case when 结果='达标' then 票数 else 0 end)*1.0/sum(case when 结果='不达标' then 票数 else 0 end)*100 as numeric(18,2)))+'%'
from cte group by 城市
/*消息 156,级别 15,状态 1,第 5 行
关键字 'from' 附近有语法错误。
消息 156,级别 15,状态 1,第 12 行
关键字 'as' 附近有语法错误。
*/
[select 城市
,不达标 = sum(case 结果 when 不达标 then 票数 else 0 end)
,达标 = sum(case 结果 when 达标 then 票数 else 0 end)
,总计 = sum(票数)
,达标率 = case when sum(票数) = 0 then '0%' else cast(100 * sum(case 结果 when 达标 then 票数 else 0 end) / sum(票数) as varchar(10)) + '%'
from (
select a.Platform as 城市,COUNT(DISTINCT(a.Invoice))as 票数,
(case when datediff(HH,a.Fahuoqrdate,a.RkArrivedRDCTime)<=b.shixiao then '达标' else '不达标' end) as 结果 from
Orders a join Platform_Ganxianshixiao b
on a.Platform=b.Platform
where RkArrivedRDCTime is not null and convert(char(10),Fahuoqrdate,20) BETWEEN '2011-12-01' and '2011-12-17'
group by a.Platform,a.Fahuoqrdate,a.RkArrivedRDCTime,b.shixiao
) as t
group by 城市
with cte as
(
select a.Platform as 城市,COUNT(DISTINCT(a.Invoice))as 票数,
(case when datediff(HH,a.Fahuoqrdate,a.RkArrivedRDCTime)<=b.shixiao then '达标' else '不达标' end) as 结果 from
Orders a join Platform_Ganxianshixiao b
on a.Platform=b.Platform
where RkArrivedRDCTime is not null and convert(char(10),Fahuoqrdate,20) BETWEEN '2011-12-01' and '2011-12-17'
group by a.Platform,a.Fahuoqrdate,a.RkArrivedRDCTime,b.shixiao
)
select 城市,
达标=sum(case when 结果='达标' then 票数 else 0 end),
不达标=sum(case when 结果='不达标' then 票数 else 0 end),
总计=sum(标数),
达标率=ltrim(cast(sum(case when 结果='达标' then 票数 else 0 end)*1.0/sum(case when 结果='不达标' then 票数 else 0 end)*100 as numeric(18,2)))+'%'
from cte group by 城市