select * from t where t.b = 2 or t.b = 3 order by b
Oracle 中可以用 rank 分析函数:select * from ( select a, b, c, rank() over (partion by b) as rnk from xx ) t where t.rnk >= 3 and t.rnk <= 11
declare @tb table (a int,b int,c int) insert into @tb select 1,2,3 insert into @tb select 4,2,5 insert into @tb select 3,3,3 insert into @tb select 2,1,3 insert into @tb select 2,3,6 insert into @tb select 4,1,7 insert into @tb select 8,4,1 insert into @tb select 9,4,6 insert into @tb select 4,3,2 insert into @tb select 4,3,6 insert into @tb select 3,2,4 insert into @tb select 4,3,3select * from @tb a where exists( select 1 from @tb where b=a.b group by b having count(1)>2) order by b,aa b c 1 2 3 3 2 4 4 2 5 2 3 6 3 3 3 4 3 2 4 3 6 4 3 3排序没排好 soryy
MS SQL 2005 也有相同的函数。
select * from ( select a, b, c, rank() over (order by b) as rnk from xx ) t where t.rnk > = 3 and t.rnk <= 11 这样就可以了。
感谢各位的帮忙,特别是fangxinggood.应该用DENSE_RANK()可以解决,
select * from ( select a, b, c, rank() over (order by b) as rnk from xx ) t where t.rnk > = 3 and t.rnk <= 11
join
(
select top @x2 b from xx
where b not in
(
select top @x1 b from xx group by b
)
group by b
)tmp on xx.b=tmp.b
a b c
1 2 3
4 2 5
3 3 3
2 1 3
2 3 6
4 1 7
8 4 1
9 4 6
4 3 2
4 3 6
3 2 4
4 3 3
我想找出
1 2 3
3 2 4
4 2 5
4 3 2
4 3 6
2 3 6
3 3 3
4 3 3
from
(
select a, b, c, rank() over (partion by b) as rnk from xx
) t
where t.rnk >= 3 and t.rnk <= 11
insert into @tb select 1,2,3
insert into @tb select 4,2,5
insert into @tb select 3,3,3
insert into @tb select 2,1,3
insert into @tb select 2,3,6
insert into @tb select 4,1,7
insert into @tb select 8,4,1
insert into @tb select 9,4,6
insert into @tb select 4,3,2
insert into @tb select 4,3,6
insert into @tb select 3,2,4
insert into @tb select 4,3,3select * from @tb a
where exists(
select 1 from @tb where b=a.b
group by b having count(1)>2)
order by b,aa b c
1 2 3
3 2 4
4 2 5
2 3 6
3 3 3
4 3 2
4 3 6
4 3 3排序没排好 soryy
select *
from
(
select a, b, c, rank() over (order by b) as rnk from xx
) t
where t.rnk > = 3 and t.rnk <= 11
这样就可以了。
from
(
select a, b, c, rank() over (order by b) as rnk from xx
) t
where t.rnk > = 3 and t.rnk <= 11