mysql - 子查询和联合

Question

我有下一个 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

我想创建一个 View 来填充 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

结果是正确的，但我想知道我是否正确使用了“子查询”和“联合”，或者是否有更好的方法通过优化查询来生成相同的结果？

谢谢!

Answer 1

你可以使用 JOIN

看看:http://dev.mysql.com/doc/refman/4.1/en/join.html