gpt4 book ai didi

MySQL - 在多个列中查找 10 条记录的 "Max"绝对值?

转载 作者:行者123 更新时间:2023-11-29 04:36:42 28 4
gpt4 key购买 nike

我有一个看起来像这样的表:

 Country | Item | Col1 | Col2 | Col3 | Col4
4 | 4 | .152 | .01 | .65 | 1
9 | 6 | .145 | .98 | .469 | .001
56 | 7 | .001 | .987 | .011 | .223
78 | 2 | -18 | .269 | -.70 | .1

等等。我想从 Col1Col2Col3Col4 中找到前 10 个最大 ABS(条目)。所以在这种情况下,答案是:

-18
1
.987
.98
-.70
.65
.469
.223
....

等等。但是我该怎么做呢?我遇到了 this其中详细说明了如何仅基于 单个 列和this 找到n 列的最大绝对值。但这只能找到 per each 行的最大值。我怎样才能将两者结合起来?

编辑

按照回答中的建议,我尝试了这样的事情:

SELECT MAX(ABS(`Col1`)) as `absValue` FROM Table1
UNION ALL
SELECT MAX(ABS(`Col2`)) as `absValue` FROM Table1
UNION ALL
SELECT MAX(ABS(`Col3`)) as `absValue` FROM Table1
UNION ALL
SELECT MAX(ABS(`Col4`)) as `absValue` FROM Table1
ORDER BY `absValue` DESC
LIMIT 10

但是得到了:

18
1
0.9869999885559082
0.699999988079071

我做错了什么? SQL Fiddle

任何帮助将不胜感激,谢谢!!

最佳答案

 SELECT  `value` 
FROM (
SELECT col1 as `value` FROM yourTable
UNION all
SELECT col2 as `value` FROM yourTable
UNION all
SELECT col3 as `value` FROM yourTable
UNION all
SELECT col4 as `value` FROM yourTable
) T
ORDER BY `value` DESC
LIMIT 10

正如大卫和卡米尔所说,你不需要子查询

SQL DEMO

SELECT col1 as `value`  FROM Table1
UNION all
SELECT col2 as `value` FROM Table1
UNION all
SELECT col3 as `value` FROM Table1
UNION all
SELECT col4 as `value` FROM Table1
ORDER BY `value` DESC
LIMIT 10

问题编辑之后尝试

SELECT ABS(`Col1`) as `absValue` FROM Table1
UNION ALL
SELECT ABS(`Col2`) as `absValue` FROM Table1
UNION ALL
SELECT ABS(`Col3`) as `absValue` FROM Table1
UNION ALL
SELECT ABS(`Col4`) as `absValue` FROM Table1
ORDER BY `absValue` DESC
LIMIT 10

或得到准确的输出

SELECT `Col1` as `value` FROM Table1
UNION ALL
SELECT `Col2` as `value` FROM Table1
UNION ALL
SELECT `Col3` as `value` FROM Table1
UNION ALL
SELECT `Col4` as `value` FROM Table1
ORDER BY ABS(`value`) DESC
LIMIT 10

关于MySQL - 在多个列中查找 10 条记录的 "Max"绝对值?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/39534818/

28 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com