dongyiyu3953 2018-04-28 01:53
浏览 73
已采纳

用于在具有GROUP_CONCAT的表中显示父子的MySQL查询

We are building an application and client requirement is show child below each parent with a separator.

For eg: I have 2 tables as below

category:             category_description:

id(PK) | parent_id    id(fk) | name
-------|----------    -------|--------
    1  |    0             1  | Fruits
    2  |    1             2  | Apple
    3  |    1             3  | Orange
    4  |    0             4  | Veggies
    5  |    4             5  | Tomatoes
    6  |    4             6  | Cucumber

and so on ...

The result should show in a table as below

ID | Name
---|-----
 1 | Fruits
 2 | Fruits > Apple
 3 | Fruits > Orange
 4 | Veggies
 5 | Veggies > Tomatoes
 6 | Veggies > Cucumber

and so on...

Even if the client inserts randomly, the result should show as above.

I have tried to execute the following query

SELECT c.category_id AS category_id, c.parent_id, c.sort_order, 
GROUP_CONCAT(cd.name SEPARATOR '  >  ') AS name FROM
category c LEFT JOIN category_description cd ON c.category_id = 
cd.category_id LEFT JOIN category_description cd1 ON cd1.category_id =
c.parent_id GROUP BY c.category_id, cd.name

But doesn't show the result as expected. Please let me know, where I am going wrong.

Hope, I could explain it better. Thanks in advance

展开全部

  • 写回答

1条回答 默认 最新

  • draj840143 2018-04-28 02:19
    关注

    I don't know why you are aggregating here; a join query should work, assuming there is only a single level hierarchy:

    SELECT
        c.ID,
        CONCAT(CASE WHEN c.parent_id = 0 THEN cd1.name ELSE cd2.name END,
            CASE WHEN c.parent_id <> 0 THEN CONCAT(' > ', cd1.name) ELSE '' END) AS Name
    FROM category c
    LEFT JOIN category_description cd1
        ON c.id = cd1.id
    LEFT JOIN category_description cd2
        ON c.parent_id = cd2.id
    ORDER BY
        CASE WHEN c.parent_id = 0 THEN c.id ELSE c.parent_id END,
        c.parent_id;
    
        ID  Name
    1   1   Fruits
    2   2   Fruits > Apple
    3   3   Fruits > Orange
    4   4   Veggies
    5   5   Veggies > Tomatoes
    6   6   Veggies > Cucumber
    

    Demo

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥50 使用ADC0809 与 51 单片机设计电路以实现显示电压模拟值
  • ¥15 QGC打开没有地图显示,离线地图也不显示,如何解决?
  • ¥20 Android Studio 的 webview 与访问网络存在的限制
  • ¥15 某帖子的数据集不清楚来源,求帮助
  • ¥15 idea构建mod报错无效的源发行版项目链接,如何解决?
  • ¥15 springboot中的路径问题
  • ¥80 App Store Connect 中设置了订阅项目,Xcode 中预览可以正确显示价格,真机测试却无法显示
  • ¥15 MATLAB的PIV算法问题
  • ¥15 RflySim例程学习:matlab编译报错
  • ¥20 谁来给我解答一下疑惑
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部