数据库架构
create table `questions` (
`id` int not null auto_increment,
`title` varchar(45) not null,
primary key (`id`));
create table `tags` (
`id` int not null auto_increment,
`question_id` int not null,
`name` varchar(45) not null,
primary key (`id`));
create table `comments` (
`id` int not null auto_increment,
`question_id` int not null,
`body` varchar(45) not null,
primary key (`id`));
insert into questions (title) values
("title1"), ("title2"), ("title3");
insert into tags (question_id, name) values
(1, "javascript"), (1, "php"), (1, "c#"), (2, "mysql"), (2, "php"), (3, "c#");
insert into comments (question_id, body) values
(1, "comment1"), (1, "comment1"), (1, "comment2"), (3, "comment3");
这就是它的视觉效果:
问题表
| id | title |
|----|--------|
| 1 | title1 |
| 2 | title2 |
| 3 | title3 |
标签表
| id | question_id | name |
|----|-------------|------------|
| 1 | 1 | javascript |
| 2 | 1 | php |
| 3 | 1 | c# |
| 4 | 2 | mysql |
| 5 | 2 | php |
| 6 | 3 | c# |
评论表
| id | question_id | body |
|----|-------------|----------|
| 1 | 1 | comment1 |
| 2 | 1 | comment1 |
| 3 | 1 | comment2 |
| 4 | 3 | comment3 |
每个问题必须至少有一个标签.它还可以有0个或更多注释.在一个问题上,同一个机构可以有两条评论.
期望的输出
我想选择所有问题,即他们的ID,标题,标签和评论.
输出应该如下所示:
| id | title | tags | comments |
|----|--------|-------------------|----------------------------|
| 1 | title1 | c#,php,javascript | comment1,comment1,comment2 |
| 2 | title2 | php,mysql | (null) |
| 3 | title3 | c# | comment3 |
试图解决问题
我尝试了以下查询:
select questions.id, questions.title,
group_concat(tags.name), group_concat(comments.body)
from questions
join tags on questions.id = tags.question_id
left join comments on questions.id = comments.question_id
group by questions.id
不幸的是,它没有按预期工作.它产生以下输出:
| id | title | group_concat(distinct tags.name) | group_concat(comments.body) |
|----|--------|----------------------------------|----------------------------------------------------------------------------------|
| 1 | title1 | c#,php,javascript | comment1,comment1,comment1,comment2,comment2,comment2,comment1,comment1,comment1 |
| 2 | title2 | php,mysql | (null) |
| 3 | title3 | c# | comment3 |
如您所见,对于第一个问题,我每次都会收到三次评论,因为此问题上有三个标记.
此外,评论的顺序错误.它们应该与插入的顺序相同,即comment1,comment1,comment2,而不是comment1,comment2,comment1.
我不能使用distinct作为评论,因为在一个问题上可以有相同正文的多个评论.
我知道这可能可以通过嵌套选择来解决,但据我所知,它会对查询的性能产生巨大的负面影响.
SQL小提琴
The SQL Fiddle与数据库架构和我的查询. 解决方法: 您需要先聚合并应用GROUP_CONCAT然后加入:
select questions.id, questions.title,
tags.name, comments.body
from questions
join (
select question_id, group_concat(tags.name) as name
from tags
group by question_id
) tags on questions.id = tags.question_id
left join (
select question_id, group_concat(comments.body) as body
from comments
group by question_id
) comments on questions.id = comments.question_id
来源:http://www./content-2-209801.html
|