首页 文章

group concat不同的行

提问于
浏览 次
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是未知的列

sql mysqli

2 回答

  • 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
    回复于
  • 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
    回复于

相关问题