MySQL 在行 SUM 之前选择连接和列乘法

Question

我有很多表，从我正在做选择的地方，但我只会写重要的部分。我有 2 张 table

住宿:

id | title | desc | etc..

住宿房间:

id | accomodation_id | beds | rooms

然后我一直在选择哪个效果好，直到我想过滤，有多少床(床*房间)有住宿。如果我尝试使用乘法从表 accomodation_rooms 中进行简单选择，效果很好。所以问题应该出在我加入其他表的地方。

这是我的选择:

SELECT 
  accomodation.*, 
  db_cities.title_en AS city, 
  db_cities.title_url AS city_url, 
  db_countries.title_url_en AS country_url, 
  SUM(accomodation_rooms.beds * accomodation_rooms.rooms) AS total_persons 
FROM 
  (SELECT id, aid, title_en, title_url_en, address, city_id, zip, district_id, province_id, region_id, country_id, mountain_id, stars, latitude, longitude, picture, valid_from, valid_to 
  FROM accomodation 
  ORDER BY info_date_add DESC) 
    AS accomodation 
LEFT JOIN db_cities 
  ON db_cities.id = accomodation.city_id 
JOIN db_countries 
  ON db_countries.id = accomodation.country_id 
JOIN skiresort_locations 
  ON 
    (((acos(sin((skiresort_locations.latitude*pi()/180)) * 
    sin((accomodation.latitude*pi()/180)) + 
    cos((skiresort_locations.latitude*pi()/180)) * 
    cos((accomodation.latitude*pi()/180)) * 
    cos(((skiresort_locations.longitude - 
    accomodation.longitude)*
    pi()/180))))*180/pi())*60*1.1515*1.609344) 
      < '50' 
JOIN accomodation_rooms 
  ON accomodation_rooms.accomodation_id = accomodation.id 
WHERE 
  db_countries.title_url_en LIKE '%country_title%' AND 
  accomodation.region_id = '8' 
GROUP BY 
  accomodation.aid 
HAVING 
  total_persons >= '1' 
ORDER BY CASE 
  WHEN 
    accomodation.valid_to>=NOW() AND 
    accomodation.valid_from<=NOW() 
      THEN 0 
  WHEN 
    NOW()>accomodation.valid_to AND 
    accomodation.valid_to!='0000-00-00' 
      THEN 1 
  ELSE 2 
    END, 
  accomodation.title_en 
LIMIT 10 
OFFSET 0

total_persons 应返回 13但它返回 624，但我真的不明白为什么？

Answer 1

认为您想要的数量可能最好通过使用子选择来获得。

我假设 accomodation_rooms 是一个表，它存储某些住宿的多行，说明该位置有多少张床(即 1 x 5 床房间、2 x 3 床房间、5 x 2 床房间)。

SELECT 
  accomodation.*, 
  db_cities.title_en AS city, 
  db_cities.title_url AS city_url, 
  db_countries.title_url_en AS country_url, 
  accomodation_rooms.total_persons 
FROM 
  (SELECT id, aid, title_en, title_url_en, address, city_id, zip, district_id, province_id, region_id, country_id, mountain_id, stars, latitude, longitude, picture, valid_from, valid_to 
  FROM accomodation 
  ORDER BY info_date_add DESC) 
    AS accomodation 
LEFT JOIN db_cities 
  ON db_cities.id = accomodation.city_id 
JOIN db_countries 
  ON db_countries.id = accomodation.country_id 
JOIN skiresort_locations 
  ON 
    (((acos(sin((skiresort_locations.latitude*pi()/180)) * 
    sin((accomodation.latitude*pi()/180)) + 
    cos((skiresort_locations.latitude*pi()/180)) * 
    cos((accomodation.latitude*pi()/180)) * 
    cos(((skiresort_locations.longitude - 
    accomodation.longitude)*
    pi()/180))))*180/pi())*60*1.1515*1.609344) 
      < '50' 
INNER JOIN (SELECT accomodation_id, SUM(accomodation_rooms.beds * accomodation_rooms.rooms) AS total_persons FROM accomodation_rooms GROUP BY accomodation_id) accomodation_rooms
  ON accomodation_rooms.accomodation_id = accomodation.id 
  AND accomodation_rooms.total_persons >= 1
