SQL2005中按列聚合（取N列中的最大最小等）

Question

create table [tb](ID int identity(1,1), a int, b int, c int, d int)insert [tb] select 1,2,3,4 -- max: 4
union all select 5,2,8,3 -- max: 8
union all select 9,7,6,5 -- max: 9
union all select 0,9,2,2 -- max: 9-- Method 1, by dobear
select ID,  Max_abcd=max([value]) 
from tb 
unpivot([value] for [abcd] in ([a], [b], [c], [d])) as U
group by ID/*
ID          Max_abcd
----------- -----------
1           4
2           8
3           9
4           9(4 行受影响)
*/--Method 2, by fcuandy
select ID=cast(b.ID as varchar(8)), Max_abcd=cast(b.v as varchar(8))
from (select x=cast((select * from tb for xml path('r')) as xml)) a
cross apply
    (select ID=x.query('./ID/text()'),v=x.query('max(./*[local-name(.)!="ID"])') 
 from a.x.nodes('//r') as t(x)) b
/*
ID       Max_abcd
-------- --------
1        4
2        8
3        9
4        9(4 行受影响)
*/drop table tb

Answer 1

--Method 2 100分
--Method 3 100分
--剩下的，大家分

Answer 2

waitting for Method 3 ...

Answer 3

--这个算吧
select *,(select max(val) from (select a as val union select b union select c union select d) a) from tb

Answer 4

最简单的，2000方法select ID,MAX(a) as val
from (
select ID,a from tb
union all
select ID,b from tb
union all
select ID,c from tb
union all
select ID,d from tb
) as t
group by ID

Answer 5

这个2005的，思路简单;with t(id,val) 
as (
select ID,a as val from tb
union all
select ID,b as val from tb
union all
select ID,c as val from tb
union all
select ID,d as val from tb
)
select ID,MAX(val) as val from t group by ID

Answer 6

这个2005的，思路简单;with t(id,val) 
as (
select ID,a as val from tb
union all
select ID,b as val from tb
union all
select ID,c as val from tb
union all
select ID,d as val from tb
)
select ID,MAX(val) as val from t group by ID

Answer 7

函数算不算？create function fn_getmax(
@id int
)
returns int
as
begin
declare @a int,@b int,@c int,@d int;
select @a=a,@b=b,@c=c,@d=d from tb where @id=id;
if @b>@a
set @a=@b;
if @c>@a
set @a=@c
if @d>@a
set @a=@d
return @a
end
goselect ID,dbo.fn_getmax(ID) as val from tb

Answer 8

select
case when a>b....else
from
t

Answer 9

-- 一点建议
from (select x=cast((select * from tb for xml path('r')) as xml)) a
--------------------------------------------------- for xml path('r'), type 这样就是xml 类型了, 不用再转换一次吧?

Answer 10

这样不行的，或者写成 for xml path('r'),type或者要显式转换， 即用cast 或convert这样才能调用x方法

Answer 11

否则返回的是xml结构，但不是当作xml类型...

Answer 12

select 
ID, 
MAX_abcd = CASE WHEN d > (CASE 
WHEN c > (CASE WHEN a > b THEN a ELSE b END) 
THEN c 
ELSE (CASE WHEN a > b THEN a ELSE b END) 
END) 
THEN d 
ELSE (CASE 
WHEN c > (CASE WHEN a > b THEN a ELSE b END) 
THEN c 
ELSE (CASE WHEN a > b THEN a ELSE b END) 
END) 
END
from tb/*
ID       Max_abcd
-------- --------
1        4
2        8
3        9
4        9(4 行受影响)
*/CASE WHEN 的嵌套，也算一种方法吧？虽然方法笨了一点，但是积极性很高哈。自己先鼓励下多。

Answer 13

create table [tb](ID int identity(1,1), a int, b int, c int, d int)insert [tb] select 1,2,3,4    -- max: 4
union all select 5,2,8,3    -- max: 8
union all select 9,7,6,5    -- max: 9
union all select 0,9,2,2    -- max: 9
go
select id,max_abcd=max(v) from(
select id,v=a from tb
union select id,b from  tb
union select id,c from tb
union select id,d from tb
) a
group by id
godrop table tb
/*
id          max_abcd    
----------- ----------- 
1           4
2           8
3           9
4           9
*/

