我有2个表，一个是Venues，一个是Listings，one to many relationship from Venue to Listings。
一些场馆付费以“特色”为特色，其结果比不付费的场馆具有更多的功能。很有可能说10个场馆中就有5个会成为特色。我不想显示5个精选和5个正常，而是2个精选和8个正常。显然，选择的2应该是动态的。
目前，我有这样的查询：（为简便起见，已删除了列数）
SELECT V.VenueName FROM Venues V
INNER JOIN Listings L ON V.VenueID = L.FK_VenueID
INNER JOIN Type T ON L.FK_ListingTypeID = T.TypeID
WHERE V.Active = 1 AND L.Active = 1 AND T.TypeID = 1 AND T.Active = 1 AND V.Featured = 1
AND HOUR(L.StartTime) <= 16 AND HOUR(L.EndTime) > 16 AND DATE(NOW()) >= DATE(L.StartDate)
AND DATE(NOW()) <= DATE(L.EndDate) AND L.DayOfWeek LIKE '%Tuesday%'
AND DATE(L.StartDate) <= '2015-01-14' AND DATE(L.EndDate) >= '2015-01-14'
UNION
SELECT V.VenueName FROM Venues V
INNER JOIN Listings L ON V.VenueID = L.FK_VenueID
INNER JOIN Type T ON L.FK_ListingTypeID = T.TypeID
WHERE V.Active = 1 AND L.Active = 1 AND T.TypeID = 1 AND T.Active = 1 AND V.Featured = 0
AND HOUR(L.StartTime) <= 16 AND HOUR(L.EndTime) > 16 AND DATE(NOW()) >= DATE(L.StartDate)
AND DATE(NOW()) <= DATE(L.EndDate) AND L.DayOfWeek LIKE '%Tuesday%'
AND DATE(L.StartDate) <= '2015-01-14' AND DATE(L.EndDate) >= '2015-01-14'
如您所见，除了中的查询，其他查询均相同Featured = 1/Featured = 0。
我将如何将第一个查询（在联合上方）更改为dynamically pick 2 featured rows at random？2是一个限制，如果表中只有一个匹配的行，则1是可以的。
您可能可以使用
SELECT VenueName from (
SELECT V.VenueName FROM Venues V
INNER JOIN Listings L ON V.VenueID = L.FK_VenueID
INNER JOIN Type T ON L.FK_ListingTypeID = T.TypeID
WHERE V.Active = 1 AND L.Active = 1 AND T.TypeID = 1 AND T.Active = 1 AND V.Featured = 1
AND HOUR(L.StartTime) <= 16 AND HOUR(L.EndTime) > 16 AND DATE(NOW()) >= DATE(L.StartDate)
AND DATE(NOW()) <= DATE(L.EndDate) AND L.DayOfWeek LIKE '%Tuesday%'
AND DATE(L.StartDate) <= '2015-01-14' AND DATE(L.EndDate) >= '2015-01-14'
ORDER BY RAND() LIMIT 2.) a
UNION
SELECT VenueName from (
SELECT V.VenueName FROM Venues V
INNER JOIN Listings L ON V.VenueID = L.FK_VenueID
INNER JOIN Type T ON L.FK_ListingTypeID = T.TypeID
WHERE V.Active = 1 AND L.Active = 1 AND T.TypeID = 1 AND T.Active = 1 AND V.Featured = 0
AND HOUR(L.StartTime) <= 16 AND HOUR(L.EndTime) > 16 AND DATE(NOW()) >= DATE(L.StartDate)
AND DATE(NOW()) <= DATE(L.EndDate) AND L.DayOfWeek LIKE '%Tuesday%'
AND DATE(L.StartDate) <= '2015-01-14' AND DATE(L.EndDate) >= '2015-01-14') b
参考：与Rand（）的并集
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句