mysql - mysql中的内连接中的子选择仅得到一个结果

Question

嗨，我遇到了困境，我正在执行此查询:

SELECT GROUP_CONCAT(DISTINCT(ca.category_name) SEPARATOR ', ') AS categories, pr.promo_name, pr.add_value_text, c.contract_id, c.cmeal_plan, c.cmin_markup, c.civa, c.tax_include, c.hotel_id, hi.hname, hi.hstars, im.image_file, pl.plan_name, ra.price
FROM contracts AS c
INNER JOIN hotel_info AS hi ON hi.hotel_id = c.hotel_id AND hi.destination_id = '6460'
INNER JOIN images AS im ON im.foreign_id = hi.hotel_id
INNER JOIN meal_plan AS pl ON pl.plan_code = c.cmeal_plan AND pl.lang = '1'
INNER JOIN hotel_categories AS hc ON hc.hotel_id = hi.hotel_id
INNER JOIN categories AS ca ON ca.category_code = hc.category_code AND ca.lang = '1'
LEFT JOIN 
                    (SELECT 
                              r.hotel_id, AVG(r.double) AS price
                    FROM
                              rates AS r ) AS ra
                    ON ra.hotel_id = hi.hotel_id
LEFT JOIN promotions AS pr ON pr.hotel_id = hi.hotel_id AND FIND_IN_SET(c.contract_id, pr.contract_id) > 0 AND pr.book_start <= '2012-11-01' AND pr.book_end >= '2012-11-02' AND travel_start <= '2012-11-23' AND travel_end >=  '2012-11-30' AND pr.lang = '1'
WHERE c.cstart <= '2012-11-01' AND c.cend >= '2012-11-01'
AND hi.status = '1'
AND im.type ='1'
GROUP BY hi.hotel_id

除了子选择查询之外，我得到了所有想要的结果。每家酒店都有一个价格，但它只给我返回一个结果，其余的都是空的。我的查询有错误吗？如果需要任何其他信息，请告诉我，并提前感谢您的帮助!

Answer 1

您的子查询中缺少GROUP BY，因此 MySQL 将仅返回一个值。如果您想要所有 hotel_id，那么您需要对该字段进行GROUP BY:

SELECT GROUP_CONCAT(DISTINCT(ca.category_name) SEPARATOR ', ') AS categories, 
    pr.promo_name, 
    pr.add_value_text, 
    c.contract_id, 
    c.cmeal_plan, 
    c.cmin_markup, 
    c.civa, 
    c.tax_include, 
    c.hotel_id, 
    hi.hname, 
    hi.hstars, 
    im.image_file, 
    pl.plan_name, 
    ra.price
FROM contracts AS c
INNER JOIN hotel_info AS hi 
    ON hi.hotel_id = c.hotel_id 
    AND hi.destination_id = '6460'
INNER JOIN images AS im 
    ON im.foreign_id = hi.hotel_id
INNER JOIN meal_plan AS pl 
    ON pl.plan_code = c.cmeal_plan 
    AND pl.lang = '1'
INNER JOIN hotel_categories AS hc 
    ON hc.hotel_id = hi.hotel_id
INNER JOIN categories AS ca 
    ON ca.category_code = hc.category_code 
    AND ca.lang = '1'
LEFT JOIN 
(
    SELECT r.hotel_id, AVG(r.double) AS price
    FROM rates AS r 
    GROUP BY r.hotel_id  <-- add a GROUP BY hotel_id then you will get avg() for each hotel
) AS ra
    ON ra.hotel_id = hi.hotel_id
LEFT JOIN promotions AS pr 
    ON pr.hotel_id = hi.hotel_id 
    AND FIND_IN_SET(c.contract_id, pr.contract_id) > 0 
    AND pr.book_start <= '2012-11-01' 
    AND pr.book_end >= '2012-11-02' 
    AND travel_start <= '2012-11-23' 
    AND travel_end >=  '2012-11-30' 
    AND pr.lang = '1'
WHERE c.cstart <= '2012-11-01' 
    AND c.cend >= '2012-11-01'
    AND hi.status = '1'
    AND im.type ='1'
GROUP BY hi.hotel_id