mysql - Deaggrigate 然后应用 With Rollup

Question

我的查询只需要稍微调整一下。我有一个表格，其中充满了赋值和值，如下所示:

a_inv
assignment_id    total
=========================
1                500

然后两个相关的表，类别和 channel 进行赋值，例如:

a_cat
assignment_id    category_id
==============================
1                1
1                11

a_ch
assignment_id    channel_id
==============================
1                16
1                25

所以我需要做的是弄清楚每个类别和 channel 花费了多少(汇总)。

这是我最接近的查询:

SELECT 
    ac.category_id, ach.channel_id, ((ia.total/COUNT(DISTINCT ac2.category_id))/COUNT(DISTINCT ach2.channel_id)) AS cur_total
FROM 
    a_cat ac
LEFT JOIN 
    a_ch ach ON ach.assignment_id = ac.assignment_id
LEFT JOIN 
    a_inv ia ON ia.assignment_id = ac.assignment_id
LEFT JOIN
    a_cat ac2 ON ach.assignment_id = ac2.assignment_id
LEFT JOIN 
    a_ch ach2 ON ach2.assignment_id = ac.assignment_id
WHERE
    ac.assignment_id = 1
GROUP BY
    ac.category_id, ach.channel_id WITH ROLLUP

它给了我一个非常接近我需要的结果:

category_id   channel_id   cur_total
=====================================
1             16           125.0000
1             25           125.0000
1             NULL         125.0000     <---- this should be "250.0000"
11            16           125.0000
11            25           125.0000
11            NULL         125.0000     <---- this should be "250.0000"
NULL          NULL         125.0000     <---- this should be "500.0000"

非常感谢任何帮助。

谢谢!

Answer 1

试试这个

SELECT category_id, channel_id, SUM(cur_total) 
FROM (
    SELECT 
        ac.category_id, ach.channel_id, ((ia.total/COUNT(DISTINCT ac2.category_id))/COUNT(DISTINCT ach2.channel_id)) AS cur_total
    FROM 
        a_cat ac
    LEFT JOIN 
        a_ch ach ON ach.assignment_id = ac.assignment_id
    LEFT JOIN 
        a_inv ia ON ia.assignment_id = ac.assignment_id
    LEFT JOIN
        a_cat ac2 ON ach.assignment_id = ac2.assignment_id
    LEFT JOIN 
        a_ch ach2 ON ach2.assignment_id = ac.assignment_id
    WHERE
        ac.assignment_id = 1
    GROUP BY
        ac.category_id, ach.channel_id
    ) as t1
GROUP BY category_id, channel_id WITH ROLLUP