mysql - 使用RAND从一组n中选择一行

我有2个表，一个是Venues，一个是Listings，one to many relationship from Venue to Listings。

一些场馆付费以“特色”为特色，其结果比不付费的场馆具有更多的功能。可以说，十分之十的场馆将有特色。我不想显示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

参考：Union with Rand()

关于mysql - 使用RAND从一组n中选择一行，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27937796/