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

Question

我有 2 张表，一张是 Venues，一张是 Listings，一对多从 field 到列表的关系。

一些场馆付费以获得“特色”，他们的结果比不付费的场馆获得额外的特色。假设 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 之外，查询都是相同的。

我如何将第一个查询(联合之上)更改为动态随机选择 2 个特色行？ 2 是一个限制，如果表中只有一个匹配行，那么 1 就可以了。

Answer 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()