27,579
社区成员
发帖
与我相关
我的任务
分享
create table tab0202
(HIC订单号 varchar(30),订单创建时间 datetime,
POD是否上传 varchar(5),准时发车 varchar(5),准时操作到达 varchar(5),准时操作签收 varchar(5),准时配送 varchar(5))
insert into tab0202
select '15811901250004','2019-01-25 08:40:40','否','是','否','是','否' union all
select '15811901250529','2019-01-25 16:39:46','否','否','否','是','否' union all
select '15811901250544','2019-01-25 16:40:08','否','否','是','否','否' union all
select '15811901280011','2019-01-28 08:47:16','否','否','是','是','否' union all
select '15811901280583','2019-01-28 17:20:09','否','是','是','否','否' union all
select '15811901280594','2019-01-28 17:20:32','否','是','是','否','否' union all
select '15811901290267','2019-01-29 16:19:54','否','否','否','否','否' union all
select '15811901290270','2019-01-29 16:19:52','否','否','是','否','否' union all
select '15811901300042','2019-01-30 09:32:43','否','是','是','是','否' union all
select '15211901300050','2019-01-30 09:33:02','否','是','是','是','否'
with t as
(select * from tab0202)
select 类型=FType+'率',
总订单=(select count(1) from t),
数量=sum(case when val='是' then 1 else 0 end),
占比=rtrim(cast(sum(case when val='是' then 1 else 0 end)/(select count(1)*1.0 from t)*100 as int))+'%'
from (select FType=b.col,b.val
from t a
unpivot(val for col in([POD是否上传],[准时发车],[准时操作到达],[准时操作签收],[准时配送])) b) c
group by FType
order by (select column_id from sys.columns d where d.object_id=object_id('tab0202') and d.name=c.FType)
/*
类型 总订单 数量 占比
------------------------- ----------- ----------- -------------
POD是否上传率 10 0 0%
准时发车率 10 5 50%
准时操作到达率 10 7 70%
准时操作签收率 10 5 50%
准时配送率 10 0 0%
(5 row(s) affected)
*/
WITH CTE
AS
(SELECT COUNT(*) AS 订单总数,
SUM(CASE WHEN POD是否上传='是' THEN 1 ELSE 0 END) AS POD是否上传,
SUM(CASE WHEN 准时配送='是' THEN 1 ELSE 0 END) AS 准时配送,
SUM(CASE WHEN 准时发车='是' THEN 1 ELSE 0 END) AS 准时发车,
SUM(CASE WHEN 准时操作到达='是' THEN 1 ELSE 0 END) AS 准时操作到达次数,
SUM(CASE WHEN 准时操作签收='是' THEN 1 ELSE 0 END) AS 准时操作签收次数
FROM TABLE)
SELECT 'POD上传率' AS 类型,订单总数,POD是否上传 AS 数量,CAST(CAST((POD是否上传*1.0)/订单总数*100 AS DECIMAL(12,2)) AS VARCHAR) +'%' AS 占比 FROM CTE
UNION ALL
SELECT '准时发车',订单总数,准时发车,CAST(CAST((准时发车*1.0)/订单总数*100 AS DECIMAL(12,2)) AS VARCHAR) +'%' AS 准时发车率 FROM CTE
UNION ALL
SELECT '准时操作到达',订单总数,POD是否上传,CAST(CAST((准时操作到达次数*1.0)/订单总数*100 AS DECIMAL(12,2)) AS VARCHAR) +'%' AS 准时操作到达率 FROM CTE
UNION ALL
SELECT '准时操作签收',订单总数,准时操作签收次数,CAST(CAST((准时操作签收次数*1.0)/订单总数*100 AS DECIMAL(12,2)) AS VARCHAR) +'%' AS 准时操作签收率 FROM CTE
UNION ALL
SELECT '准时配送',订单总数,准时配送,CAST(CAST((准时配送*1.0)/订单总数*100 AS DECIMAL(12,2)) AS VARCHAR) +'%' AS 准时配送率 FROM CTE