Sum，Case，Union，Group by：Php Mysql [英] Sum,Case,Union,Group by : Php Mysql
问题描述
样本日期：
$
<$ c
下面的数据是由group prdid制作的数据
c> wghvalue prdid addby cutequl
0.4 57546 me 1
0.6 71256 me 1
0.2 68754 me 1
以下数据是我通过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 p>
我的价值如下... 2-2 = 0，3-2 = 1,4-4 = 0
但是相同
$ b
sum（case prdb.order - cutid.order = 0 then 1 else 0 end）as newval
获得像...... 3，2，1的值
我认为bove值3,2,1没有得到prdid组
查询
选择
sum（t1.count）作为计数，
sum（t1.wghvalue）作为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（选择ID，wghvalue，addby，prdid
FROM prdentrya
，其中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 |我| | 6
我应该如下所示
count | wghvalue | addby | cutequl
3 | 1.2 |我| | 3
我认为 GROUP BY prdid 不适用于sum case，$
谢谢
首先尝试将结果存储到临时表中。
然后在它上使用SUM操作。
pre $ $ codeREATE TEMPORARY TABLE如果不存在TempTemp AS
（
SELECT
COUNT（pl.id）作为计数，
SUM（pl.wghvalue）作为wghvalue
addby，
SUM（CASE
当prdb.cutaname = cutid.cutname
和prdb.order = cutid.order
和prdb.order - cutid.order = 0
THEN 1
当prdb.order为null或prdb.cutaname为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 prd B关于prod.pid = prda.pid
LEFT JOIN（选择ID，wghvalue，addby，prdid
FROM prdentrya
，其中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
SUM CASE not showing proper details
Sample date :
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
one strange thing :
in above sample data
prdb.order cutid.order
2 2
3 2
4 4
so if i am writing query
prdb.order - cutid.order as newval
i am getting value like ... 2-2 = 0 , 3-2 = 1 , 4-4 =0
but same for
sum(case when prdb.order - cutid.order = 0 then 1 else 0 end) as newval
getting value like ... 3 , 2 , 1
i think bove value 3,2,1 not getting group by prdid
below is query
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
Output coming from above query is :
count | wghvalue | addby | cutequl
3 | 1.2 | me | 6
What i should get as below
count | wghvalue | addby | cutequl
3 | 1.2 | me | 3
i think GROUP BY prdid is not working in sum case , how i can include that in sum case.
thanks
First Try To store result into temp Table. Then use SUM operation on It.
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
这篇关于Sum，Case，Union，Group by：Php Mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
