提问人：Cloud5 Studios 提问时间：8/24/2023 最后编辑：Stas TrefilovCloud5 Studios 更新时间：8/24/2023 访问量：45
MySQL - 在 WHERE 子句中使用 GREATEST 中的别名
MySQL - Use Alias inside WHERE clause with GREATEST
问：
我在MySQL中有一个查询，我目前正在使用如下排序：
ORDER BY total_numbers_line_1 DESC, total_numbers_line_2 DESC, total_numbers_line_3 DESC
但是，我需要使用 GREATEST，它应该看起来像这样：
ORDER BY GREATEST(total_numbers_line_1, total_numbers_line_2, total_numbers_line_3);
但是，MySQL不接受它，因为提供的字段是三个SUM子句的ALIAS。有没有办法做到这一点？我需要在排序时考虑三个别名中的最高值。
我已经尝试使用 Greatest、IF 和 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
答：
0赞
Stas Trefilov
8/24/2023
#1
使用 subquery 按行中 3 列中的最大值排序：
SELECT id_bingo, id_card,
total_numbers_line_1,
total_numbers_line_2,
total_numbers_line_3,
GREATEST(total_numbers_line_1, total_numbers_line_2, total_numbers_line_3) AS greatest_total
FROM (
SELECT id_bingo, id_card,
numbers_line1,
numbers_line2,
numbers_line3,
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
) AS tbl
ORDER BY greatest_total DESC;
评论
0赞
Cloud5 Studios
8/24/2023
“total_line_numbers_x”不是 columms，是 SUM clausules 的别名，我现在发布了完整的查询
0赞
danblack
8/24/2023
注意等于 as 返回 1 或 0。将逗号分隔的值列和 3 个“numbers_line”列删除到单独的表中，可以降低这个问题的复杂性，并可能降低同一表上的许多其他查询的复杂性。IF(FIND_IN_SET(...), 1, 0)FIND_IN_SET(...)
0赞
Stas Trefilov
8/24/2023
根据 mysql 手册，FIND_IN_SET(str,strlist) Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings
评论
greatestorder bywhereorder bygreatest在你的子句中也应该不是问题，尽管别名在那里不起作用。如果它们是聚合，您可能会转到您的子句。wherehavinggreatestorder by