使用SQL server 查询语句的if函数时候,一直报错,发现是SQL 的函数在不同的数据库不通用的原因。
select Submission_Date,
,sum(IF(Rating = 1,1,0)) AS '1分'
,sum(IF(Rating = 2,1,0)) AS '2分'
,sum(IF(Rating = 3,1,0)) AS '3分'
,sum(IF(Rating = 4,1,0)) AS '4分'
,sum(IF(Rating = 5,1,0)) AS '5分'
,count(Submission_Date)
from VO_DETAILED_CUSTOMER_REVIEWS
group by Submission_Date,Rating
order by Submission_Date Desc
错误:
原因:
SQL Server 没有自定义的IF函数,MySQL可以用的的IF函数功能在这里没法实现,用 case when。
解决方法:
select Submission_Date
,count(CASE WHEN Rating = '1' THEN Rating END) as '1 分'
,count(CASE WHEN Rating = '2' THEN Rating END) as '2 分'
,count(CASE WHEN Rating = '3' THEN Rating END) as '3 分'
,count(CASE WHEN Rating = '4' THEN Rating END) as '4 分'
,count(CASE WHEN Rating = '5' THEN Rating END) as '5 分'
from VO_DETAILED_CUSTOMER_REVIEWS
group by Submission_Date
order by Submission_Date Desc