MySQL插入类别行

我正努力在标题中描述问题,更不用说寻找可能存在的解决方案了,所以请原谅,或将我指向该主题。我需要填充行,因此每个类别有4行,如下面的简化方案所示:

mysql> select * from a;
+-------+
|  Num  |
+-------+
|     1 |
|     2 |
|     3 |
|     4 |
+-------+
mysql> select * from b;
+----------+------+------+
| Category | Num  | Data |
+----------+------+------+
| X        |    2 |   10 |
| X        |    3 |   12 |
| X        |    4 |    8 |
| Y        |    1 |    0 |
| Y        |    2 |   19 |
| y        |    3 |   15 |
| y        |    4 |   22 |
| Z        |    2 |   10 |
+----------+------+------+

我需要的结果是每个类别始终有4行(表b始终每个类别都具有Num = 1、2、3或4),为填充行设置data = null:

mysql> select * from c;
+----------+------+------+
| Category | Num  | Data |
+----------+------+------+
| X        |    1 | NULL |
| X        |    2 |   10 |
| X        |    3 |   12 |
| X        |    4 |    8 |
| Y        |    1 |    0 |
| Y        |    2 |   19 |
| y        |    3 |   15 |
| y        |    4 |   22 |
| Z        |    1 | NULL |
| Z        |    2 |   10 |
| Z        |    3 | NULL |
| Z        |    4 | NULL |
+----------+------+------+

我人为地创建了表,因为我认为它可以帮助(INSERT)查询?

评论
  • miguel
    miguel 回复

    您需要左加入

    select a.num, b.category , b.data
    from tablea a
    left join tableb b on a.num = b.num 
    

    和插入

    insert into tablec (num, category, data)
    select a.num, b.category , b.data
    from tablea a
    left join tableb b on a.num = b.num 
    

    是否需要更新现有行

    update tablec c
    inner join ( 
    select a.num, b.category , ifnull(b.data,0)
    from tablea a
    left join tableb b on a.num = b.num ) t t.num= c.num  and t.category = c.category
    set data  = t.data
    
  • Lily
    Lily 回复

    You can cross join table a with all available categories in table b, and then bring table b with a left join.

    select 
        c.category,
        a.num,
        b.data
    from a
    cross join (select distinct category from b) c
    left join b on b.category = c.category and b.num = a.num
    

    You can easily turn this to an insert query to another table:

    insert into c(category, num, data)
    select 
        c.category,
        a.num,
        b.data
    from a
    cross join (select distinct category from b) c
    left join b on b.category = c.category and b.num = a.num