mysql - SQL 分组依据和两列聚合

Question

这是我之前问题的后续，@Barmar 友好地回答了: When using MYSQL GROUP BY WITH ROLLUP on Year(date) and Month(date) i am not able to change Null to 'Total'

我有一个 SQL 查询，它从我的事务、subkategori 和kategori 表中获取数据，并且效果很好。但现在我想添加预算表并对预算金额进行求和。我的做法如下:

$stmt = $dbh->prepare("
                SELECT IFNULL(Year, 'All') AS Year,
                       IFNULL(Month, 'All') AS Month,
                       IFNULL(Kategori,'All') AS Kategori,
                       IFNULL(Subkategori,'All') AS Subkategori,
                       Belopp,
                       Budget
                FROM (
                      SELECT YEAR(transaktioner.datum) AS Year,
                             MONTH(transaktioner.datum) AS Month,
                             kategori.kat_namn AS Kategori,
                             subkategori.subkat_namn AS Subkategori,
                             SUM(transaktioner.belopp)  AS Belopp,
                             SUM(budget.budgetedAmmount) AS Budget

                      FROM subkategori
                      LEFT JOIN transaktioner 
                            ON subkategori.subkat_id=transaktioner.subkat_id
                      LEFT JOIN kategori
                            ON subkategori.kat_id = kategori.kat_id
                      LEFT JOIN budget
                            ON subkategori.subkat_id = budget.subkat_id

                      GROUP BY Year, Month, Kategori, Subkategori
                      WITH ROLLUP

                    ) AS x


                    WHERE Year =".$year." 

                    ");

它给了我一个null输出。有人可以帮我解决这个问题吗？

表格如下:

交易者

id
datum
beskrivning
overforing
belopp
balans
subkat_id
konto_id
latest_update

类别

kat_id
kat_namn
type
latest_update

子类别

subkat_id
subkat_namn
kat_id
latest_update

预算表

budget_id
subkat_id
budgetedAmmount
user_id
year

期望结果表

Year    Month   Kategory    Subkategory     Spent   Budget
2017    1       Kat1        Subkat1         1000    500
2017    1       Kat1        Subkat2         500     250
2017    1       kat1        ALL             1500    750
2017    1       Kat2        Subkat1         2000    1000
2017    1       Kat2        Subkat2         450     500
2017    1       kat2        ALL             2450    1500
2017    1        ALL        ALL             3950    2250
ALL     ALL      ALL        ALL             3950    2250

参见 fiddle :http://sqlfiddle.com/#!9/35a778/2

Answer 1

这可能有助于找到答案，如下

1. 移动 where 子句

                          SELECT YEAR(transaktioner.datum) AS Year,
                          MONTH(transaktioner.datum) AS Month,
                          kategori.kat_namn as Kategori,
                          subkategori.subkat_namn as Subkategori,
                          SUM(transaktioner.belopp)  AS Belopp,
                          SUM(budget.budgetedAmmount) AS Budget
   
                   FROM subkategori
                   Left JOIN transaktioner 
                       ON subkategori.subkat_id=transaktioner.subkat_id
                   Left  JOIN kategori
                       ON subkategori.kat_id = kategori.kat_id
                   LEFT JOIN budget
                       ON subkategori.subkat_id = budget.subkat_id
       > here >>   WHERE ???
                   GROUP BY Year, Month,Kategori,Subkategori
                   WITH ROLLUP
   

2. 如果得到结果，请直接在 MySQL 中运行该部分查询

3. 如果得到结果，请在 MySQL 中运行完整的查询

4. 检查您的 PHP 是否正确注入(inject)了年份参数，只需输出生成的查询代码，然后尝试在 MySQL 中运行该代码

我现在已经退出了，调试 PHP 不是“我的事”

哦，有一个小问题，与 IFNULL() 等效的 ANSI SQL 函数是 COALESCE()。虽然 IFNULL() 在 MySQL 中很好，但我鼓励您使用 COALESCE()，但显然您不必这样做。