WHERE db_countries.title_url_en LIKE '%country_title%' 
AND accomodation.region_id = '8' 
ORDER BY CASE 
  WHEN accomodation.valid_to>=NOW() AND accomodation.valid_from<=NOW() 
      THEN 0 
  WHEN NOW()>accomodation.valid_to AND accomodation.valid_to!='0000-00-00' 
      THEN 1 
  ELSE 2 
    END, 
  accomodation.title_en 
LIMIT 10 
OFFSET 0

作为一个小问题，不确定您是否需要原始的住宿选择作为子选择(或有一个 order by 子句，但您似乎没有限制，所以我认为它不会有任何效果):-

SELECT 
  accomodation.id, 
  accomodation.aid, 
  accomodation.title_en, 
  accomodation.title_url_en, 
  accomodation.address, 
  accomodation.city_id, 
  accomodation.zip, 
  accomodation.district_id, 
  accomodation.province_id, 
  accomodation.region_id, 
  accomodation.country_id, 
  accomodation.mountain_id, 
  accomodation.stars, 
  accomodation.latitude, 
  accomodation.longitude, 
  accomodation.picture, 
  accomodation.valid_from, 
  accomodation.valid_to, 
  db_cities.title_en AS city, 
  db_cities.title_url AS city_url, 
  db_countries.title_url_en AS country_url, 
  accomodation_rooms.total_persons 
FROM accomodation
LEFT JOIN db_cities 
  ON db_cities.id = accomodation.city_id 
JOIN db_countries 
  ON db_countries.id = accomodation.country_id 
JOIN skiresort_locations 
  ON 
    (((acos(sin((skiresort_locations.latitude*pi()/180)) * 
    sin((accomodation.latitude*pi()/180)) + 
    cos((skiresort_locations.latitude*pi()/180)) * 
    cos((accomodation.latitude*pi()/180)) * 
    cos(((skiresort_locations.longitude - 
    accomodation.longitude)*
    pi()/180))))*180/pi())*60*1.1515*1.609344) 
      < '50' 
INNER JOIN (SELECT accomodation_id, SUM(accomodation_rooms.beds * accomodation_rooms.rooms) AS total_persons FROM accomodation_rooms GROUP BY accomodation_id) accomodation_rooms
  ON accomodation_rooms.accomodation_id = accomodation.id 
  AND accomodation_rooms.total_persons >= 1
WHERE db_countries.title_url_en LIKE '%country_title%' 
AND accomodation.region_id = '8' 
ORDER BY CASE 
  WHEN accomodation.valid_to>=NOW() AND accomodation.valid_from<=NOW() 
      THEN 0 
  WHEN NOW()>accomodation.valid_to AND accomodation.valid_to!='0000-00-00' 
      THEN 1 
  ELSE 2 
    END, 
  accomodation.title_en 
LIMIT 10 OFFSET 0

编辑 - 修改为获取每个援助的最新住宿记录并将其连接回来以获得最新住宿记录的其余部分。

SELECT 
  accomodation.*, 
  db_cities.title_en AS city, 
  db_cities.title_url AS city_url, 
  db_countries.title_url_en AS country_url, 
  accomodation_rooms.total_persons 
FROM (SELECT aid, MAX(info_date_add) AS max_info_date_add FROM accomodation GROUP BY aid) accomodation_max
INNER JOIN accomodation ON accomodation_max.aid = accomodation.aid AND accomodation_max.max_info_date_add = accomodation.info_date_add
LEFT JOIN db_cities ON db_cities.id = accomodation.city_id 
JOIN db_countries ON db_countries.id = accomodation.country_id 
JOIN skiresort_locations 
  ON 
    (((acos(sin((skiresort_locations.latitude*pi()/180)) * 
    sin((accomodation.latitude*pi()/180)) + 
    cos((skiresort_locations.latitude*pi()/180)) * 
    cos((accomodation.latitude*pi()/180)) * 
    cos(((skiresort_locations.longitude - 
    accomodation.longitude)*
    pi()/180))))*180/pi())*60*1.1515*1.609344) 
      < '50' 
