我有下一个mysql表
CREATE TABLE IF NOT EXISTS `my_app`.`hotel` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`destination_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_name` (`name` ASC),
INDEX `fk_hotel_destination1_idx` (`destination_id` ASC),
CONSTRAINT `fk_hotel_destination1`
FOREIGN KEY (`destination_id`)
REFERENCES `my_app`.`destination` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `my_app`.`hotel_alias` (
`id` INT NOT NULL AUTO_INCREMENT,
`hotel_id` INT NOT NULL,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_name` (`name` ASC),
INDEX `fk_hotel_alias_hotel_idx` (`hotel_id` ASC),
CONSTRAINT `fk_hotel_alias_hotel`
FOREIGN KEY (`hotel_id`)
REFERENCES `my_app`.`hotel` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `my_app`.`destination` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_name` (`name` ASC))
ENGINE = InnoDB
CREATE TABLE IF NOT EXISTS `my_app`.`place` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`destination_id` INT NOT NULL,
PRIMARY KEY (`id`),
INDEX `idx_name` (`name` ASC),
INDEX `fk_place_destination1_idx` (`destination_id` ASC),
CONSTRAINT `fk_place_destination1`
FOREIGN KEY (`destination_id`)
REFERENCES `my_app`.`destination` (`id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
我想创建一个视图来填充jquery自动完成,所以我做了下一个查询
SELECT name, 'hotel' AS type, (SELECT id FROM destination WHERE hotel.destination_id = destination.id) AS destination_id FROM hotel
UNION
SELECT name, 'place' AS type, (SELECT id FROM destination WHERE place.destination_id = destination.id) AS destination_id FROM place
UNION
SELECT name, 'alias' AS type, (SELECT destination.id FROM destination,hotel WHERE hotel_alias.hotel_id = hotel.id AND hotel.destination_id = destination.id) AS destination_id FROM hotel_alias
返回以下结果
|name |type |destination_id
---------------------------------------------------
|hotel casa maya |hotel |1
|sandos caracol |hotel |2
|cabañas tulum |hotel |3
|sandos luxury |hotel |1
|ocean spa |hotel |1
|sandos playacar |hotel |2
|walmart |place |1
|walmart |place |2
|centro |place |3
|campo de golf pok-ta-pok |place |1
|sandos beach scape |alias |2
|sunset spa |alias |1
结果是正确的,但我想知道我是否正确使用“子查询”和“联合”,或者是否有更好的方法通过优化查询生成相同的结果?
格拉西亚斯!
3 回答
你可以使用JOIN
看看:http://dev.mysql.com/doc/refman/4.1/en/join.html
您可以通过先执行联合,然后内部连接到目标表来确保目标存在且有效,从而获得所需的结果而无需借助多个从属子查询 .
没有依赖子查询,
union all
以避免不同的清除,与查询结果相同 . demo here .由于您希望将其用作视图,因此可以将查询扩展为:
或者要使用原始查询,将其拆分为两个视图
然后
然后
select * from YOUR_VIEW
会给你想要的结果 .
updated fiddle显示以上所有选项 .
无论你为获得结果做了什么都是正确的 . 没有任何其他方法可以生成相同的结果 .