如何用SQL语句(不是Oracle),求出下表每一行的5个字段中的最大值,最后生成一个新字段。
例如:
第一行最大值 -5.0 (c字段) 空值忽略
第二行最大值 -5.5 (a字段) 空值忽略a b c d e
-21.5 -15.0 -5.0
-5.5 -11.5
-5.0 -16.5 -10.5
-9.0
-11.5 -14.0 -8.5
-10.5 -11.0 -15.5 -14.0 -12.5
-15.0 -11.0 -10.5 -17.0
-12.5 -8.0 -14.5
-8.0 -12.0
-6.5 -11.5 -19.5 -22.5 -20.0
-13.0 -7.5 -14.0
-8.0 -12.0 -12.0
-11.0 -11.0 -12.0
-13.5 -18.5 -14.5 -4.5
-3.0 -13.0
-5.0 -31.5 -24.5
-10.0 -12.0 -11.5
-17.5 -15.0 -12.0 -10.5 -19.5
-13.5 -6.5
-3.5 -19.0
-11.5 -11.5
-7.0 -17.5 -16.5
-5.5 -15.0 -31.5
-12.5 -13.0 -9.5 -31.5
-3.0 -24.5
-4.5 -31.5 -25.0
-15.0 -22.5 -7.5 -22.5
-6.5 -22.0
-14.0 -14.0 -12.5 -10.5
-9.0 -24.0
-8.5 -28.0
-10.0 -15.5 -8.0 -20.0
-10.5 -26.0 -17.0 -12.5
-7.0 -15.5 -22.5
-8.5 -21.0
-12.5 -9.5 -13.0 -14.0
-15.5 -10.5 -13.5 -25.5
-18.5 -14.5 -8.5
-6.0 -23.5
-7.0 -6.5 -18.5
-18.0 -14.5 -14.0 -11.5 -21.0
-7.5 -16.5
-15.0 -21.5 -7.5 -16.5
-7.0 -10.5 -25.5
-14.0 -10.0 -14.5
例如:
第一行最大值 -5.0 (c字段) 空值忽略
第二行最大值 -5.5 (a字段) 空值忽略a b c d e
-21.5 -15.0 -5.0
-5.5 -11.5
-5.0 -16.5 -10.5
-9.0
-11.5 -14.0 -8.5
-10.5 -11.0 -15.5 -14.0 -12.5
-15.0 -11.0 -10.5 -17.0
-12.5 -8.0 -14.5
-8.0 -12.0
-6.5 -11.5 -19.5 -22.5 -20.0
-13.0 -7.5 -14.0
-8.0 -12.0 -12.0
-11.0 -11.0 -12.0
-13.5 -18.5 -14.5 -4.5
-3.0 -13.0
-5.0 -31.5 -24.5
-10.0 -12.0 -11.5
-17.5 -15.0 -12.0 -10.5 -19.5
-13.5 -6.5
-3.5 -19.0
-11.5 -11.5
-7.0 -17.5 -16.5
-5.5 -15.0 -31.5
-12.5 -13.0 -9.5 -31.5
-3.0 -24.5
-4.5 -31.5 -25.0
-15.0 -22.5 -7.5 -22.5
-6.5 -22.0
-14.0 -14.0 -12.5 -10.5
-9.0 -24.0
-8.5 -28.0
-10.0 -15.5 -8.0 -20.0
-10.5 -26.0 -17.0 -12.5
-7.0 -15.5 -22.5
-8.5 -21.0
-12.5 -9.5 -13.0 -14.0
-15.5 -10.5 -13.5 -25.5
-18.5 -14.5 -8.5
-6.0 -23.5
-7.0 -6.5 -18.5
-18.0 -14.5 -14.0 -11.5 -21.0
-7.5 -16.5
-15.0 -21.5 -7.5 -16.5
-7.0 -10.5 -25.5
-14.0 -10.0 -14.5
假设你的表有个标识字段[ID],没有的话建一个
SELECT [ID],MaxVal = max(value)
from(
select [ID],value = [a]
from [表]
union all
select [ID],value = [b]
from [表]
union all
select [ID],value = [c]
from [表]
union all
select [ID],value = [d]
from [表]
union all
select [ID],value = [e]
from [表]) as tmp1
GROUP BY
Tmp1.[ID]
insert T select -21.5, -15.0, -5.0, null, null
union all select -5.5, -11.5, null, null, null
union all select -1.0, -16.5, -10.5, null, null
select *,
max_value=(
select max(A) from
(
select A
union all
select B
union all
select C
union all
select D
union all
select E
)tmp)
from T
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)
(
select A
union all
select B
union all
select C
union all
select D
union all
select E
)tmp)
from T
这个当的的'tmp'是什么意思?
-----------
三楼的也没错啊。
这个当的的'tmp'是什么意思?
--------
查询结果的别名,相当于一个表名。
union all
select B
union all
select C
union all
select D
union all
select E
这个是不是把所有的字段整和成一个名为A 的字段啊,那select max(A) 怎么会取每一个行的最大值呢,望高手讲解
drop table tb
gocreate table tb(a decimal(18,2),b decimal(18,2),c decimal(18,2),d decimal(18,2),e decimal(18,2))
insert into tb(a,b,c,d,e) values(-21.5,-15.0,-5.0,null,null)
insert into tb(a,b,c,d,e) values(-5.5,-11.5,null,null,null)
insert into tb(a,b,c,d,e) values(-5.0,-16.5,-10.5,null,null)
goselect id=identity(int,1,1) , * into test from tbselect id , max(value) as value from
(
select id , a as value from test
union all
select id , b as value from test
union all
select id , c as value from test
union all
select id , d as value from test
union all
select id , d as value from test
) t
group by iddrop table tb,test/*
id value
----------- --------------------
1 -5.00
2 -5.50
3 -5.00(所影响的行数为 3 行)
*/
借用乌龟大哥的数据.
if object_id('pubs..tb') is not null
drop table tb
gocreate table tb(a decimal(18,2),b decimal(18,2),c decimal(18,2),d decimal(18,2),e decimal(18,2))
insert into tb(a,b,c,d,e) values(-21.5,-15.0,-5.0,null,null)
insert into tb(a,b,c,d,e) values(-5.5,-11.5,null,null,null)
insert into tb(a,b,c,d,e) values(-5.0,-16.5,-10.5,null,null)
gocreate function f_tb(@a decimal(18,2),@b decimal(18,2))
returns decimal(18,2)
as
begin
declare @max decimal(18,2)
if @a<@b
set @max=@b
else
set @max=@a
return @max
endselect dbo.f_tb(dbo.f_tb(dbo.f_tb(dbo.f_tb(a,b),c),d),e)
from tb
union all
select B
union all
select C
union all
select D
union all
select E
-------------
这个是把“当前这一行”的所有字段的值union在一起,再取一个最大值,就是这一行的最大值。
我觉得这种方法最好,不用再加个ID什么的。
这次的用法~~是这个帖子的升级版~~~
http://community.csdn.net/Expert/topic/5370/5370448.xml?temp=.9551813
---------不错!
http://community.csdn.net/Expert/topic/5370/5370448.xml?temp=.9551813
-----------
这孩子真聪明……
union all
select B
union all
select C
union all
select D
union all
select E
这个为什么后面不要加from就可以,而加了from后结果就是整个表的最大值