mysql父id循环

Question

我有两个Mysql表

participants

id   |   name   | lastname |
-----------------------------
1    |    Jon   |   Bush   |
-----------------------------
2    |  Stephen |   Eagle  |

和

posts

id    |  parentid |  Title  |   text
--------------------------------------
1     |    1      |  Title1 |  Text1
---------------------------------------
2     |   1       | title3  |  text2
---------------------------------------
3     |      1    | title4  | text4
--------------------------------------
4     |      2    |   title |   ttext

我要走出桌子

--------------------------
id (1) | Jon   | Title1, title3, title4
------------------------------
id (2) | Stephen | title

我试着这样做

$result = mysql_query("SELECT name, Title, participants.id, parent FROM aposts, participants WHERE paricipants.id = parent.parent group by last_name  ORDER BY .......");

但是在这个cas中，我不能在parent上循环来获取这个parent的所有帖子……也许有人能帮我..。

Answer 1

我不确定这是不是你想要的。在您的示例中，您希望为每个ID和Name返回用逗号分隔的Title。MySQL有一个名为GROUP_CONCAT的内置函数，它连接行而不是列。

SELECT  a.ID, a.Name,
        GROUP_CONCAT(b.Title) TitleList
FROM    participants a
        INNER JOIN posts b
            ON a.ID = b.parentID
GROUP   BY  a.ID, a.Name

• SQLFiddle Demo
• MySQL GROUP_CONCAT()

输出

╔════╦═════════╦══════════════════════╗
║ ID ║  NAME   ║      TITLELIST       ║
╠════╬═════════╬══════════════════════╣
║  1 ║ Jon     ║ Title1,title3,title4 ║
║  2 ║ Stephen ║ title                ║
╚════╩═════════╩══════════════════════╝