select 字段1,字段2 from (select 字段1,字段2, dense_rank() over (partition by 字段1 order by 字段2 desc) rank_no from 表名) where rank_no <= 3 order by 字段1,字段2;
select 字段1,字段2 from (select 字段1,字段2, dense_rank() over (partition by 字段1 order by 字段2 desc) rank_no from 表名) where rank_no <= 3 order by 字段1,字段2;这个可能大于三条,如字段2有并列的表况,就会; 用下面这个可以解决问题: select 字段1,字段2 from (select 字段1,字段2, row_number() over (partition by 字段1 order by 字段2 desc) flag from 表名) where flag <= 3 order by 字段1,字段2
这里真冷清,我没用过oracel,这是用ms sql server做的,你借鉴一下create table tb(id int identity, services nvarchar(5), portid int, pol nvarchar(5), pod nvarchar(5)) insert tb select 'b2', 1, '盐田', NULL union all select 'b2' , 2 , '香港', NULL union all select 'b2' , 3, NULL, '高雄' union all select 'b2' , 4 , NULL, '关岛' union all select 'c1' , 1 , '盐田', NULL union all select 'c1' , 2 , '盐田', NULL union all select 'c1' , 3 , '盐田', NULL union all select 'c1', 4 , NULL, '关岛' union all select 'c1' , 5, NULL, '纽约' union all select 'm1' , 1, '香港', NULL union all select 'm1' , 2, NULL, '关岛' Select distinct services into #tempTable From tb declare @i int set @i=1 declare @sql nvarchar(2000) set @sql='Select distinct services into #temp From tb 'while @i<=(select count(*) from #tempTable) begin if @i<>1 begin set @sql=@sql + 'union all ' end set @sql=@sql + 'Select * From tb a Where id in (Select top 3 id From tb b where b.services=(Select Top 1 * From #temp where services in (Select top ' +convert(nvarchar,@i)+ ' * From #temp) Order by services Desc) and b.id in (Select Top 3 id From tb c where c.services=b.services) )' set @i=@i+1 end exec (@sql) drop table #tempTable,tb
where rank_no <= 3 order by 字段1,字段2;
select 字段1,字段2 from (select 字段1,字段2, dense_rank() over (partition by 字段1 order by 字段2 desc) rank_no from 表名)
where rank_no <= 3 order by 字段1,字段2;这个可能大于三条,如字段2有并列的表况,就会;
用下面这个可以解决问题:
select 字段1,字段2 from (select 字段1,字段2, row_number() over (partition by 字段1 order by 字段2 desc) flag from 表名)
where flag <= 3 order by 字段1,字段2
insert tb
select 'b2', 1, '盐田', NULL
union all select 'b2' , 2 , '香港', NULL
union all select 'b2' , 3, NULL, '高雄'
union all select 'b2' , 4 , NULL, '关岛'
union all select 'c1' , 1 , '盐田', NULL
union all select 'c1' , 2 , '盐田', NULL
union all select 'c1' , 3 , '盐田', NULL
union all select 'c1', 4 , NULL, '关岛'
union all select 'c1' , 5, NULL, '纽约'
union all select 'm1' , 1, '香港', NULL
union all select 'm1' , 2, NULL, '关岛' Select distinct services into #tempTable From tb
declare @i int
set @i=1
declare @sql nvarchar(2000)
set @sql='Select distinct services into #temp From tb 'while @i<=(select count(*) from #tempTable)
begin
if @i<>1
begin
set @sql=@sql + 'union all '
end
set @sql=@sql + 'Select * From tb a
Where id in
(Select top 3 id From tb b
where b.services=(Select Top 1 * From #temp where services in (Select top ' +convert(nvarchar,@i)+ ' * From #temp) Order by services Desc)
and b.id in (Select Top 3 id From tb c where c.services=b.services)
)'
set @i=@i+1
end
exec (@sql)
drop table #tempTable,tb