MySQL 每隔一行加权重

Question

我有一个问题，我无法使用 MySQL 的 SQL 查询来解决。

第一个表“内容”

ID/标题/作者/策划/创建

下表“白名单”

ID/作者/权重

我想让 SQL 做的是选择所有内容 WHERE content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day)) 另外我要订购作者随机的权重，因此权重高的作者会比权重低的作者显示更多。至于现在我正在使用这个函数 ORDER BY -LOG(RAND(1337))/whitelist.weight ASC。最后，我希望每一行都被策划，而下一行不是。

所以结果会是这样的。

ID  Title       Author      Curated     Created
3   My Home1    Krister     1           2015-01-20
13  My Home14   Krister     0           2015-01-20
33  My Home8    Eva         1           2015-01-15
34  My Home11   Krister     0           2015-01-01
43  My Home18   Eva         1           2015-01-01

我试过的...

SELECT *
FROM (
    SELECT `content`.*, IF(`content`.`curated`=0, @mr:=@mr+1, @fr:=@fr+1) AS cur
    FROM `content` INNER JOIN `whitelist` ON `content`.`author` = `whitelist`.`author` , (SELECT @mr:=0, @fr:=0) initvars
    WHERE
        content.is_deleted = 0 
        AND content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day))
    ) tmp
INNER JOIN whitelist ON tmp.author = whitelist.author
ORDER BY cur ASC LIMIT 5 OFFSET 0;

Answer 1

试试这个:

SELECT * FROM content
JOIN whitelist ON content.Author = whitelist.Author
WHERE content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day))
ORDER BY (RAND() * (SELECT MAX(Weight) FROM whitelist)) + Weight DESC

Now with curated:

SET @pos1=0;
SET @pos2=0;

SELECT * FROM 
(
    SELECT *, @pos1 := @pos1 + 1 pos FROM
    (
        SELECT Content.* FROM content
        JOIN whitelist ON content.Author = whitelist.Author
        WHERE curated = 0
        AND content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day))
        ORDER BY (RAND() * (SELECT MAX(Weight) FROM whitelist)) + Weight 
    ) s1
    UNION
    SELECT *, @pos2 := @pos2 + 1 pos FROM
    (
        SELECT Content.* FROM content
        JOIN whitelist ON content.Author = whitelist.Author
        WHERE curated = 1
        AND content.created >= UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 14 day))
        ORDER BY (RAND() * (SELECT MAX(Weight) FROM whitelist)) + Weight 
    ) s2
) t1
ORDER BY pos DESC, Curated