我有三个表供我选择,其中两个表的列名为"id“。它是language.id和route.id
如果我运行这个查询
SELECT *
FROM route
LEFT JOIN route_has_languages
ON route.id = route_has_languages.route_id
LEFT JOIN language
ON language.id = route_has_languages.languages_id
WHERE route_has_languages.languages_id = 1它运行得很好,它减少了language.id,这是我想要的,但是我想要使用它作为内部选择,但是当我这样做的时候
SELECT * FROM (
SELECT *
FROM route
LEFT JOIN route_has_languages
ON route.id = route_has_languages.route_id
LEFT JOIN language
ON language.id = route_has_languages.languages_id
WHERE route_has_languages.languages_id = 1
) T1它以Error Code: 1060. Duplicate column name 'id'结尾
我尝试过重命名它,此查询运行良好,如预期一样显示noneed_id列。
SELECT *, language.id as noneed_id
FROM route
LEFT JOIN route_has_languages
ON route.id = route_has_languages.route_id
LEFT JOIN language
ON language.id = route_has_languages.languages_id
WHERE route_has_languages.languages_id = 1但是,再次使用它作为内部选择结束具有相同的错误。
SELECT * FROM (
SELECT *, language.id as noneed_id
FROM route
LEFT JOIN route_has_languages
ON route.id = route_has_languages.route_id
LEFT JOIN language
ON language.id = route_has_languages.languages_id
WHERE route_has_languages.languages_id = 1
) T1我不明白这种行为,有人能指点我吗?
PS :我知道我可以这样做,但我不想说出所有的表/列。
SELECT * FROM (
SELECT route.*, route_has_languages.*, language.short_name, language.long_name
FROM route
LEFT JOIN route_has_languages
ON route.id = route_has_languages.route_id
LEFT JOIN language
ON language.id = route_has_languages.languages_id
WHERE route_has_languages.languages_id = 1
) T1如果有人想尝试,你可以运行这个
CREATE TABLE IF NOT EXISTS `language` (
`id` INT NOT NULL AUTO_INCREMENT,
`short_name` VARCHAR(7) NOT NULL,
`long_name` VARCHAR(15) NOT NULL,
`name` VARCHAR(63) NOT NULL,
`flag` TEXT NULL,
`language_order` INT NOT NULL,
PRIMARY KEY (`id`),
UNIQUE INDEX `short_name_UNIQUE` (`short_name` ASC),
UNIQUE INDEX `long_name_UNIQUE` (`long_name` ASC))
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `route` (
`id` INT NOT NULL AUTO_INCREMENT,
`length` DOUBLE NOT NULL,
`estimated_time` INT NOT NULL,
`accessibility` TINYINT(1) NOT NULL,
`version` INT NOT NULL,
`north_east_point_lat` DOUBLE NOT NULL,
`north_east_point_lng` DOUBLE NOT NULL,
`south_west_point_lat` DOUBLE NOT NULL,
`south_west_point_lng` DOUBLE NOT NULL,
`available` TINYINT(1) NOT NULL DEFAULT 1,
`store_id` VARCHAR(255) NULL,
`color` VARCHAR(63) NULL,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB;
CREATE TABLE IF NOT EXISTS `route_has_languages` (
`route_id` INT NOT NULL,
`languages_id` INT NOT NULL,
`description` TEXT NOT NULL,
`voice` VARCHAR(63) NOT NULL,
`info_at_poi` TEXT NOT NULL,
PRIMARY KEY (`route_id`, `languages_id`),
INDEX `fk_route_has_languages_languages1_idx` (`languages_id` ASC),
INDEX `fk_route_has_languages_route1_idx` (`route_id` ASC),
CONSTRAINT `fk_route_has_languages_route1`
FOREIGN KEY (`route_id`)
REFERENCES `audioguide`.`route` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_route_has_languages_languages1`
FOREIGN KEY (`languages_id`)
REFERENCES `audioguide`.`language` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;发布于 2015-10-07 09:52:13
只要在两个*语句中使用SELECT,就会出现此错误。
将其重命名为:, language.id as noneed_id只将添加一个列,但不会删除id列。
解决方案可以是重命名数据库中的字段。例如,route.id到route_id等。
发布于 2015-10-07 09:59:12
让我们检查问题-
你的第一个问题-
SELECT *
FROM route
LEFT JOIN route_has_languages
ON route.id = route_has_languages.route_id
LEFT JOIN language
ON language.id = route_has_languages.languages_id
WHERE route_has_languages.languages_id = 1在这里,mysql将遵循字段顺序,按表顺序表示第一个表所有字段,然后是第二个表,所有字段等等,如下所示-
在您的第二个表中,您使用的是子查询方式,mysql将处理相同的和povide结果,如下所示-
id (first table), col12,col13,col14..., id(2nd table),col22,col23,col24..., other tables columns.....意味着你的结果是-
id,col11,col12,col13,...,id,col21,col22,col23,....现在,您的外部查询将从这里获取结果,并获得2次id列并抛出错误。
溶液
要么您必须提到子查询中的所有列,并且需要为公共字段保留不同的别名,要么需要在一个表中更改列名。
https://stackoverflow.com/questions/32988909
复制相似问题