统计与排列
表格如下:
id a b c  
01 7 1 2
02 2 7 1
03 5 3 2
04 3 3 4
希望得到如下结果:


max_a min_a max_b min_b max_c min_c
01(7) 02(2) 02(7) 01(1) 04(4) 02(1)


------解决方案--------------------
CREATE TABLE tab (id varchar(2),a int,b int,c int)
insert tab select '01', 7, 1, 2 
insert tab select '02', 2, 7, 1 
insert tab select '03', 5, 3, 2 
insert tab select '04', 3, 3, 4

select max(max_a)as max_a, max(min_a)as min_a, max(max_b)as max_b, max(min_b)as min_b, max(max_c) as max_c, max(min_c)as min_c from
(
select 
case when a=(select max(a) from tab) then id+quotename(a,'()') end as max_a,
case when a=(select min(a) from tab) then id+quotename(a,'()') end as min_a,
case when b=(select max(b) from tab) then id+quotename(b,'()') end as max_b,
case when b=(select min(b) from tab) then id+quotename(b,'()') end as min_b,
case when c=(select max(c) from tab) then id+quotename(c,'()') end as max_c,
case when c=(select min(c) from tab) then id+quotename(c,'()') end as min_c
from tab
) a


------解决方案--------------------
可以用动态SQL做的。 不过动态SQL拼起来费劲。赶着下班。所以用这个笨方法了。不过可以用。

SQL code

create table py(id varchar(10),a int,b int,c int)
insert into py select '01',7,1,2
insert into py select '02',2,7,1
insert into py select '03',5,3,2
insert into py select '04',2,3,4


create proc wsp111
as
declare @max_a varchar(10)
declare @min_a varchar(10)
declare @max_b varchar(10)
declare @min_b varchar(10)
declare @max_c varchar(10)
declare @min_c varchar(10)
if((select count(1) from py where a =(select max(a) as [a] from py))>1)
begin
    select @max_a=isnullull,null