doujianglin6704 2011-06-02 08:22
浏览 255
已采纳

GROUP_CONCAT与嵌套集模型

I have an application that uses a nested set model class to organise my data, however I'm trying to write a query that will group_concat my results. I know I need to put some sub select statements somewhere but I can't figure it out!

Here's my structure at the moment:

table: person

 -----------+------------+-----------
|Person_ID  | Name       | Age       |
 -----------+------------+-----------
| 1         | Mark Vance | 19        |
| 2         | Michael Tsu| 22        |
| 3         | Mark Jones | 29        |
| 4         | Sara Young | 25        |
 -----------+------------+-----------

table: person_to_group

 ----+------------+-----------
|ID  | Person_ID  | Group_ID  |
 ----+------------+-----------
| 1  | 3          | 1         |
| 2  | 3          | 2         |
| 3  | 1          | 2         |
| 4  | 4          | 3         |
 ----+------------+-----------

table: groups

 ----------+--------------+--------------+-------------
|Group_ID  | Group_Name   | Group_Left   | Group_Right |
 ----------+--------------+--------------+-------------
| 1        | Root         | 1            | 6           |
| 2        | Node         | 2            | 5           |
| 3        | Sub Node     | 3            | 4           |
 ----------+--------------+--------------+-------------

I need to render something like this with my results:

//Grab the group_IDs for this person and put them in the class tag...

<li class="2 3">Sara Young is in the Sub Node Group</li>

Notice that although Sara is in the Sub Node group, she is still being given the id for Node aswell because she is a child of Node.

The following is the query that I am working with as a starting point.

SELECT *, GROUP_CONCAT( CAST( gg.Group_ID AS CHAR ) SEPARATOR ' ' ) Group_IDs
        FROM groups gg
        LEFT JOIN person_to_group AS t1 ON gg.Group_ID = t1.Group_ID
        LEFT JOIN person AS t2 ON t2.Person_ID = t1.Person_ID
        GROUP BY t2.per_ID 
        ORDER BY t2.Name ASC

Any help would be much appreciated!

展开全部

  • 写回答

2条回答 默认 最新

  • doujilou3903 2011-06-02 08:55
    关注

    Here's how I'd write the query:

    SELECT p.Name, 
      GROUP_CONCAT( g.Group_Name ) AS Group_List,
      GROUP_CONCAT( CAST( gg.Group_ID AS CHAR ) SEPARATOR ' ' ) AS Group_ID_List
    FROM person AS p
    INNER JOIN person_to_group AS pg ON p.Person_ID = pg.Person_ID
    INNER JOIN groups AS g ON pg.Group_ID = g.Group_ID
    INNER JOIN groups AS gg ON g.Group_Left BETWEEN gg.Group_Left AND gg.Group_Right
    GROUP BY p.Name
    ORDER BY p.Name ASC
    

    Note that if you group by person name, you also need to GROUP_CONCAT the list of group names. According to your schema, a person could belong to multiple groups, because of the many-to-many relationship.

    I also recommend against using SELECT * in general. Just specify the columns you need.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)
编辑
预览

报告相同问题?

悬赏问题

  • ¥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】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部