表结构如下:
业务员 业务1(业务量值) 业务2 业务3
我想得到每个业务办理量的前10名的业务数量和业务员的名字,怎么得到呀
业务1 业务员 业务2 业务员 业务3 业务员每项业务和业务员都取前10名,生成一张表.
业务员 业务1(业务量值) 业务2 业务3
我想得到每个业务办理量的前10名的业务数量和业务员的名字,怎么得到呀
业务1 业务员 业务2 业务员 业务3 业务员每项业务和业务员都取前10名,生成一张表.
select (select top 10 业务1, 业务员 from 表 order by 业务1 desc),
(select top 10 业务2, 业务员 from 表 order by 业务3 desc),
(select top 10 业务3, 业务员 from 表 order by 业务3 desc)
select top 10 业务员,业务2,[ID]=identity(int,1,1) into #2 from T order by 业务2 desc
select top 10 业务员,业务3,[ID]=identity(int,1,1) into #3 from T order by 业务3 desc
select
a.业务员,业务1,b.业务员,业务2,c.业务员,业务3
from
#1 a
join
#2 b on a.ID=b.ID
join
#3 c on c.ID=b.ID
(select top 10 业务员, sum(业务1) from 表 group by 业务员 order by sum(业务1) desc),
(select top 10 业务员, sum(业务2) from 表 group by 业务员 order by sum(业务2) desc),
(select top 10 业务员, sum(业务3) from 表 group by 业务员 order by sum(业务3) desc)
select
a.业务员,业务1,b.业务员,业务2,c.业务员,业务3
from
(select 业务员,业务1,[ID]=row_number()over(order by 业务1) from T) a
join
(select 业务员,业务2,[ID]=row_number()over(order by 业务2) from T) b on a.ID=b.ID
join
(select 业务员,业务3,[ID]=row_number()over(order by 业务3) from T) c on c.ID=b.ID
where
a.ID<=0
(select top 10 业务1, 业务员 from 表 order by 业务1 desc) as aa
left join (select top 10 业务2, 业务员 from 表 order by 业务3 desc) as bb on 1=1
left join (select top 10 业务3, 业务员 from 表 order by 业务3 desc) as cc on 1=1
给每个业务加上一个排名从第一名1排到最后1名。
然后获取每个业务排名<10的业务员就可以了。
select 人员,业务1,(select count(*) from tempdb..业务表 a where a.业务1>=t.业务1)
from tempdb..业务表 tselect 人员,业务2,(select count(*) from tempdb..业务表 a where a.业务2>=t.业务2)
from tempdb..业务表 tselect 人员,业务3,(select count(*) from tempdb..业务表 a where a.业务3>=t.业务3)
from tempdb..业务表 t
select top 10 业务1, 业务员 from 表 order by 业务1 desc
union
select top 10 业务2, 业务员 from 表 order by 业务3 desc
union
select top 10 业务3, 业务员 from 表 order by 业务3 desc
/******************************************************************************/
/*回复：20080610002总：00065 */
/*主题：不确定表名，字段下的多重排序 */
/*作者：二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
create table [yw] ([ry] varchar(1),[y1] int,[y2] int,[y3] int)
insert into [yw] select 'a',2,4,6
insert into [yw] select 'b',3,3,8
insert into [yw] select 'c',4,2,6
insert into [yw] select 'd',5,1,9
insert into [yw] select 'e',6,4,6
insert into [yw] select 'f',2,7,7
insert into [yw] select 'g',3,6,null
insert into [yw] select 'g',2,6,null
insert into [yw] select 'i',2,6,null
go--代码--------------------------------------------------------------------------
declare @t varchar(100),@kc varchar(100),@c varchar(100),@mc int
declare @sql varchar(8000),@i int,@cc varchar(100)
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(100)
--表名：yw,主字段ry,其它字段'y1,y2,y3',名次数 5
select @t = 'yw',@kc = 'ry',@c='y1,y2,y3',@mc = 5,@i = 1
while charindex(',',@c+',',@i)>0
begin
select @cc = substring(@c,@i,charindex(',',@c+',',@i)-@i)
select @i = charindex(',',@c+',',@i)+1
select @sql = isnull(@sql+','+char(10),'')
+'(select xh=(select count(*) from ['+@t+'] where ['+@cc+'] > a.['+@cc+'] '
+ ' or (['+@cc+'] = a.['+@cc+'] '
+ ' and ['+@kc+'] >= a.['+@kc+']))'
+',['+@kc+'],['+@cc+'] from ['+@t+'] a) as ['+@cc+']'
if @s3 is null set @s3='['+@cc+'].xh '
if @s2 is null set @s2 = 'where '+@s3+'<='+rtrim(@mc)+' '
else set @s2 = @s2 + ' and '+@s3+'= ['+@cc+'].xh'
select @s1 = isnull(@s1+',','')+'['+@cc+'].['+@kc+'],['+@cc+']'
end
select @sql = 'select '+@s1 +' from '+char(10)+@sql+char(10)+@s2
exec(@sql)go/*结果--------------------------------------------------------------------------
ry y1 ry y2 ry y3
---- ----------- ---- ----------- ---- -----------
d 5 i 6 b 8
b 3 e 4 c 6
e 6 f 7 d 9
g 3 g 6 e 6
g 3 g 6 e 6
--清除------------------------------------------------------------------------*/
drop table yw
/******************************************************************************/
/*回复：20080610002总：00065 */
/*主题：不确定表名，字段下的多重排序 */
/*作者：二等草 */
/******************************************************************************/set nocount on--数据--------------------------------------------------------------------------
create table [yw] ([ry] varchar(1),[y1] int,[y2] int,[y3] int)
insert into [yw] select 'a',2,4,6
insert into [yw] select 'b',3,3,8
insert into [yw] select 'c',4,2,6
insert into [yw] select 'd',5,1,9
insert into [yw] select 'e',6,4,6
insert into [yw] select 'f',2,7,7
insert into [yw] select 'g',3,5,8
insert into [yw] select 'g',2,6,null
insert into [yw] select 'i',2,6,null
go--代码--------------------------------------------------------------------------
declare @t varchar(100),@kc varchar(100),@c varchar(100),@mc int
declare @sql varchar(8000),@i int,@cc varchar(100)
declare @s1 varchar(8000),@s2 varchar(8000),@s3 varchar(100)
select @t = 'yw',@kc = 'ry',@c='y1,y2,y3',@mc = 5,@i = 1
while charindex(',',@c+',',@i)>0
begin
select @cc = substring(@c,@i,charindex(',',@c+',',@i)-@i)
select @i = charindex(',',@c+',',@i)+1
select @sql = isnull(@sql+','+char(10),'')
+'(select xh=(select count(*) from ['+@t+'] where ['+@cc+'] > a.['+@cc+'] '
+ ' or (['+@cc+'] = a.['+@cc+'] '
+ ' and ['+@kc+'] >= a.['+@kc+']))'
+',['+@kc+'],['+@cc+'] from ['+@t+'] a) as ['+@cc+']'
if @s3 is null set @s3='['+@cc+'].xh '
if @s2 is null set @s2 = 'where '+@s3+'<='+rtrim(@mc)+' '
else set @s2 = @s2 + ' and '+@s3+'= ['+@cc+'].xh'
select @s1 = isnull(@s1+',','')+'['+@cc+'].['+@kc+'],['+@cc+']'
end
select @sql = 'select '+@s3+' 排名,'+@s1 +' from '+char(10)+@sql+char(10)+@s2+char(10)+'order by '+@s3
exec(@sql)go/*结果--------------------------------------------------------------------------
排名 ry y1 ry y2 ry y3
----------- ---- ----------- ---- ----------- ---- -----------
1 e 6 f 7 d 9
2 d 5 i 6 g 8
3 c 4 g 6 b 8
4 g 3 g 5 f 7
5 b 3 e 4 e 6 --清除------------------------------------------------------------------------*/
drop table yw