SQL 的坑1 除法“”不可用“”

阅读量：5239 次

发布时间：2019-06-14

本文共 2271 字，大约阅读时间需要 7 分钟。

今天工作中遇见一问题，有5各部分，现要求5个部分各自的比例，SQL语句没有问题，后来还试了“加”，“减”，“乘”，“Round”，结果都对，唯独“除法”得不到结果。

之前的语句和结果

select Date_1,    isnull(APP,0) as APP,    isnull(邮件,0)as YouJ,    isnull(微信,0) as WeiX,    isnull(微博,0) as WeiB,    isnull(电信渠道,0) as DXQD,    (isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0))as total,    isnull(APP,0)+(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)) as APP_Ratio_text,    ROUND(isnull(邮件,0)/(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)),6) as YouJ_Ratio,    ROUND(isnull(微信,0)*(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)),6) as WeiX_Ratio_test,    isnull(微博,0)/(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)) as WeiB_Ratio,    isnull(电信渠道,0)/(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)) as DXQD_Ratio    from(select convert(varchar(100),date_1,111) as Date_1,promotion_unit,sum(amount) as amount1from dbo.EDM_message_consume('2016/06/26','2016/06/27')where project_type is not null group by promotion_unit,date_1) newtbpivot (sum(amount1)  for newtb.promotion_unit in (APP,邮件,微信,微博,电信渠道)) as pivottb

后同事提醒试乘1.0,遂解决如下

select Date_1,    isnull(APP,0) as APP,    isnull(邮件,0)as YouJ,    isnull(微信,0) as WeiX,    isnull(微博,0) as WeiB,    isnull(电信渠道,0) as DXQD,    (isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0))as total,    isnull(APP,0)*1.0/(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)) as APP_Ratio,    isnull(邮件,0)*1.0/(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)) as YouJ_Ratio,    isnull(微信,0)*1.0/(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)) as WeiX_Ratio,    isnull(微博,0)*1.0/(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)) as WeiB_Ratio,    isnull(电信渠道,0)*1.0/(isnull(APP,0)+isnull(邮件,0)+isnull(微信,0)+isnull(微博,0)+isnull(电信渠道,0)) as DXQD_Ratio    from(select convert(varchar(100),date_1,111) as Date_1,promotion_unit,sum(amount) as amount1from dbo.EDM_message_consume('2016/06/26','2016/06/27')where project_type is not null group by promotion_unit,date_1) newtbpivot (sum(amount1)  for newtb.promotion_unit in (APP,邮件,微信,微博,电信渠道)) as pivottb

o_sql1.0.jpg