首页 文章

具有group_concat，GROUP BY和null值的月度值

提问于
浏览 次
0

即使没有付款，我也会在一年内使用group_concat付款'm trying to output each months'，同时按类别进行分组 . amount 是每个月按月排序的总付款的逗号分隔列表 . 以下查询的当前输出是：

label     amount
NULL      0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
Food      17.90,977.25
Transport 40.00

我无法管理将NULL标签与其他标签合并以及没有支出的列表类别 . 我想这可能与我加入 table 的方式有关？

预期的输出是：

label       amount
Healthcare  0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
Food        0.00,0.00,17.90,0.00,977.25,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00
Transport   40.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00,0.00

我目前的查询是：

select label, group_concat(payment) as amount
from(
SELECT 
categoryName as label, 
YEAR(t1.transactionDate),
t.month,
coalesce(SUM(t1.transactionAmount),0) payment
FROM
(SELECT 1 AS `month`
UNION 
SELECT 2 AS `month`
UNION  
SELECT 3 AS `month`
UNION 
 SELECT 4 AS `month`
UNION
 SELECT 5 AS `month`
UNION
 SELECT 6 AS `month`
UNION 
 SELECT 7 AS `month`
UNION 
 SELECT 8 AS `month`
UNION 
  SELECT 9 AS `month`
UNION 
  SELECT 10 AS `month`
UNION 
  SELECT 11 AS `month`
UNION 
SELECT 12 AS `month`
) AS t
LEFT JOIN transaction t1 on(t.month = MONTH(t1.transactionDate))
LEFT JOIN category USING (categoryID)
where userid = 1 or userid is null group by t.month)a group by label

任何帮助都会很棒，因为我一直在努力解决这个问题一段时间 . 谢谢！

mysql

1 回答

  • 0

    我认为关键是在类别和月份上使用笛卡尔联合 . 我没有测试过这个，但它必须接近解决方案：

    select label, group_concat(monthly_sum separator ',') as amount
from (
    select c.categoryName as label, coalesce(sum(t1.payment), 0) as monthly_sum
    from category c
    join (
        SELECT 1 AS month
        UNION 
        SELECT 2 AS month
        UNION  
        SELECT 3 AS month
        UNION 
        SELECT 4 AS month
        UNION
        SELECT 5 AS month
        UNION
        SELECT 6 AS month
        UNION 
        SELECT 7 AS month
        UNION 
        SELECT 8 AS month
        UNION 
        SELECT 9 AS month
        UNION 
        SELECT 10 AS month
        UNION 
        SELECT 11 AS month
        UNION 
        SELECT 12 AS month
    ) t
    left join transaction t1 on t.month = MONTH(t1.transactionDate)
    where userid = 1 or userid is null
    group by t.month, c.categoryName
) a
group by label
    回复于

相关问题