我有两个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
And I need get out table
--------------------------
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 .......");
但是在这种情况下,我不能得到循环的父出这个父的所有帖子…也许有人能帮我....
我不确定这是否正是你想要的。看到您的示例,您希望为每个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演示/gh>
- MySQL GROUP_CONCAT ()
输出
╔════╦═════════╦══════════════════════╗
║ ID ║ NAME ║ TITLELIST ║
╠════╬═════════╬══════════════════════╣
║ 1 ║ Jon ║ Title1,title3,title4 ║
║ 2 ║ Stephen ║ title ║
╚════╩═════════╩══════════════════════╝