mysql - 在mysql中的连接表的子选择中获取具有相同值的字段

Question

我遇到了一个问题。这很难解释，所以我将在下面放置示例:

我的 table :

region_hotelids
------------------------
EANHotelID  |  RegionID
------------------------
1234           935
2343           935
1234           850

hotel_attributes
------------------------
EANHotelID  |  AttributeID
------------------------
1234           1
1234           2
1234           4
1234           6

2343           1
2343           4
2343           2
2343           9

现在我首先需要找到所有具有相同属性的酒店:

SELECT r.EANHotelID, COUNT(c.EANHotelID) AS cat_count
FROM region_hotelid as r 
INNER JOIN hotel_attributes AS c ON r.EANHotelID = c.EANHotelID
WHERE c.AttributeID IN (1,2) AND r.RegionID = 935
GROUP BY c.EANHotelID
HAVING cat_count = 2

这很好地工作并输出了这个(找到所有包含这两个 attributeID 的 EANHotelID):

result
EANHotelID  |  cat_count
---------------------------
1234           2
2343           2

现在的问题是，我需要获取这两家酒店共有的所有其他属性 ID。在此示例中，它只是 AttributeID 4。我不想要任何不相同的 AttributeIDS(在此示例中为 9 和 6)。这是我想添加到原始查询中的内容:

SELECT r.EANHotelID, COUNT(c.EANHotelID) AS cat_count, 
    (SELECT GROUP_CONCAT(c2.AttributeID) as ids 
     FROM hotel_attributes as c2 
     WHERE c2.EANHotelID = r.EANHotelID
     AND c.EANHotelID = r.EANHotelID
     GROUP BY c2.EANHotelID ORDER BY ids ASC ) as similarIds
FROM region_hotelid as r 
INNER JOIN hotel_attributes AS c ON r.EANHotelID = c.EANHotelID
WHERE c.AttributeID IN (9,44) AND r.RegionID = 935
GROUP BY c.EANHotelID
HAVING cat_count = 2

但不是只返回我想要的 ID(4 个或全部 - 1、2、4)，而是返回与此 EANHotelID 关联的所有 AttributeId:

result
EANHotelID  |  cat_count  |  similarIds
----------------------------------------
1234           2            1,2,3,4,6
2343           2            1,2,3,4,9

我完全不知道如何克服这个问题。任何建议或信息将不胜感激!

Answer 1

您可以使用另一个 INNER JOIN 来只选择所有酒店都具有相同属性的行。我将 WHERE 子句移动到 JOIN 的 ON 条件中。

查看工作 SQL-Fiddle example here .

请注意，您的 HAVING COUNT(..) 条件表现不佳。数据库引擎必须在 SELECT 之后检查每个结果集，以查看您的 HAVING 条件是否匹配。我没有改变它，但我认为这也应该优化!

SELECT 
  ha.*
FROM
  hotel_attributes ha 
INNER JOIN hotel_attributes ha_other
ON
  ha.AttributeID = ha_other.AttributeID
  AND NOT ha.EANHotelID = ha_other.EANHotelID
  AND ha_other.EANHotelID IN ( SELECT r.EANHotelID
    FROM region_hotelids as r 
    INNER JOIN hotel_attributes AS c ON r.EANHotelID = c.EANHotelID
    WHERE c.AttributeID IN (1,2) AND r.RegionID = 935
    GROUP BY c.EANHotelID
    HAVING COUNT(c.EANHotelID) = 2
  )
GROUP BY ha.EANHotelID, ha.AttributeID