代码之家  ›  专栏  ›  技术社区  ›  Eleanor

按SQL分组时处理未观察的类别

  •  0
  • Eleanor  · 技术社区  · 7 年前

    我有一个关于处理未观察到的类别的问题。 例如,

    Month   Number
    Jan.    1
    Jan.    4
    Mar.    5
    Apr.    3
    July.   1
    Sept.   4
    Nov.    7
    Nov.    8
    Dec.    9
    Dec.    1
    Dec.    2
    

    如果我使用这个查询

    select t.Month, sum(t.Number) as sum from table t group by t.Month;
    

    它只会返回这样的一张桌子

    Month   sum
    Jan.    5
    Mar.    5
    Apr.    3
    July.   1
    Sept.   4
    Nov.    15
    Dec.    12
    

    但我想要的其实是这个

    Month   sum
    Jan.    5
    Feb.    0
    Mar.    5
    Apr.    3
    May.    0
    June.   0
    July.   1
    Aug.    0
    Sept.   4
    Oct.    0
    Nov.    15
    Dec.    12
    

    它还将包括那些未观察到的月份,返回值为0。

    我怎么能这样做?

    谢谢您。

    2 回复  |  直到 7 年前
        1
  •  1
  •   D-Shih    7 年前

    LEFT JOIN

    select cd.months, coalesce(sum(t.Number),0) as 'sum' 
    from (
    SELECT 'Jan.' as 'months' UNION ALL
    SELECT 'Feb.' UNION ALL
    SELECT 'Mar.' UNION ALL
    SELECT 'Apr.' UNION ALL
    SELECT 'May.' UNION ALL
    SELECT 'June.' UNION ALL
    SELECT 'July.' UNION ALL
    SELECT 'Aug.' UNION ALL
    SELECT 'Sept.' UNION ALL
    SELECT 'Oct.' UNION ALL
    SELECT 'Nov.' UNION ALL
    SELECT 'Dec.' 
    ) cd LEFT JOIN T on cd.months = t.Month
    group by cd.months;
    

    https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=1aca12dbcc087ce85145e3a8919a6182

        2
  •  0
  •   jspcal    7 年前

    left outer join

    create table month (month int);
    insert into month values
        (1), (2), (3), (4), (5), (6), (7), (8), (9), (10), (11), (12);
    

    create table month_val (month int, val int);
    insert into month_val values
        (1, 10), (1, 11), (2, 20), (2, 21), (3, 30);
    

    select month.month, coalesce(sum(month_val.val), 0)
    from month
    left join month_val on (month.month = month_val.month)
    group by month.month
    

    month   coalesce(sum(month_val.val), 0)
    1   21
    2   41
    3   30
    4   0
    5   0
    6   0
    7   0
    8   0
    9   0
    10  0
    11  0
    12  0