您的位置：程序门 -> ms-sql server -> 基础类

统计与排列

[收藏此页] [打印本页]选择字色：背景色：字体：[大][中][小]

统计与排列[已结贴,结贴人:bz586]

发表于：2007-10-11 13:00:02 楼主

表格如下:
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)

发表于：2007-10-11 13:36:461楼得分:0

select max（a),min（a),max（b),min（b),max（c),min（c) from ta

发表于：2007-10-11 13:54:232楼得分:0

select id,a from test1 where a=（ select min（a)from test1)
...
因该可以合并在一起写,偶是新手
等待高手解决

发表于：2007-10-11 13:57:143楼得分:0

max_a min_a max_b min_b max_c min_c
01（7) 02（2) 02（7) 01（1) 04（4) 02（1)
lz的意思是 a b c三个科目
返回的是该科目的最大值及最小值以及相对应的id号

发表于：2007-10-11 13:58:024楼得分:0

最好这样

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

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

发表于：2007-10-11 13:59:185楼得分:0

是的,而且考虑有可能有重复id的情况.

发表于：2007-10-11 13:59:256楼得分:0

select
id,
（select max（a) from ta),
（select min（a) from ta),
（select max（b) from ta),
（select min（b) from ta),
（select max（c) from ta),
（select min（c) from ta)
from ta

发表于：2007-10-11 14:05:047楼得分:0

sql code

declare @t table（id nvarchar（2) , a  int, b int,     c  int) 
insert @t select '01',  7, 1, 2 
insert @t select '02',  2, 7, 1 
insert @t select '03',  5, 3, 2 
insert @t select '04',  3, 3, 4

select
    id+'（'+（select rtrim（max（a)) from @t)+')'
from 
    @t
where a=（select max（a) from @t)

---------------- 
01（7)

（所影响的行数为 1 行）

发表于：2007-10-11 14:13:358楼得分:0

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

结果
-------------
01（7) 02（2) 02（7) 01（1) 04（4) 02（1)

发表于：2007-10-11 14:16:329楼得分:10

发表于：2007-10-11 14:18:4710楼得分:0

有重复 id

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

发表于：2007-10-11 17:16:2511楼得分:0

如果有多个id有相同的最大（小)值,能否把它们这样排列?
比如:
id a b c
01 7 1 2
02 2 7 1
03 5 3 2
04 2 3 4
希望得到如下结果:

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

发表于：2007-10-11 18:02:5512楼得分:10

可以用动态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=isnull（@max_a+',','')+id from py where a in （select max（a) as [a] from py)
    set @max_a=@max_a+'（'+cast（（select min（a) as [a] from py) as varchar)+')'
end
else
begin
    select @max_a=id+'（'+cast（a as varchar)+')' from py where a in （select max（a) as [a] from py)
end
if（（select count（1) from py where a =（select min（a) as [a] from py))>1)
begin
    select @min_a=isnull（@min_a+',','')+id from py where a in （select min（a) as [a] from py)
    set @min_a=@min_a+'（'+cast（（select min（a) as [a] from py) as varchar)+')'
end
else
begin
    select @min_a=id+'（'+cast（a as varchar)+')' from py where a in （select min（a) as [a] from py)
end

if（（select count（1) from py where b =（select max（b) as [b] from py))>1)
begin
    select @max_b=isnull（@max_b+',','')+id from py where b in （select max（b) as [b] from py)
    set @max_b=@max_b+'（'+cast（（select min（b) as [b] from py) as varchar)+')'
end
else
begin
    select @max_b=id+'（'+cast（b as varchar)+')' from py where b in （select max（b) as [b] from py)
end
if（（select count（1) from py where b =（select max（b) as [b] from py))>1)
begin
    select @min_b=isnull（@min_b+',','')+id from py where b in （select min（b) as [b] from py)
    set @min_b=@min_b+'（'+cast（（select min（b) as [b] from py) as varchar)+')'
end
else
begin
    select @min_b=id+'（'+cast（b as varchar)+')' from py where b in （select min（b) as [b] from py)
end
if（（select count（1) from py where c =（select max（c) from py))>1)
begin
    select @max_c=isnull（@max_c+',','')+id from py where c in （select max（c) as [c] from py)
    set @max_c=@max_c+'（'+cast（（select min（c) as [c] from py) as varchar)+')'
end
else
begin
    select @max_c=id+'（'+cast（c as varchar)+')' from py where c in （select max（c) as [c] from py)
end
if（（select count（1) from py where c =（select max（c) from py))>1)
begin
    select @min_c=isnull（@min_c+',','')+id from py where c in （select min（c) as [c] from py)
    set @min_c=@min_c+'（'+cast（（select min（c) as [c] from py) as varchar)+')'
end
else
begin
    select @min_c=id+'（'+cast（c as varchar)+')' from py where c in （select min（c) as [c] from py)
end
select @max_a[max_a],@min_a[min_a],@max_b[max_b],@min_b[min_b],@max_c[max_c],@min_c[min_c] 

调用存储过程：
ＥＸＥＣ wsp111

发表于：2007-10-11 21:02:5513楼得分:0

xuexile