php - 如何使用多次出现的项目构建具有偏差的mysql查询

Question

我想构建一个查询来随机显示 ITEM 表中的项目，但根据项目类别在 PREFERENCE 表中存在的次数(较大偏差)。类别必须自动添加到查询中。

ITEM TABLE
Itemname   Category Id
'item1', '20081'
'item2 ', '15032'
'items3', '20081'
'item4', '20081'

PREFERENCE TABLE
Userid, Categoryname, Categoryid
'79', 'Everything Else', '15032'
'146', 'Antiques', '20081'
'79', 'Antiques', '20081'
'79', 'Antiques', '20081'
'79', 'Antiques', '20081'

简单来说就是这样

SELECT * FROM `ex`.`item` where category_id=20081 or category_id=79 /*there rest to be added automatically and also with the bias depending on the count in preference  */order by rand();

Answer 1

SELECT i.*
FROM item i
JOIN (SELECT Categoryid, COUNT(*) cat_count
      FROM preference
      WHERE Categoryid IN (20081, 79)
      GROUP BY Categoryid) p
ON i.Categoryid = p.Categoryid
ORDER BY cat_count*RAND() DESC