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

[dobear_0922]

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

[Garnett_KG]

.

[jiang5311]

帮忙顶一下，给点分吧，呵呵

[昵称被占用了]

最简单的，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

[昵称被占用了]

这个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

[昵称被占用了]

这个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

[昵称被占用了]

函数算不算？

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
go

select ID,dbo.fn_getmax(ID) as val from tb

[viva369]

路过看看！

[viva369]

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

[zjcxc]

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

[fcuandy]

[Quote=引用 25 楼 zjcxc 的回复:]
SQL code--一点建议from(selectx=cast((select*fromtbforxml path('r'))asxml)) a--------------------------------------------------- for xml path('r'), type 这样就是xml 类型了, 不用再转换一次吧?
[/Quote]

这样不行的，或者写成 for xml path('r'),type

或者要显式转换， 即用cast 或convert

这样才能调用x方法

[fcuandy]

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

[JavCof]

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 的嵌套，也算一种方法吧？虽然方法笨了一点，但是积极性很高哈。自己先鼓励下多。

[szzwl]

接分

[-狙击手-]

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]

[wanshichen]

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

--这样也给点分吧。。。

[-狙击手-]

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]

[-狙击手-]

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 

[Zoezs]

jf

[huanyuan12]

顶下，学习中

[shanzhongchao]

谢谢～