Question

我有以下问询,我很想找到更好的办法来做到这一点,因为我这样做的方式似乎并不正确。

EDIT

我没有具体指出,我只想回那些实际上与所有福利相匹配的广告!

SELECT TOP 50 Advert.Id
FROM Advert 
WHERE Id in(SELECT Advert_id FROM AdvertsToAmenities WHERE Amenity_id = 1   AND Advert_Id = Id)
  AND Id in(SELECT Advert_id FROM AdvertsToAmenities WHERE Amenity_id = 3   AND Advert_Id = Id)
  AND Id in(SELECT Advert_id FROM AdvertsToAmenities WHERE Amenity_id = 5   AND Advert_Id = Id)  

-- OR --

SELECT TOP 50 Advert.Id
FROM Advert 
JOIN AdvertsToAmenities a on Advert.Id = a.Advert_id
JOIN AdvertsToAmenities b on Advert.Id = b.Advert_id
JOIN AdvertsToAmenities c on Advert.Id = c.Advert_id
WHERE a.Amenity_id = 1 
  AND b.Amenity_id = 3 
  AND c.Amenity_id = 5

我很想知道如何优化这些询问。

Answer 1

你的询问令我感到很.。另一种选择是使用这样的东西:

SELECT TOP 50 Advert.Id
  FROM Advert JOIN AdvertsToAmenities a ON Advert.Id = a.Advert_id
 WHERE a.Amenity_id = 1 
    OR a.Amenity_id = 3 
    OR a.Amenity_id = 5
 GROUP BY Advert.Id
HAVING COUNT(DISTINCT a.Amenity_id) = 3

如果<代码>(Advert_Id, Amenity_id) pairs是独一无二的,你可以放弃。 DISTINCT。

Answer 2

How about the following restructure of your query which allows you to simply create a comma seperated list of specific amenity IDs?

SELECT TOP 50 
a.[ID]
FROM [Advert] a
INNER JOIN 
(
    SELECT ata.[Advert_ID]
    FROM [AdvertsToAmenities] ata
    WHERE ata.[Amenity_ID] 
    IN (1, 3, 4, 5) -- Your list of amenity IDs
) as ata
ON a.[ID] = ata.[Advert_ID]

这使用动态表格说明,在加入反照清单之前,只预先列出适用设施。注,Sql服务器在处决前将大大优化次问。这应当意味着,在Sql服务器提供的所有优化情况下,你会得到更可维持的发言的好处。

Answer 3

∗∗∗ * 简单明了。

SELECT TOP 50 a.Id
FROM Advert a, AdvertsToAmenities b 
WHERE a.Amenity_id in ( 1 , 3 , 5 )
and a.Id = b.Amenity_id

友情链接