mysql sum join 多表统计求和时不正确的问题

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_34412985/article/details/84841606

但是我们发现,这个数据是错误的

于是我想到了用 distinct  但是发现用不了

于是又想到 group by

出来的结果是下面这样的


发现统计只是分组统计,没达到所有行统计,而且统计的数据不正确


于是我写了下面的语句来查看结果,加多了一个count函数查看

    SELECT s.oid,p.oid  AS poid,  SUM( priceCount) AS count_price, COUNT(p.oid) FROM mobile_shops_orders AS s
    LEFT JOIN mobile_shops_products AS p ON p.oid = s.oid
    WHERE s.userid =  '2'
    GROUP BY poid

 

出来的结果是下面的,一看就知道是什么原因了
 
oid     poid     count_price     COUNT(p.oid)
2015031809153611521     2015031809153611521     664.00     2
2015031809155386138     2015031809155386138     40.00     1

 

最后我们的语句应该是下面这个的,join 后面这个是重点,意思是返回不重复的数据

SELECT s.oid, p.oid AS poid, SUM(priceCount) AS count_price

FROM mobile_shops_orders AS s

LEFT JOIN  (select distinct  oid from  mobile_shops_products)   AS  p ON p.oid = s.oid WHERE s.userid =  '2’


oid     poid     count_price
2015031809153611521     2015031809153611521     372.00


这里有几个要注意的问题,一是,group by 是用于分组统计,如果要所有行统计不能用他

二需要使用子句先排除重复行,否则也会出得统计不正确的问题

还有,看网上说 返回 null 时sum等统计函数是不正确的

展开阅读全文

sum求和去重问题

10-22

[code=sql]SELECT B.TREE_NODE rn , SUM(CASE WHEN A.EMPL_CLASS IN ('001' rn , '009' rn , '002' rn , '003' rn , '004' rn , '005' rn , '006' rn , '007' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('001' rn ,'009') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('009') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('008' rn , '010' rn , '011' rn , '016' rn , '017') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('008') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('011') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('017') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('002' rn , '003' rn , '004' rn , '005' rn , '006' rn , '007') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('002') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('004') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('003') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('006') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN A.EMPL_CLASS IN ('005' rn ,'007') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('01' rn , '02' ) rn AND D.SPG_COMP2_LEVEL = '01' rn AND a.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('03' rn ,'04' rn , '05') rn AND D.SPG_COMP2_LEVEL = '01' rn AND a.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('01' rn , '02' ) rn AND D.SPG_COMP2_LEVEL = '02' rn AND a.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('03' rn ,'04' rn , '05') rn AND D.SPG_COMP2_LEVEL = '02' rn AND a.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('01' rn , '02' ) rn AND D.SPG_COMP2_LEVEL = '03' rn AND a.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('03' rn ,'04' rn , '05') rn AND D.SPG_COMP2_LEVEL = '03' rn AND a.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('01' rn , '02' ) rn AND D.SPG_COMP2_LEVEL = '04' rn AND a.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN E.SPG_POS_LEV_ID IN ('03' rn ,'04' rn , '05') rn AND D.SPG_COMP2_LEVEL = '04' rn AND a.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn , SUM(CASE WHEN E.SPG_POS_LEV_ID NOT IN ('01' rn , '02' rn , '03' rn , '04' rn , '05') rn AND a.EMPL_CLASS IN ('001' rn , '009' rn , '008' rn , '010' rn , '011' rn , '016' rn , '017' rn , '018' rn , '019' rn , '020') THEN 1 ELSE 0 END) rn FROM PS_JOB A rn , PS_SPH_FOR_DEPT_TL B rn , PS_DEPT_TBL C rn , PS_COMPANY_TBL D rn , PS_JOBCODE_TBL E rn WHERE A.EFFDT = ( rn SELECT MAX(A1.EFFDT) rn FROM PS_JOB A1 rn WHERE A1.EMPLID = A.EMPLID rn AND A1.EMPL_RCD = A.EMPL_RCD rn AND A1.EFFDT <= :1) rn AND A.EFFSEQ = ( rn SELECT MAX(A2.EFFSEQ) rn FROM PS_JOB A2 rn WHERE A2.EMPLID = A.EMPLID rn AND A2.EMPL_RCD = A.EMPL_RCD rn AND A2.EFFDT = A.EFFDT) rn AND A.DEPTID = B.TREE_NODE_CHILD rn AND (B.SPG_DEPT_CLASS = '01' rn OR B.SPG_DEPT_CLASS = '18') rn AND A.JOB_INDICATOR = 'P' rn AND A.HR_STATUS = 'A' rn AND A.BUSINESS_UNIT = 'BU100' rn AND B.SETID = C.SETID rn AND B.TREE_NODE = C.DEPTID rn AND C.EFFDT = ( rn SELECT MAX(C1.EFFDT) rn FROM PS_DEPT_TBL C1 rn WHERE C1.SETID = C.SETID rn AND C1.DEPTID = C.DEPTID rn AND C1.EFFDT <= :1) rn AND D.EFFDT = ( rn SELECT MAX(D1.EFFDT) rn FROM PS_COMPANY_TBL D1 rn WHERE D1.COMPANY = D.COMPANY rn AND D1.EFFDT <= :1) rn AND E.EFFDT = ( rn SELECT MAX(E1.EFFDT) rn FROM Ps_Jobcode_Tbl E1 rn WHERE E1.setid = E.setid rn AND E1.jobcode = E.jobcode rn AND E1.EFFDT <= :1) rn AND A.COMPANY = D.COMPANY rn AND A.JOBCODE = E.JOBCODE rn AND EXISTS ( rn SELECT 1 rn FROM PS_SPH_DEPT_TREE M rn WHERE M.SPH_PARENT_NODE = :3 rn AND B.TREE_NODE = DECODE(:2, 'Y', M.SPH_CHILD_NODE, :3) rn AND (M.SPH_CHILDNOD_CLASS = '01' rn OR M.SPH_CHILDNOD_CLASS = '18')) rn GROUP BY B.TREE_NODE, C.SPG_SORT_COMTREE rn ORDER BY C.SPG_SORT_COMTREE[/code] 论坛

没有更多推荐了,返回首页