MySQL 在一个结果行中选择多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9788670/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
select multiple rows in one result row
提问by Jilco Tigchelaar
In one of my tables i store my advertisement data, thats one row per advertisement. I also store some dates in an other table, but that's one row per date because i don't know howmany dates a specific advertisement gets. I want to select al the dates (where ID adventisement = 1) in the same query as the data selection, seperated bij a komma. Only problem is that i get as many rows as there are dates, i only want one row with al the data…..
在我的一张表中，我存储了我的广告数据，即每个广告一行。我还在另一个表中存储了一些日期，但每个日期只有一行，因为我不知道特定广告获得了多少个日期。我想在与数据选择相同的查询中选择所有日期（其中 ID adventisement = 1），将 bij a komma 分开。唯一的问题是我得到的行数和日期一样多，我只想要一行包含所有数据......
Table 1 (Advertisements)
ID_adv data 1 data2
1 name1 picture1
2 name2 picture2
3 name3 picture3
4 name4 picture4
Table 2 (Dates)
ID ID_adv date
1 2 1-1-2012
2 2 2-1-2012
3 3 1-1-2012
4 3 2-1-2012
5 3 3-1-2012
6 3 4-1-2012
Outcome query (Select ID_adv, data1, data2, dates WHERE ID_adv = 3)
结果查询 (Select ID_adv, data1, data2, dates WHERE ID_adv = 3)
3,name3,picture3,"1-1-2012,2-1-2012,3-1-2012,4-1-2012"
The dates column can be one string with al the dates seperated by a comma….
日期列可以是一个字符串，所有日期用逗号分隔......。
Any ideas?
有任何想法吗？
回答by Marcus Adams
You can use GROUP_CONCAT()and GROUP BYto get the results you desire:
您可以使用GROUP_CONCAT()和GROUP BY来获得您想要的结果：
SELECT t1.*, GROUP_CONCAT(t2.date) AS dates
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.ID_adv = t1.ID_adv
GROUP BY t1.ID_adv
This returns all the dates for each advertisement, concatenated by commas. Where there are no dates in Table2 for a particular advertisment, you'll get NULL for the dates column.
这将返回每个广告的所有日期，用逗号连接。如果表 2 中没有特定广告的日期，则日期列将获得 NULL。
To target a particular advertisement, simply add the WHEREclause:
要定位特定广告，只需添加WHERE子句：
SELECT t1.*, GROUP_CONCAT(t2.date) AS dates
FROM Table1 t1
LEFT JOIN Table2 t2
ON t2.ID_adv = t1.ID_adv
WHERE t1.ID_adv = 3
GROUP BY t1.ID_adv
回答by qubic
You can turn rows into a column with GROUP_CONCATfunction
您可以使用GROUP_CONCAT函数将行转换为列
