我有一个应用程序,它使用嵌套的集合模型类来组织我的数据,但我正在尝试编写一个查询,该查询将对我的结果进行分组。我知道我需要在某个地方放一些sub-select语句,但我想不通!
这是我目前的结构:
表:个人
-----------+------------+-----------
|Person_ID | Name | Age |
-----------+------------+-----------
| 1 | Mark Vance | 19 |
| 2 | Michael Tsu| 22 |
| 3 | Mark Jones | 29 |
| 4 | Sara Young | 25 |
-----------+------------+-----------
表:person_to_group
----+------------+-----------
|ID | Person_ID | Group_ID |
----+------------+-----------
| 1 | 3 | 1 |
| 2 | 3 | 2 |
| 3 | 1 | 2 |
| 4 | 4 | 3 |
----+------------+-----------
表:分组
----------+--------------+--------------+-------------
|Group_ID | Group_Name | Group_Left | Group_Right |
----------+--------------+--------------+-------------
| 1 | Root | 1 | 6 |
| 2 | Node | 2 | 5 |
| 3 | Sub Node | 3 | 4 |
----------+--------------+--------------+-------------
我需要用我的结果呈现这样的东西:
//获取此人的group_ID并将其放入class标记中。。。
<li class="2 3">Sara Young is in the Sub Node Group</li>
请注意,尽管Sara在Sub Node组中,但由于她是Node的子代,因此仍会为她提供Node的id。
以下是我正在处理的查询,作为一个起点。
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
任何帮助都将不胜感激!
以下是我如何编写查询:
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
请注意,如果按人名分组,则还需要group_CONCT组名列表。根据你的模式,由于多对多的关系,一个人可能属于多个群体。
我还建议一般不要使用SELECT *
。只需指定所需的列即可。
这有点有趣,因为我同时使用MsSQL和MySql进行编程。在SQL中,我使用了一个名为STUFF的函数。在MySQL中,您可以使用一个名为INSERT的函数。我在MsSQL中尝试了以下查询。手边没有MySQL来尝试我的查询。如果我有时间,我会发布MySQL版本的查询。
DECLARE @person TABLE (Person_ID INT, Name VARCHAR(50), Age INT)
INSERT INTO @person VALUES
(1,'Mark Vance',19),
(2,'Michael Tsu',22),
(3,'Mark Jones',29),
(4,'Sara Young',25)
DECLARE @groups TABLE (Group_ID INT, Group_Name VARCHAR(50), Group_Left INT, Group_Right INT)
INSERT INTO @groups VALUES
(1,'Root',1,6),
(2,'Node',2,5),
(3,'Sub Node',3,4)
DECLARE @person_to_group TABLE (ID INT, Person_ID INT, Group_ID INT)
INSERT INTO @person_to_group VALUES
(1,3,1),
(2,3,2),
(3,1,1),
(4,4,1),
(4,1,1)
SELECT *,STUFF((SELECT ',' + CAST(g.Group_ID AS VARCHAR) FROM @groups g
JOIN @person_to_group pg ON g.Group_ID = pg.Group_ID AND pg.Person_ID = a.Person_ID FOR XML PATH('')) , 1, 1, '' ) FROM @person a
函数:INSERT(str,pos,len,newstr)文档:http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_insert