SQL、按随机排序和LIMIT不起作用

Question

为什么这个查询没有在15停止？这是我的疑问。

SELECT title, type, profile
FROM playlists
WHERE active='1' AND feature='1' AND type='3' AND user='1'
ORDER BY RAND()
AND LIMIT 15

这导致了几十条记录，我只想每次显示15个随机结果。我在俯瞰什么？

Answer 1

您可以尝试如下操作-您需要在LIMIT之前删除AND

SELECT title, type, profile FROM playlists WHERE active='1' AND feature='1' 
AND type='3' AND user='1' 
ORDER BY RAND() 
LIMIT 15

Answer 2

您需要删除关键字LIMIT之前的AND。

$sql = "SELECT title, type, profile FROM playlists WHERE active='1' AND feature='1' AND type='3' AND user='1' ORDER BY RAND()  LIMIT 15";

Answer 3

这里有一个用于随机化结果的有用链接。请在此处查看。

您的代码仅适用于MySQL。对于PostgreSQL和sqlite，请使用RANDOM()。对于SQL Server，请使用NEWID()。对于PostgreSQL和sqlite

$sql = "SELECT title, type, profile FROM playlists WHERE active='1' AND feature='1' AND type='3' AND user='1' ORDER BY RANDOM() LIMIT 15";

对于SQl服务器

$sql = "SELECT title, type, profile FROM playlists WHERE active='1' AND feature='1' AND type='3' AND user='1' ORDER BY NEWID() FETCH NEXT 15 ROWS ONLY";

请注意SQL Server中limit的不同用法。这是一个问题。林克。