Mysql在复杂查询中与GROUP_CONCAT联接
问题描述:
我遇到了运行查询的问题。 我有三个表:Mysql在复杂查询中与GROUP_CONCAT联接
类别
| id | name |
| --- | ---------- |
| 1 | Logo |
| 2 | Poster |
| 3 | Stationary |
| 4 | Web |
| 5 | Projects |
| 6 | Sporadic |
照片
| id | portid | image | preveiw |
| --- | ------ | ---------------- | ------- |
| 171 | 30 | a30preview.jpg | 1 |
| 172 | 30 | b30.jpg | |
| 173 | 30 | c30.jpg | |
| 174 | 32 | a32preview.jpg | 1 |
| 175 | 32 | b32.jpg | |
| 176 | 33 | a33preview.jpg | 1 |
| 179 | 33 | b33.jpg | |
| 180 | 41 | a41preview.jpg | 1 |
组合
| id | catid | type | title | text | date |
| --- | ------ | ----- | ------------ | ------------ | ------ |
| 30 | 2 | M | xxxxxxxx | xxxxxxxx | xxxxxx |
| 32 | 2 | M | xxxxxx | xxxxxx | xxxxxx |
| 33 | 2 | L | xxxxxxxxxx | xxxxxxxxxx | xxxxxx |
| 41 | 1 | L | xxxx | xxxx | xxxxxx |
| 45 | 2 | L | xxxxx | xxxxx | xxxxxx | <-(This record has no image in 'photos' table, so it's not in 'output')
我想这些记录作为输出(其中name =“海报”)
| id | catid | name | type | title | text | date | image <-(first image is the one that has 'photos.preview' = 1)|
| --- | ------ | -------- | ----- | ------------ | ------------ | ------ | ---------------------------------- |
| 30 | 2 | Poster | M | xxxxxxxx | xxxxxxxx | xxxxxx | a30preview.jpg, b30.jpg, c30.jpg |
| 32 | 2 | Poster | M | xxxxxx | xxxxxx | xxxxxx | a32preview.jpg, b32.jpg |
| 33 | 2 | Poster | L | xxxxxxxxxx | xxxxxxxxxx | xxxxxx | a33preview.jpg, b33.jpg |
我已经试过这条SQL语句:
select * from `portfolios`
inner join `categories` on `portfolios`.`catid` = `categories`.`id`
inner join `photos` on `portid` = `portfolios`.`id`
where `categories`.`name` = "Poster"
当然,它不会因为我的工作不知道我应该在哪里放置GROUP_CONCAT
。任何想法 ?
答
考虑,即GROUP_CONCAT()
是一个聚合函数,你必须使用GROUP BY
,从而获得所需结果集。如果你拼写出列,而不是使用
SELECT
`portfolios`.*,
`categories`.*,
GROUP_CONCAT(
`photos`.`image`
ORDER BY `photos`.`preveiw` DESC
SEPARATOR ', '
) as `image`
FROM
`portfolios`
INNER JOIN `categories` ON `portfolios`.`catid` = `categories`.`id`
INNER JOIN `photos` ON `portid` = `portfolios`.`id`
WHERE
`categories`.`name` = 'Poster'
GROUP BY
`portfolios`.`id`
+1
不错,很清楚。谢谢。 – 2014-09-01 10:18:36
+1
@ Siamak.A.M干杯。第一次很棘手,但在练习中你会习惯。 – BlitZ 2014-09-01 10:22:57
答
SELECT portfolios.*, categories.*, GROUP_CONCAT(photos.image) AS image
FROM `portfolios`
inner join `categories` on `portfolios`.`catid` = `categories`.`id`
inner join `photos` on `portid` = `portfolios`.`id`
where `categories`.`name` = "Poster"
GROUP BY portfolios.id
答
这将是一个容易得多*:你可以试试这个
select p.id, p.catid, c.name, p.type, p.title
, p.text, p.date. group_concat(ph.image order by case when ph.previw = 1
then 0 else 1
end) as images
from `portfolios` p
join `categories` c
on p.`catid` = c.`id`
join `photos` ph
on ph.`portid` = p.`id`
where c.`name` = "Poster"
group by p.id, p.catid, c.name, p.type, p.title
, p.text, p.date
这被加上PHP,所以没有必要使用GROUP_CONCAT在所有。作为一般规则,当您有可用的应用程序级代码时,我认为最好是在那里处理串联 - 除非您还在执行其他聚合操作。所以只需返回一个有序数组,然后在PHP中完成其余部分。它更灵活,尺度更好。 – Strawberry 2014-09-01 11:40:16