php - MySql - 加入另一个多行表，将查询插入另一个查询？

Question

我绞尽脑汁想了几个小时想弄清楚如何连接这两个查询..

我的目标是根据特定条件返回多个 field 行(来自 field )......这就是我当前查询所做的......

SELECT venues.id AS ven_id, 
   venues.venue_name, 
   venues.sub_category_id, 
   venues.score, 
   venues.lat, 
   venues.lng, 
   venues.short_description, 
   sub_categories.id, 
   sub_categories.sub_cat_name, 
   sub_categories.category_id, 
   categories.id, 
   categories.category_name, 
   ((ACOS(  SIN(51.44*PI()/180)*SIN(lat*PI()/180) + COS(51.44*PI()/180)*COS(lat*PI()/180)*COS((-2.60796 - lng)*PI()/180)) * 180/PI())*60 * 1.1515) AS dist

FROM venues, 
  sub_categories, 
  categories 
WHERE 
  venues.sub_category_id = sub_categories.id 
  AND sub_categories.category_id = categories.id 
HAVING 
  dist < 5 
ORDER BY score DESC 
LIMIT 0, 100

但是，我需要在此查询中包含另一个字段(缩略图)，它来自另一个表 (venue_images)。这个想法是根据它与哪个地点相关以及它的顺序来提取一个图像行。然而，只需要提取一张图像。所以限制 1。

我基本上需要插入这个查询:

SELECT 
  venue_images.thumb_image_filename, 
  venue_images.image_venue_id,  
  venue_images.image_order 
FROM venue_images 
WHERE venue_images.image_venue_id = ven_id //id from above query
ORDER BY venue_images.image_order 
LIMIT 1

进入我的第一个查询，并将这个新字段标记为“缩略图”。

任何帮助将不胜感激。谢谢!

Answer 1

首先，您可以使用 INNER JOIN 编写第一个查询:

SELECT
  ...
FROM
  venues INNER JOIN sub_categories ON venues.sub_category_id = sub_categories.id 
  INNER JOIN categories ON sub_categories.category_id = categories.id
HAVING
  ...

结果应该是一样的，但我更喜欢这个。

接下来我想做的是加入一个子查询，像这样:

...
INNER JOIN (SELECT ... FROM venue_images 
              WHERE venue_images.image_venue_id = ven_id //id from above query
              ORDER BY venue_images.image_order 
              LIMIT 1) first_image

但不幸的是，这个子查询看不到 ven_id，因为它首先被评估，在外部查询之前(我认为这是 MySql 的限制)，所以我们不能使用它，我们必须找到另一个解决方案。由于您使用的是 LIMIT 1，因此仅使用 JOINS 重写所需的条件并不容易。

如果 MySql 提供一个 FIRST() 聚合函数会更容易，但由于它没有，我们必须模拟它，例如这个问题:How to fetch the first and last record of a grouped record in a MySQL query with aggregate functions?

因此，使用这个技巧，您可以编写一个查询，为 每个 image_venue_id 提取第一个 image_id:

SELECT
  image_venue_id,
  SUBSTRING_INDEX(
    GROUP_CONCAT(image_id order by venue_images.image_order),',',1) as first_image_id
FROM venue_images
GROUP BY image_venue_id

并且此查询可以集成到您上面的查询中:

SELECT
  ...
FROM
  venues INNER JOIN sub_categories ON venues.sub_category_id = sub_categories.id 
  INNER JOIN categories ON sub_categories.category_id = categories.id
  INNER JOIN (the query above) first_image on first_image.image_venue_id = venues.id
  INNER JOIN venue_images on first_image.first_image_id = venue_images.image_id
HAVING
  ...

我还添加了一个 JOIN，将第一个图像 ID 与实际图像连接起来。我无法检查您的查询，但我的想法是这样进行。

由于查询现在变得越来越复杂且难以维护，我认为最好创建一个 View 来提取每个场所的第一张图像，然后仅在查询中加入该 View 。这只是一个想法。让我知道它是否有效或您是否需要任何帮助!