mysql - SQL排序通过维护变量顺序

Question

我创建了一个按分数排序的排名查询，然后如果是平局则按创建日期排序。问题是当我使用日期来订购抽奖时，它变得一团糟。

这是我首先拥有的:

SELECT 
    @rownum := @rownum + 1 AS rank,
    sticker_id,
    total_shared,
    total_liked,
    total_used,
    total_shared+total_liked+total_used AS points,
    stickers.date
FROM( 
    SELECT 
        sticker_id,
        SUM(if(event_id = 1, 4, 0)) AS total_shared,
        SUM(if(event_id = 2, 2, 0)) AS total_liked,
        SUM(if(event_id = 3, 6, 0)) AS total_used
    FROM stickers_stats
    WHERE timestamp LIKE '2015-09%'
    GROUP BY sticker_id
) AS ranks 
JOIN (SELECT @rownum := 0) r
INNER JOIN stickers
    ON ranks.sticker_id = stickers.id
ORDER BY points DESC

结果:

---

这就是我得到的结果:

SELECT 
    @rownum := @rownum + 1 AS rank,
    sticker_id,
    total_shared,
    total_liked,
    total_used,
    total_shared+total_liked+total_used AS points,
    stickers.date
FROM( 
    SELECT 
        sticker_id,
        SUM(if(event_id = 1, 4, 0)) AS total_shared,
        SUM(if(event_id = 2, 2, 0)) AS total_liked,
        SUM(if(event_id = 3, 6, 0)) AS total_used
    FROM stickers_stats
    WHERE timestamp LIKE '2015-09%'
    GROUP BY sticker_id
) AS ranks 
JOIN (SELECT @rownum := 0) r
INNER JOIN stickers
    ON ranks.sticker_id = stickers.id
ORDER BY points DESC, stickers.date ASC

结果:

我已经尝试更改字段的显示顺序和所有内容，但找不到解决方案。如何通过 points 和 stickers.date 使用新排名位置进行排序？

Answer 1

当您开始将变量与其他结构混合时，MySQL 可能会感到困惑。我不确定是什么提示您的查询越界，但使用子查询应该有所帮助:

SELECT @rownum := @rownum + 1 AS rank, t.*
FROM (SELECT sticker_id, total_shared, total_liked, total_used,
             (total_shared + total_liked + total_used) AS points,
             s.date
      FROM (SELECT sticker_id,
                   SUM(if(event_id = 1, 4, 0)) AS total_shared,
                   SUM(if(event_id = 2, 2, 0)) AS total_liked,
                   SUM(if(event_id = 3, 6, 0)) AS total_used
            FROM stickers_stats
            WHERE timestamp >= '2015-09-01' and timestamp < '2015-10-01'
            GROUP BY sticker_id
          ) r JOIN
          stickers s
          ON r.sticker_id = s.id 
     ) t CROSS JOIN
     (SELECT @rownum := 0) params
ORDER BY points DESC, date ASC;

还有:

• 不要对日期/时间值使用 LIKE。
• 始终将 ON 子句与 JOIN 一起使用。如果您想要一个CROSS JOIN，请明确。