我有多个表需要连接一组结果。总共有6个表,每个表都有一个与初始表中的primary_key相匹配的索引。
但是,当我执行查询时,它将导致为每个表返回每个结果的多个实例。如果我只选择一条记录,则输出很好。似乎该查询正在每个表中运行多次,并且所有结果都被串联在一起。
SELECT id,
GROUP_CONCAT(tb1.table1) AS tbl1,
GROUP_CONCAT(tb2.table2) AS tbl2,
GROUP_CONCAT(tb3.table3) AS tbl3,
GROUP_CONCAT(tb4.table4) AS tbl4,
GROUP_CONCAT(tb5.table5 )AS tbl5
FROM table t
LEFT OUTER JOIN (
SELECT id, field2
FROM table1
GROUP BY id
) tb1
ON tb1.id = t.id
LEFT OUTER JOIN (
SELECT id, field2
FROM table2
GROUP BY id
) tb2
ON tb2.id = t.id
LEFT OUTER JOIN (
SELECT id, field2
FROM table3
GROUP BY id
) tb3
ON tb3.id = t.id
LEFT OUTER JOIN (
SELECT id, field2
FROM table4
GROUP BY id
) tb4
ON tb4.id = t.id
LEFT OUTER JOIN (
SELECT id, field2
FROM table5
GROUP BY id
) tb5
ON tb5.id = t.id
GROUP BY t.id
我期望/想要结果。
Primary Key | Field 1 | Field 2 | Field 3 | Field 4 | Field 5
1 | 1, 2 | 2, 3 | 2, 4 | 1, 5 | NUll
但是返回的结果是,这仅是未指定记录ID时1行的示例,但是所有行都显示如下。
Primary Key | Field 1 | Field 2 | Field 3 | Field 4 | Field 5
1 | 1,2 | 2,3, | 2,4 | 1,5, | NUll
2 | 1,2,1, | 2,3,2, | 2,4,2, | 1,5,1, | 1
2 | 1,2,1,2 | 2,3,2,3 | 2,4,2,4 | 1,5,1,5 | 1, 2
我不知所措,查询在何处或为什么要对其自身进行多次遍历,然后对每个表进行遍历。
任何帮助将不胜感激。
问候,
编辑:我已经更新了我最初的期望结果,并添加了更多行以将表重新带回。
还包括一个4表模式:CREATE TABLE IF NOT EXISTS table
(id
int(11)NOT NULL AUTO_INCREMENT,Name
varchar(255)DEFAULT NULL,PRIMARY KEY(id
));
CREATE TABLE IF NOT EXISTS `table2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`table1id` int(11) NOT NULL,
`Name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX (`table1id`)
);
CREATE TABLE IF NOT EXISTS `table3` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`table1id` int(11) NOT NULL,
`Name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX (`table1id`)
);
CREATE TABLE IF NOT EXISTS `table4` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`table1id` int(11) NOT NULL,
`Name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`),
INDEX (`table1id`)
);
INSERT INTO `table`(`id`, `Name`) VALUES (1, 'Test'),(2, 'Second Test'),(3,'Third Test'),(4, 'Test Four'),(5,'Test Five');
INSERT INTO `table2` (`id`,`table1id`, `Name`) VALUES (1,1,'Test Value'), (2,2, 'Test Value 2'), (3,3, 'Test Value 3');
INSERT INTO `table3` (`id`,`table1id`, `Name`) VALUES (1,2,'Value'), (2,3, 'Value 2'), (3,4, 'Value 3');
INSERT INTO `table4` (`id`,`table1id`, `Name`) VALUES (1,1,'Test'), (2,2, 'Test 2'), (3,5, 'Test 3');
使用DISTINCT
中的所有列GROUP_CONCAT()
试试这个:
SELECT t.id,
GROUP_CONCAT(DISTINCT tb1.field2) AS tbl1,
GROUP_CONCAT(DISTINCT tb2.field2) AS tbl2,
GROUP_CONCAT(DISTINCT tb3.field2) AS tbl3,
GROUP_CONCAT(DISTINCT tb4.field2) AS tbl4,
GROUP_CONCAT(DISTINCT tb5.field2) AS tbl5
FROM TABLE t
LEFT OUTER JOIN table1 tb1 ON tb1.id = t.id
LEFT OUTER JOIN table2 tb2 ON tb2.id = t.id
LEFT OUTER JOIN table3 tb3 ON tb3.id = t.id
LEFT OUTER JOIN table4 tb4 ON tb4.id = t.id
LEFT OUTER JOIN table5 tb5 ON tb5.id = t.id
GROUP BY t.id
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句