使用MySQL 'search until'功能实现有序计数的技术问询

嘿，我来帮你搞定这个有序计数的问题！

首先，先确认下你原来的"search until"逻辑：按value降序排列，逐行处理直到遇到value=100的行（根据你的语句，这一行会被排除，之前的行都会保留）。但直接用COUNT(*)的时候，你可能忽略了MySQL的执行顺序——WHERE子句会在ORDER BY之前执行，这就导致变量@until_var的赋值是在未排序的数据上进行的，自然得不到正确的计数结果。

要解决这个问题，我们需要先确保数据是按value DESC排序好的，再在这个有序结果上应用"search until"的过滤逻辑，最后统计数量。正确的SQL语句应该是这样的：

SELECT COUNT(*) AS matching_rows_count
FROM (
    -- 先按value降序排序，确保数据顺序正确
    SELECT value
    FROM `table`
    ORDER BY value DESC
) sorted_data
-- 初始化变量
JOIN (SELECT @until_var := NULL) _until
-- 应用原有的过滤逻辑
WHERE ISNULL(@until_var := IF(value = 100, value, @until_var));

为什么这样能行？ #

• 内层子查询sorted_data先把表中的数据按value降序排列，保证我们是从最大的value开始逐行检查；
• 然后在这个有序的结果集上，用@until_var变量逐行判断：
  • 初始时@until_var是NULL，对于value≠100的行，IF语句会保留@until_var的NULL值，ISNULL(NULL)为true，所以这些行会被保留；
  • 当遇到第一个value=100的行时，@until_var被赋值为100，ISNULL(100)为false，该行被排除，同时后续所有行的@until_var已经是100，都会被排除；
• 最后COUNT(*)就会统计出所有符合条件的行数。

另外，顺便提一句：你原来的SELECT *语句其实也存在执行顺序的隐患，虽然可能在某些情况下能工作，但更可靠的写法应该和上面的计数语句一样，先排序再过滤：

SELECT *
FROM (
    SELECT *
    FROM `table`
    ORDER BY value DESC
) sorted_data
JOIN (SELECT @until_var := NULL) _until
WHERE ISNULL(@until_var := IF(value = 100, value, @until_var));

这样能确保变量的逻辑是在有序数据上执行的，避免因为MySQL执行计划变化导致结果出错。

内容的提问来源于stack exchange，提问作者Krupp