MySQL中如何用GREATEST对SUM别名字段进行排序？

问题描述 #

我有一个在线彩票系统的MySQL查询，当前排序逻辑是：

ORDER BY total_numbers_line_1 DESC, total_numbers_line_2 DESC, total_numbers_line_3 DESC

我需要改成按这三个字段的最大值排序，期望写法是：

ORDER BY GREATEST(total_numbers_line_1, total_numbers_line_2, total_numbers_line_3);

但因为这三个字段是SUM子句的别名，MySQL不支持直接在ORDER BY中用GREATEST引用别名，试过GREATEST、IF、CASE WHEN都没解决，求可行方法。

我的实际查询语句：

SELECT *,
  SUM(IF(FIND_IN_SET('21', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line1), 1 ,0)
  ) AS total_numbers_line_1,
  SUM(IF(FIND_IN_SET('21', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line2), 1 ,0)
  ) AS total_numbers_line_2,
  SUM(IF(FIND_IN_SET('21', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line3), 1 ,0)
  ) AS total_numbers_line_3
FROM cards
WHERE id_bingo = '64e3da663a617'
GROUP BY
    id_card
HAVING
    total_numbers_line_1 > 0
    OR total_numbers_line_2 > 0
    OR total_numbers_line_3 > 0 
ORDER BY total_numbers_line_1 DESC,
  total_numbers_line_2 DESC,
  total_numbers_line_3 DESC
LIMIT 20

可行解决方案 #

方案1：子查询/CTE包裹（推荐） #

把原查询的结果作为临时表，在外层使用GREATEST函数直接引用别名排序，绕开MySQL不能在ORDER BY中用函数引用SELECT别名的限制：

子查询写法（兼容所有MySQL版本）

SELECT *
FROM (
  SELECT *,
    SUM(IF(FIND_IN_SET('21', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('15', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('43', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('42', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('78', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('4', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('27', numbers_line1), 1 ,0)
    ) AS total_numbers_line_1,
    SUM(IF(FIND_IN_SET('21', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('15', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('43', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('42', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('78', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('4', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('27', numbers_line2), 1 ,0)
    ) AS total_numbers_line_2,
    SUM(IF(FIND_IN_SET('21', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('15', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('43', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('42', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('78', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('4', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('27', numbers_line3), 1 ,0)
    ) AS total_numbers_line_3
  FROM cards
  WHERE id_bingo = '64e3da663a617'
  GROUP BY id_card
  HAVING total_numbers_line_1 > 0 OR total_numbers_line_2 > 0 OR total_numbers_line_3 > 0
) AS temp_table
ORDER BY GREATEST(total_numbers_line_1, total_numbers_line_2, total_numbers_line_3) DESC
LIMIT 20

CTE写法（MySQL 8.0及以上版本支持）

用WITH子句创建临时结果集，写法更清晰：

WITH temp_table AS (
  SELECT *,
    SUM(IF(FIND_IN_SET('21', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('15', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('43', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('42', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('78', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('4', numbers_line1), 1 ,0) +
      IF(FIND_IN_SET('27', numbers_line1), 1 ,0)
    ) AS total_numbers_line_1,
    SUM(IF(FIND_IN_SET('21', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('15', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('43', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('42', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('78', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('4', numbers_line2), 1 ,0) +
      IF(FIND_IN_SET('27', numbers_line2), 1 ,0)
    ) AS total_numbers_line_2,
    SUM(IF(FIND_IN_SET('21', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('15', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('43', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('42', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('78', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('4', numbers_line3), 1 ,0) +
      IF(FIND_IN_SET('27', numbers_line3), 1 ,0)
    ) AS total_numbers_line_3
  FROM cards
  WHERE id_bingo = '64e3da663a617'
  GROUP BY id_card
  HAVING total_numbers_line_1 > 0 OR total_numbers_line_2 > 0 OR total_numbers_line_3 > 0
)
SELECT *
FROM temp_table
ORDER BY GREATEST(total_numbers_line_1, total_numbers_line_2, total_numbers_line_3) DESC
LIMIT 20

方案2：在ORDER BY中重复计算逻辑 #

直接把每个total_numbers_line的SUM计算逻辑放到GREATEST函数里，代码冗余但不需要修改查询结构：

SELECT *,
  SUM(IF(FIND_IN_SET('21', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line1), 1 ,0)
  ) AS total_numbers_line_1,
  SUM(IF(FIND_IN_SET('21', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line2), 1 ,0)
  ) AS total_numbers_line_2,
  SUM(IF(FIND_IN_SET('21', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line3), 1 ,0)
  ) AS total_numbers_line_3
FROM cards
WHERE id_bingo = '64e3da663a617'
GROUP BY id_card
HAVING total_numbers_line_1 > 0 OR total_numbers_line_2 > 0 OR total_numbers_line_3 > 0 
ORDER BY GREATEST(
  SUM(IF(FIND_IN_SET('21', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line1), 1 ,0)
  ),
  SUM(IF(FIND_IN_SET('21', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line2), 1 ,0)
  ),
  SUM(IF(FIND_IN_SET('21', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line3), 1 ,0)
  )
) DESC
LIMIT 20

方案3：用CASE WHEN手动实现最大值判断 #

如果不想用子查询或重复计算，可以在ORDER BY里用CASE WHEN逐个比较三个字段的值，取最大的那个排序，兼容所有MySQL版本：

SELECT *,
  SUM(IF(FIND_IN_SET('21', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line1), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line1), 1 ,0)
  ) AS total_numbers_line_1,
  SUM(IF(FIND_IN_SET('21', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line2), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line2), 1 ,0)
  ) AS total_numbers_line_2,
  SUM(IF(FIND_IN_SET('21', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('15', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('43', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('42', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('78', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('4', numbers_line3), 1 ,0) +
    IF(FIND_IN_SET('27', numbers_line3), 1 ,0)
  ) AS total_numbers_line_3
FROM cards
WHERE id_bingo = '64e3da663a617'
GROUP BY id_card
HAVING total_numbers_line_1 > 0 OR total_numbers_line_2 > 0 OR total_numbers_line_3 > 0 
ORDER BY 
  CASE
    WHEN total_numbers_line_1 >= total_numbers_line_2 AND total_numbers_line_1 >= total_numbers_line_3 THEN total_numbers_line_1
    WHEN total_numbers_line_2 >= total_numbers_line_1 AND total_numbers_line_2 >= total_numbers_line_3 THEN total_numbers_line_2
    ELSE total_numbers_line_3
  END DESC
LIMIT 20

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