首页 > 数据库技术 > 详细

sql语句求百分比

时间:2014-11-17 17:23:13      阅读:311      评论:0      收藏:0      [点我收藏+]

此sql语句包括了两个聚合函数做除法求百分比,并保留两位小数,直接输出字符串形式的百分比。以及对case when在聚合函数的应用。

SELECT ss.SS_NAME,SS_ID,
COUNT(ea.EA_ID) AS EACounts,--回单交换单个数
COUNT(eb.EB_ID) AS EBCounts,--交换单个数
COUNT(ps.PS_ID) AS PSCounts,--签收单个数
COUNT(SW_ID) AS SWCounts,--运单数个数
COUNT(CASE WHEN PS_STATUS=1 THEN PS_ID END) AS PSSCounts,--签收成功单个数
CAST(CONVERT( DECIMAL(18,2),COUNT(ea.EA_ID)/CAST(ISNULL(NULLIF(COUNT(EB_ID),0),1) AS FLOAT)*100)AS VARCHAR(10))+% AS ReturnPer,--回单率
CAST(CONVERT(DECIMAL(18,2),COUNT(ps.PS_ID)/CAST(ISNULL(NULLIF(COUNT(SW_ID),0),1) AS FLOAT)*100)AS VARCHAR(10))+% AS SignPer ,--签收率
CAST(CONVERT(DECIMAL(18,2),COUNT(CASE WHEN PS_STATUS=1 THEN PS_ID END)/CAST(ISNULL(NULLIF(COUNT(PS_ID),0),1) AS FLOAT)*100 ) AS VARCHAR(10))+% AS SignSucPer --签收成功率
FROM dbo.SBD_WAYBILL sw
LEFT JOIN dbo.PDA_SININFO ps ON sw.SW_ID=ps.PS_SW_ID
LEFT JOIN dbo.SBD_EXCHANGE_RETURN_BILL ea ON ea.EA_SW_ID=sw.SW_ID
LEFT JOIN dbo.SBD_EXCHANGE_BILL eb ON eb.EB_SW_ID=sw.SW_ID
LEFT JOIN dbo.SBD_STATION ss ON ss.SS_ID=sw.SW_DESTINATION
WHERE 1=1 GROUP BY SS_ID,SS_NAME
ORDER BY SS_NAME

 

sql语句求百分比

原文:http://www.cnblogs.com/sunxi/p/4103918.html

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!