方便更快捷的说明问题,可以按需填写(可删除)
使用环境:
ss5.0、springboot,引入ss之后sql无法正常运行。
场景、问题:
SELECT dd.FullDateAlternateKey dataTime, IFNULL(rr.innum,0) innum, IFNULL(rr.jkl,0.00) jkl
FROM dimdate dd
LEFT JOIN
(SELECT SUM(rsd.innum) innum,ROUND(SUM(rsd.innum)/SUM(rsd.area),2) jkl,rsd.datatime datatime
FROM report_site_day rsd
LEFT JOIN site_relation sr ON sr.childId=rsd.relationId
LEFT JOIN project_status ps ON rsd.relationId=ps.site_id
LEFT JOIN site_all_info sai ON rsd.relationId=sai.SiteId
LEFT JOIN privilege_userssite AS pu ON pu.sitekey = sai.siteid
LEFT JOIN userinsmallprogram AS us ON us.userid = pu.userid
WHERE
us.openid = ?
AND sr.id =?
AND ps.status=1
AND rsd.dataTime BETWEEN ? AND ?
GROUP BY rsd.datatime
)rr
ON dd.FullDateAlternateKey=rr.datatime
WHERE dd.FullDateAlternateKey BETWEEN ? AND ?
ORDER BY dd.FullDateAlternateKey ASC
已进行操作:
官方文档介绍 CASE WHEN 和 UNION、UNION ALL不支持