php - 求和、大小写、并集、分组 by : Php Mysql

Question

SUM CASE 未显示正确的详细信息

日期示例:

below is data with group by prdid

wghvalue    prdid  addby  cutequl
0.4         57546    me     1
0.6         71256    me     1
0.2         68754    me     1

below is data when i remove group by prdid

wghvalue    prdid  addby  cutequl
0.1         57546    me     1
0.1         57546    me     1
0.2         57546    me     1
0.1         71256    me     1
0.5         71256    me     1
0.2         68754    me     1

一件奇怪的事情:

在上面的示例数据中

prdb.order  cutid.order
    2           2
    3           2
    4           4

所以如果我正在编写查询

prdb.order - cutid.order as newval

我得到的值类似于... 2-2 = 0 , 3-2 = 1 , 4-4 =0

但同样

sum(case when prdb.order - cutid.order = 0 then 1 else 0 end) as newval

获取像... 3 , 2 , 1 这样的值

我认为 bove 值 3,2,1 没有按 prdid 进行分组

下面是查询

SELECT 
    sum(t1.count) as count,
    sum(t1.wghvalue) as wghvalue,
    t1.addby,
    SUM(t1.cutequl) as cutequl
FROM 
    (SELECT  
        COUNT(pl.id) as count,
        SUM(pl.wghvalue) as wghvalue
        addby,
        SUM(CASE 
            WHEN prdb.cutaname = cutid.cutname
                 AND prdb.order = cutid.order
                 AND prdb.order - cutid.order = 0  
            THEN 1 
            else 0 
        end) as cutequl
    FROM 
        product AS prod
        LEFT JOIN producta AS prda ON prod.id = prda.id
        LEFT JOIN prdoctb AS prdb ON prod.pid = prda.pid 
        LEFT JOIN ( SELECT id, wghvalue, addby, prdid
                    FROM prdentrya
                    WHERE 1=1 
                    GROUP BY prdid
                    UNION
                    SELECT id, wghvalue, addby, prdid
                    FROM prdentryb
                    WHERE 1=1
                    GROUP BY prdid
                   ) AS pl ON( pl.prdid = prda.prdid  )
        LEFT JOIN cut AS cutid ON prdb.cutaname = cutid.cutname  
    WHERE 
        pl.aadby = 103 
    GROUP BY 
        pl.prdid
    ORDER BY
        pl.prdid ASC,
        pl.aadby ASC 
    ) t1
group by t1.addby

上述查询的输出是:

count | wghvalue | addby | cutequl 
3     |  1.2     |  me   |   6

我应该得到如下

count | wghvalue | addby | cutequl 
3     |  1.2     |  me   |   3

我认为GROUP BY prdid在sum情况下不起作用，我如何将其包含在总和中。

谢谢

Answer 1

首先尝试将结果存储到临时表中。然后对其进行SUM运算。

                CREATE TEMPORARY TABLE IF NOT EXISTS TempTemp AS
                (
                    SELECT  
                        COUNT(pl.id) as count,
                        SUM(pl.wghvalue) as wghvalue
                        addby,
                        SUM(CASE 
                            WHEN prdb.cutaname = cutid.cutname
                                 AND prdb.order = cutid.order
                                 AND prdb.order - cutid.order = 0  
                            THEN 1 
                            WHEN prdb.order is null or prdb.cutaname  is null THEN 1
                            else 0 
                        end) as cutequl
                    FROM 
                        product AS prod
                        LEFT JOIN producta AS prda ON prod.id = prda.id
                        LEFT JOIN prdoctb AS prdb ON prod.pid = prda.pid 
                        LEFT JOIN ( SELECT id, wghvalue, addby, prdid
                                    FROM prdentrya
                                    WHERE 1=1 
                                    GROUP BY prdid
                                    UNION
                                    SELECT id, wghvalue, addby, prdid
                                    FROM prdentryb
                                    WHERE 1=1
                                    GROUP BY prdid
                                   ) AS pl ON( pl.prdid = prda.prdid  )
                        LEFT JOIN cut AS cutid ON prdb.cutaname = cutid.cutname  
                    WHERE 
                        pl.aadby = 103 
                    GROUP BY 
                        pl.prdid
                    ORDER BY
                        pl.prdid ASC,
                        pl.aadby ASC 
                )


                SELECT 
                    sum(t1.count)  , sum(t1.wghvalue) ,t1.addby,SUM(t1.cutequl) , From 
                    TempTemp t1 
                    group by addby