200分，SQL小游戏常有常新：进来玩玩！（测试语句已经给出直接运行）

[超级大笨狼]

有个表
id bid name 。。。
1 A a1
2 A a2
3 A a3
4 A a3
5 B b1
6 B b2
7 B b3
8 B b4
9 C c1
10 C c2
11 C c3
12 C c4

怎么用sql提取一个表象
id bid name
4 A a4
3 A a3
7 B b3
8 B b4
12 C c4
11 C c3
就是提取每个bid的前两个。。。

--创建表
CREATE TABLE [tb]
(
[id] [int] IDENTITY (1, 1) NOT NULL ,
[bid] [varchar] (20) NULL ,
[name] [varchar] (20) NULL
)
--创建测试数据
insert tb select 'A','a1'
union all select 'A','a2'
union all select 'A','a3'
union all select 'A','a4'
union all select 'B','b1'
union all select 'B','b2'
union all select 'B','b3'
union all select 'B','b4'
union all select 'C','c1'
union all select 'C','c2'
union all select 'C','c3'
union all select 'C','c4'
--察看测试数据
select * from tb
--查询语句，请写出

--删除测试
drop table tb

[wxiaol]

惭愧惭愧~~~~

学习学习！

呵呵

[filebat]

--有那么复杂吗？难道是我把题意理解错了?
select *
from tb as t1
where id in(
select top 2 id
from tb as t2
where t1.bid=t2.bid
order by id desc)
order by bid, id desc

[hj3793]

楼上的好，学习

[空心兜兜]

主要来学习！

[xiongzai]

学习……

[iuhxq]

--有那么复杂吗？难道是我把题意理解错了?
select *
from tb as t1
where id in(
select top 2 id
from tb as t2
where t1.bid=t2.bid
order by id desc)
order by bid, id desc


高手，filebat(Mark) 在SQL SERVER版一星了，呵呵

[2599]

学习!

[尚和空四]

好 东东

[子陌红尘]

呵呵，应该不算什么难题，还可以用楼下的查询：
----------------------------------------------------------
select
a.*
from
#t a,
(select
c.id
from
#t c,#t d
where
c.bid=d.bid and c.id<=d.id
group by
c.id
having
count(d.id)<3) b
where
a.id = b.id
order by
a.cid,a.id

[stefli]

filebat(Mark) 的思路比较清晰，嘻嘻

[超级大笨狼]

select
a.*
from
tb a,
(select
c.id
from
tb c,tb d
where
c.bid=d.bid and c.id<=d.id
group by
c.id
having
count(d.id)<3) b
where
a.id = b.id
order by
a.bid,a.id

[超级大笨狼]

对，其实就是filebat(Mark)做的这么简单。

[whb147]

select id,bid,name from tb where id in
(select max(id) as id from tb group by bid
union all
select max(id) as id from (select id,bid from tb where id not in (select max(id) from tb group by bid)) as a
group by bid)
order by bid asc,id desc

初步测试可以满足要求

[weidegong]

惭愧，用了很笨的办法

select * from tb where id in(
select max(id) as id from tb group by bid
union
select max(id) as id from (select tb.* from tb where id not in (select max(id) from tb group by bid)) tmp group by bid
)

[whb147]

select id,bid,[name] from tb a where
(select count(*) from tb where bid=a.bid and id>a.id)<2
order by bid,ID desc
这个效率会更高一点

[whb147]

郁闷
还有人吗？？

[weidegong]

佩服佩服，没想到SQL还能这样用

我看到题目的时候，第一想到的就是游标，惭愧啊

[风流才子]

经典，学习ING……

[Andy__Huang]

--创建表
CREATE TABLE [tb]
(
[id] [int] IDENTITY (1, 1) NOT NULL ,
[bid] [varchar] (20) NULL ,
[name] [varchar] (20) NULL
)
--创建测试数据
insert tb select 'A','a1'
union all select 'A','a2'
union all select 'A','a3'
union all select 'A','a4'
union all select 'B','b1'
union all select 'B','b2'
union all select 'B','b3'
union all select 'B','b4'
union all select 'C','c1'
union all select 'C','c2'
union all select 'C','c3'
union all select 'C','c4'
--察看测试数据
select * from tb
--查询语句，请写出

--删除测试
drop table tb

select a.* from tb a where id in(select top 2 id from tb where bid=a.bid)
--結果
id bid name
------------------------
1 A a1
2 A a2
5 B b1
6 B b2
9 C c1
10 C c2

[超级大笨狼]

小灰的的确经典！！

偶的比较笨，呵呵，不过写的过程我觉得非常好玩。

select * into #tb1 from tb a where not exists
( select bid from tb where bid=a.bid and [name]> a.[name])
select * into #tb2 from tb a where id not in (select id from #tb1)
select * from #tb1
UNION all
select * from #tb2 b where not exists
( select bid from #tb2 where bid=b.bid and [name]> b.[name])
order by bid
drop table #tb1
drop table #tb2