代码之家  ›  专栏  ›  技术社区  ›  Petah

快速复杂查询以选择预订

  •  2
  • Petah  · 技术社区  · 14 年前

    我正试图编写一个查询来获取课程信息以及预订和与会者的数量。每个课程可以有许多预订,每个预订可以有许多与会者。

    我们已经有了一个工作报告,但是它使用多个查询来获取所需的信息。一个是获得课程,一个是获得预订,还有一个是获得与会者数量。由于数据库已经增长到了一定的大小,这个过程非常缓慢。

    报告有许多附加条件:

    • 预订必须超过5个 分钟前,或已确认
    • 不能取消预订
    • 不得将课程标记为已删除
    • 课程地点和地点必须 LIKE 搜索字符串
    • 没有预订的课程必须出现在结果中

    这是表结构:(我省略了不需要的信息。所有字段不为空,没有默认值)

    mysql>描述急救课程;

    +------------------+--------------+-----+----------------+
    | Field            | Type         | Key | Extra          |
    +------------------+--------------+-----+----------------+
    | id               | int(11)      | PRI | auto_increment |
    | course_date      | date         |     |                |
    | region_id        | int(11)      |     |                |
    | location         | varchar(255) |     |                |
    | venue            | varchar(255) |     |                |
    | number_of_spaces | int(11)      |     |                |
    | deleted          | tinyint(1)   |     |                |
    +------------------+--------------+-----+----------------+
    

    mysql>描述急救预订;

    +-----------------------+--------------+-----+----------------+
    | Field                 | Type         | Key | Extra          |
    +-----------------------+--------------+-----+----------------+
    | id                    | int(11)      | PRI | auto_increment |
    | first_aid_course_id   | int(11)      |     |                |
    | placed                | datetime     |     |                |
    | confirmed             | tinyint(1)   |     |                |
    | cancelled             | tinyint(1)   |     |                |
    +-----------------------+--------------+-----+----------------+
    

    mysql>描述急救人员;

    +----------------------+--------------+-----+----------------+
    | Field                | Type         | Key | Extra          |
    +----------------------+--------------+-----+----------------+
    | id                   | int(11)      | PRI | auto_increment |
    | first_aid_booking_id | int(11)      |     |                |
    +----------------------+--------------+-----+----------------+
    

    mysql>描述区域;

    +----------+--------------+-----+----------------+
    | Field    | Type         | Key | Extra          |
    +----------+--------------+-----+----------------+
    | id       | int(11)      | PRI | auto_increment |
    | name     | varchar(255) |     |                |
    +----------+--------------+-----+----------------+
    

    我需要选择以下选项:

    Course ID:        first_aid_courses.id
    Date:             first_aid_courses.course_date
    Region            regions.name
    Location:         first_aid_courses.location
    Bookings:         COUNT(first_aid_bookings)
    Attendees:        COUNT(first_aid_attendees)
    Spaces Remaining: COUNT(first_aid_bookings) - COUNT(first_aid_attendees)
    

    这就是我目前为止所拥有的:

    SELECT `first_aid_courses`.*,
           COUNT(`first_aid_bookings`.`id`)  AS `bookings`,
           COUNT(`first_aid_attendees`.`id`) AS `attendees`
    FROM   `first_aid_courses`
           LEFT JOIN `first_aid_bookings`
             ON `first_aid_courses`.`id` =
                `first_aid_bookings`.`first_aid_course_id`
           LEFT JOIN `first_aid_attendees`
             ON `first_aid_bookings`.`id` =
                `first_aid_attendees`.`first_aid_booking_id`
    WHERE  ( `first_aid_courses`.`location` LIKE '%$search_string%'
              OR `first_aid_courses`.`venue` LIKE '%$search_string%' )
           AND `first_aid_courses`.`deleted` = 0
           AND ( `first_aid_bookings`.`placed` > '$five_minutes_ago'
                 AND `first_aid_bookings`.`cancelled` = 0
                  OR `first_aid_bookings`.`confirmed` = 1 )
    GROUP  BY `first_aid_courses`.`id`
    ORDER  BY `course_date` DESC  
    

    它不太管用,有人能帮我写正确的查询吗?此外,该数据库中还有1000行,因此对于加快速度的任何帮助都是值得的(比如索引哪些字段)。

    3 回复  |  直到 12 年前
        1
  •  2
  •   Petah    14 年前

    SELECT `first_aid_courses`.*,
           `regions`.`name`                          AS `region_name`,
           COUNT(DISTINCT `first_aid_bookings`.`id`) AS `bookings`,
           COUNT(`first_aid_attendees`.`id`)         AS `attendees`
    FROM   `first_aid_courses`
           JOIN `regions`
             ON `first_aid_courses`.`region_id` = `regions`.`id`
           LEFT JOIN `first_aid_bookings`
             ON `first_aid_courses`.`id` =
                `first_aid_bookings`.`first_aid_course_id`
           LEFT JOIN `first_aid_attendees`
             ON `first_aid_bookings`.`id` =
                `first_aid_attendees`.`first_aid_booking_id`
    WHERE  ( `first_aid_courses`.`location` LIKE '%$search_string%'
              OR `first_aid_courses`.`venue` LIKE '%$search_string%' )
           AND `first_aid_courses`.`deleted` = 0
           AND ( `first_aid_bookings`.`cancelled` = 0
                 AND `first_aid_bookings`.`confirmed` = 1 )
    GROUP  BY `first_aid_courses`.`id`
    ORDER  BY `course_date` ASC  
    
        2
  •  1
  •   Ben Lee    14 年前

    SUM(IF(`first_aid_bookings`.`id` IS NOT NULL, 1, 0))  AS `bookings`,
    COUNT(IF(`first_aid_attendees`.`id` IS NOT NULL, 1, 0)) AS `attendees`
    
        3
  •  1
  •   David Mårtensson    14 年前