一条SQL语句实现:一行多个字段数据的最大值。
原帖:http://community.csdn.net/Expert/topic/5403/5403570.xml?temp=.6892511
在回帖中发现有一方法不错,那就是marco08(天道酬勤) 的回帖,如下:
1
create table T(A decimal(10,1), B decimal(10,1), C decimal(10,1), D decimal(10,1), E decimal(10,1))
2
insert T select -21.5,-15.0,-5.0, null, null
3
union all select -5.5,-11.5,null, null, null
4
union all select -1.0,-16.5,-10.5, null, null
5![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
select *,
8
max_value=(
9
select max(A) from
10
(
11
select A
12
union all
13
select B
14
union all
15
select C
16
union all
17
select D
18
union all
19
select E
20
)tmp)
21
from T
22![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
2
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
3
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
4
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
9
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
11
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
13
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
15
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
17
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
19
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
22
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
--result
A B C D E max_value
------------ ------------ ------------ ------------ ------------ ------------
-21.5 -15.0 -5.0 NULL NULL -5.0
-5.5 -11.5 NULL NULL NULL -5.5
-1.0 -16.5 -10.5 NULL NULL -1.0
(3 row(s) affected)
这一方法,自我感觉不错,还真的第1次看到这样的写法。原来SQL里面还可以实现这样的写法,又学到了一点知识。