sqlserver怎么取行记录的最大值

[jiangsmile]

比如说我有如下数据
Day zero one two three four five six sen
jintian 0 1 1 2 5 4 3 2
last year1 5 2 11 11 12 22 3
zuotian 3 2 1 0 6 5 2 7
怎么用sql实现取这三行的最大值,而不是用程序数组实现?

[hhhdyj]

没看明白，是所有列的最大值？

[yangys]

将行转换为列,再用Max()

[子陌红尘]

select
t.[Day],max(t.num) as max_num
from
(select [Day],zero as num from 表 union
select [Day],one from 表 union
select [Day],two from 表 union
select [Day],three from 表 union
select [Day],four from 表 union
select [Day],five from 表 union
select [Day],six from 表 union
select [Day],seven from 表) t
group by
t.[Day]

[子陌红尘]

declare @t table(Day varchar(10),zero int,one int,two int,three int,four int,five int,six int,seven int)
insert into @t select 'jintian ',0,1,1,2 ,5 ,4 ,3 ,2
insert into @t select 'last year',1,5,2,11,11,12,22,3
insert into @t select 'zuotian ',3,2,1,0 ,6 ,5 ,2 ,7

select
t.[Day],max(t.num) as max_num
from
(select [Day],zero as num from @t union
select [Day],one from @t union
select [Day],two from @t union
select [Day],three from @t union
select [Day],four from @t union
select [Day],five from @t union
select [Day],six from @t union
select [Day],seven from @t) t
group by
t.[Day]

/*
Day max_num
---------- -----------
jintian 5
last year 22
zuotian 7
*/

[jiangsmile]

高

[dawugui]

单个的?
select max(sero) as zero ,
max(one) as one,
max(two) as one,
max(three) as three,
max(four) as four,
max(six) as six
max(seven) as seven
from tb

整行加在一起的?
select day , max(data) from
(
select day , sum(data) as data from
(
select day ,zero as data from tb
union all
select day , one as data from tb
union all
select day , three as data from tb
union all
select day , four as data from tb
union all
select day , five as data from tb
union all
select day , six as data from tb
union all
select day , seven as data from tb
) t
group by day
) m
group by day

[dawugui]

if object_id('pubs..tb') is not null
drop table tb
go

create table tb
(
[Day] varchar(10),
zero int,
one int,
two int,
three int,
four int,
five int,
six int,
seven int
)

insert into tb([Day],zero,one,two,three,four,five,six,seven) values('jintian', 0,1,1 ,2 ,5 ,4 ,3,2)
insert into tb([Day],zero,one,two,three,four,five,six,seven) values('last year1',5,2,11,11,12,22,3,0)
insert into tb([Day],zero,one,two,three,four,five,six,seven) values('zuotian', 3,2,1 ,0 ,6 ,5, 2,7)

select top 1 * from
(
select day , sum(data) as data from
(
select day ,zero as data from tb
union all
select day , one as data from tb
union all
select day , three as data from tb
union all
select day , four as data from tb
union all
select day , five as data from tb
union all
select day , six as data from tb
union all
select day , seven as data from tb
) t
group by day
) m
order by data desc

drop table tb

day data
---------- -----------
last year1 55

（所影响的行数为 1 行）