Answer 14

union(all)写法和我那个unpivot是一样的，不过写起来复杂些。

Answer 15

--union all 和 unpivot在结果集上可能有点小区别，unpivot不显示Max_abcd为null的记录，有时候这点让人很不爽
if object_id('[tb]') is not null drop table [tb]
go
create table [tb](ID int identity(1,1), a int, b int, c int, d int)insert [tb] select null,null,null,null    -- max: null
union all select 5,2,8,3    -- max: 8
union all select 9,7,6,5    -- max: 9
union all select 0,9,2,2    -- max: 9-- Method 1, by dobear
select ID,  Max_abcd=max([value]) 
from tb 
unpivot([value] for [abcd] in ([a], [b], [c], [d])) as U
group by ID
/*
ID          Max_abcd
----------- -----------
2           8
3           9
4           9(3 row(s) affected)
*/select ID,Max_abcd=(select max([value]) from (select [value]=a union all select b union all select c union all select d) t)
from tb
/*
ID          Max_abcd
----------- -----------
1           NULL
2           8
3           9
4           9
Warning: Null value is eliminated by an aggregate or other SET operation.(4 row(s) affected)
*/

Answer 16

create table [tb](ID int identity(1,1), a int, b int, c int, d int)insert [tb] select null,null,null,null    -- max: null
union all select 5,2,8,3    -- max: 8
union all select 9,7,6,5    -- max: 9
union all select 0,9,2,2    -- max: 9select ID,  Max_abcd=max([value]) 
from tb unpivot([value] for [abcd] in ([a], [b], [c], [d])) as U
group by ID
union all 
select ID, null from tb where coalesce(a,b,c,d) is null order by ID
/*
ID          Max_abcd
----------- -----------
1           NULL
2           8
3           9
4           9(4 行受影响)
*/drop table tb

Answer 17

create table [tb](ID int identity(1,1), a int, b int, c int, d int)insert [tb] select 1,2,3,4    -- max: 4
union all select 5,2,8,3    -- max: 8
union all select 9,7,6,5    -- max: 9
union all select 0,9,2,2    -- max: 9
select id, x.query('//v').value('max(v)','int')  as max_int
from
(select id,
cast('<root><v>'+ltrim(a)+'</v><v>'+ltrim(b)+
'</v><v>'+ltrim(c)+'</v><v>'+ltrim(d)+'</v></root>' as xml) as x
from [tb]) a 
/*
id          max_int
----------- -----------
1           4
2           8
3           9
4           9(4 行受影响)*/drop table [tb]

Answer 18

select ID,MAX(a) as val
from (
select ID,a from tb
union all
select ID,b from tb
union all
select ID,c from tb
union all
select ID,d from tb
) as t
group by ID--这样也给点分吧

Answer 19

create table [tb](ID int identity(1,1), a int, b int, c int, d int)insert [tb] select null,null,null,null    -- max: null
union all select 1,2,3,4    -- max: 4
union all select 5,2,8,3    -- max: 8
union all select 9,7,6,5    -- max: 9
union all select 0,9,2,2    -- max: 9
select id, x.value('max(v)','int')  as max_int
from
(select id,
cast('<v>'+ltrim(a)+'</v><v>'+ltrim(b)+
'</v><v>'+ltrim(c)+'</v><v>'+ltrim(d)+'</v>' as xml) as x
from [tb]) a /*
id          max_int
----------- -----------
1           NULL
2           4
3           8
4           9
5           9(5 行受影响)*/drop table [tb]

Answer 20

select id, x.value('max(v)','int')  as max_int
from
(select id,
cast(replace('<v>'+ltrim(a)+','+ltrim(b)+
','+ltrim(c)+','+ltrim(d)+'</v>',',','</v><v>') as xml) as x
from [tb]) a