我有一个表,看起来像:
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
等等。我想从Col1
、Col2
、Col3
、Col4
中找到前10个最大ABS(条目(。因此,在这种情况下,答案是:
-18
1
.987
.98
-.70
.65
.469
.223
....
等等。但是我该怎么做呢?我遇到了这两个问题,它详细说明了如何仅基于单个列来查找n列的最大绝对值,还有这个问题,但它只查找每个行的最大值。我怎样才能把两者结合起来?
编辑
正如回答中所建议的那样,我尝试了这样的方法:
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
正如David和Kamil所说,您不需要子查询
SQL演示
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
OR以获得准确的输出
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
对于每一列,获取它们的10个最大值,并从所有列中检索10个最高值:
select max_value
from (
(select col1 AS max_value
from yourtable
order by col1 desc
limit 10)
union all
(select col2
from yourtable
order by col2 desc
limit 10)
union all
(select col3
from yourtable
order by col3 desc
limit 10)
union all
(select col4
from yourtable
order by col4 desc
limit 10)
) foo
order by max_value desc
limit 10
select * from (
select Col1 as data from database1
UNION
select Col2 as data from database1
UNION
select Col3 as data from database1
UNION
select Col4 as data from database1
) as T order by data desc ;
union命令在这个场景中非常有用。