解决MySQL左联LIFT JOIN做求和数据出现重复数据。
SELECT
GROUP_CONCAT( DISTINCT fa.nickname ),
GROUP_CONCAT( DISTINCT ev.facility_id ),
CONVERT (
SUM( ev.income ),
DECIMAL ( 10, 2 )) AS su,
COUNT( ev.income ),
GROUP_CONCAT( ev.income )
FROM
every_day_income AS ev
LEFT JOIN ( SELECT GROUP_CONCAT( DISTINCT nickname ) AS nickname, facility_id FROM facility GROUP BY facility_id ) AS fa ON ev.facility_id = fa.facility_id
GROUP BY
ev.facility_id
ORDER BY
su DESC
第一天写好的查寻语句,第一天可以用的,但今天用的时候,发现求和重复出现,导致数据错误。
网上查寻了,主要还是左联的时候,右边表中的数据有多条满足条件,导致左联的时候,左边的数据被重复计算多次。
根据碰到的情况,将右边表中的的数据进行去重,就是LEFT JOIN中的表去重,解决问题。
相关推荐
yangkang 2020-11-09
lbyd0 2020-11-17
sushuanglei 2020-11-12
85477104 2020-11-17
KANSYOUKYOU 2020-11-16
wushengyong 2020-10-28
lizhengjava 2020-11-13
星月情缘 2020-11-13
huangxiaoyun00 2020-11-13
luyong0 2020-11-08
腾讯soso团队 2020-11-06
Apsaravod 2020-11-05
PeterChangyb 2020-11-05
gaobudong 2020-11-04
wwwjun 2020-11-02
gyunwh 2020-11-02
EchoYY 2020-10-31
dingyahui 2020-10-30