INNER JOIN (SELECT accomodation_id, SUM(accomodation_rooms.beds * accomodation_rooms.rooms) AS total_persons FROM accomodation_rooms GROUP BY accomodation_id) accomodation_rooms
ON accomodation_rooms.accomodation_id = accomodation.id AND accomodation_rooms.total_persons >= 1
WHERE db_countries.title_url_en LIKE '%country_title%' 
AND accomodation.region_id = '8' 
ORDER BY CASE 
  WHEN accomodation.valid_to>=NOW() AND accomodation.valid_from<=NOW() 
      THEN 0 
  WHEN NOW()>accomodation.valid_to AND accomodation.valid_to!='0000-00-00' 
      THEN 1 
  ELSE 2 
    END, 
  accomodation.title_en 
LIMIT 10 
OFFSET 0

编辑 - 使用 GROUP BY 子句添加 MIN 距离。但不确定这是否会比使用 DISTINCT 快得多。它强制对 JOIN 进行大量计算(即，如果您有 100 条住宿记录和 100 条 Skiresort_locations 记录，那么这将导致 10000 次合理的复杂计算来确定距离。如果您可以在那么这将节省相当多的时间(例如，稍微非规范化，但也许您有一个区域表连接到region_id上的住宿，并且它可能包含该区域的最小和最大纬度和经度，您可以使用它对于连接，然后将复杂的计算放入 WHERE 子句中)。此外，您还有一个用于 db_counties.title_url_en 的 LIKE 子句，该子句具有前导通配符，这会很慢，因为它不会使用索引(尽管它应该是在连接上使用country_id索引)。

SELECT 
  accomodation.*, 
  db_cities.title_en AS city, 
  db_cities.title_url AS city_url, 
  db_countries.title_url_en AS country_url, 
  accomodation_rooms.total_persons,
  MIN(((acos(sin((skiresort_locations.latitude*pi()/180)) * 
    sin((accomodation.latitude*pi()/180)) + 
    cos((skiresort_locations.latitude*pi()/180)) * 
    cos((accomodation.latitude*pi()/180)) * 
    cos(((skiresort_locations.longitude - 
    accomodation.longitude)*
    pi()/180))))*180/pi())*60*1.1515*1.609344)
FROM (SELECT aid, MAX(info_date_add) AS max_info_date_add FROM accomodation GROUP BY aid) accomodation_max
INNER JOIN accomodation ON accomodation_max.aid = accomodation.aid AND accomodation_max.max_info_date_add = accomodation.info_date_add
LEFT JOIN db_cities ON db_cities.id = accomodation.city_id 
JOIN db_countries ON db_countries.id = accomodation.country_id 
JOIN skiresort_locations 
  ON 
    (((acos(sin((skiresort_locations.latitude*pi()/180)) * 
    sin((accomodation.latitude*pi()/180)) + 
    cos((skiresort_locations.latitude*pi()/180)) * 
    cos((accomodation.latitude*pi()/180)) * 
    cos(((skiresort_locations.longitude - 
    accomodation.longitude)*
    pi()/180))))*180/pi())*60*1.1515*1.609344) 
      < '50' 
INNER JOIN (SELECT accomodation_id, SUM(accomodation_rooms.beds * accomodation_rooms.rooms) AS total_persons FROM accomodation_rooms GROUP BY accomodation_id) accomodation_rooms
ON accomodation_rooms.accomodation_id = accomodation.id AND accomodation_rooms.total_persons >= 1
WHERE db_countries.title_url_en LIKE '%country_title%' 
AND accomodation.region_id = '8' 
GROUP BY accomodation.id, 
  db_cities.title_en, 
  db_cities.title_url, 
  db_countries.title_url, 
  accomodation_rooms.total_persons
ORDER BY CASE 
  WHEN accomodation.valid_to>=NOW() AND accomodation.valid_from<=NOW() 
      THEN 0 
  WHEN NOW()>accomodation.valid_to AND accomodation.valid_to!='0000-00-00' 
      THEN 1 
  ELSE 2 
    END, 
  accomodation.title_en 
LIMIT 10 
OFFSET 0