首页 文章

group concat不同的行

提问于
浏览 300
1
name   type   shape
---------------------
name1  type1  shape1
name1  type2  shape1
name1  type3  shape2

在查询之后我需要那个结果:

name    shape1Types     shape2Types
-----------------------------------
name1   type1, type2    type3

我只能这么想:

select name, group_concat(type) as shape1Types, group_concat(type) as shape2Types 
from table 
where name = 'name1' 
  and shape1Types in (select type from table2 where shape = 'shape1') 
  and shape2Times in (select type from table3 where shape = 'shape2') 
group by name

但是在这里它说shape1Types是未知的列

2 回答

  • 1

    别名名称shape1Types和shape2Types在该WHERE子句中是未知的 .
    因此错误 .

    而不是使用 IN ,您可以 JOIN 到其他2个表中的唯一类型 .

    select 
     t.name, 
     group_concat(shape1.type) as shape1Types, 
     group_concat(shape2.type) as shape2Types 
    from table1 t
    left join (select type from table2 where shape = 'shape1' group by type) shape1 
      on shape1.type = t.type
    left join (select type from table3 where shape = 'shape2' group by type) shape2 
      on shape2.type = t.type
    where t.name = 'name1'
    -- and (shape1.type is not null or shape2.type is not null)
    group by t.name
    
  • 0

    通过使用案例时尝试如下

    select name, group_concat(case when shape='shape1' then type end) as shape1Types, group_concat(case when shape='shape2' then type end) as shape2Types 
        from table 
        group by name
    

相关问题