在一个结果行中选择多行 [英] select multiple rows in one result row

问题描述

在我的一张桌子中，我存储了我的广告数据，即每个广告一行. 我还将一些日期存储在另一个表中，但是每个日期只能存储一行，因为我不知道特定广告会获得多少个日期. 我想在与数据选择相同的查询中选择所有日期(其中ID adventisement = 1)，将日期分开.唯一的问题是，我得到的行数与日期一样多，我只希望一行包含所有数据……..

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…..

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

结果查询(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"

dates列可以是一个字符串，且日期之间用逗号分隔....

The dates column can be one string with al the dates seperated by a comma….

有什么想法吗?

推荐答案

您可以使用GROUP_CONCAT()和GROUP BY获得所需的结果:

You can use GROUP_CONCAT() and GROUP BY to get the results you desire:

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

这将返回每个广告的所有日期，并以逗号分隔.在Table2中没有特定广告的日期的地方，dates列的值为NULL.

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.

要定位特定广告，只需添加WHERE子句:

To target a particular advertisement, simply add the WHERE clause:

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

这篇关于在一个结果行中选择多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！