表格如下:
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,2insert into py select '02',2,7,1insert into py select '03',5,3,2insert into py select '04',2,3,4create proc wsp111asdeclare @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