关于读出数据的问题

[makar138]

数据库中的两个表

a表结构以及数据

aid aname
1 dfasfd
2 dfff
3 eewrr
4 jkjkg
.
.

b表结构以及数据

bid bname aid bSet btime
1 rtrtr 1 0
2 trtt 1 1 2008-03-04
3 tret 1 1 2008-04-01
4 tretrt 1 1 2008-04-20
5 jjhj 2 0
6 kkjj 4 0
7 spdfff 2 1 2008-04-01
.
.
.

读出B表中的数据，要求如下：
1.读出b表中的前三条
2.相同的aid只读出一条
3.优先读出bset=1，并且btime大于"今天"日期的数据
4.如果没有bset=1的时候，读出bset=0的数据
5.最后按bid升序排序（order by bid asc）

按上面的要求，比如今天时间是2008-03-26，则显示出来的数据是

bid bname aid bSet btime

3 tret 1 1 2008-04-01
6 kkjj 4 0
7 spdfff 2 1 2008-04-01

这个SQL语句该怎么写呢？谢谢

[kaikai_kk]

结果和a表没关系啊

第3点要求,btime大于'今天'日期,

显示结果第6条怎么能读出来?

[pt1314917]

declare @t table(bid int,bname varchar(10),aid int,bset int,btime datetime)
insert into @t select 1,'rtrtr',1,0,null
insert into @t select 2,'trtt',1,1,'2008-03-04'
insert into @t select 3,'tret',1,1,'2008-04-01'
insert into @t select 4,'tretrt',1,1,'2008-04-20'
insert into @t select 5,'jjhj',2,0,null
insert into @t select 6,'kkjj',4,0,null
insert into @t select 7,'spdfff',2,1,'2008-04-01'
insert into @t select 8,'spdfff',5,1,'2008-04-01'

select top 3 * from @t a where bid in
(select top 1 bid from @t where aid=a.aid 
and isnull(btime,getdate())>=getdate()
order by bset desc,bid )
order by bid

[pt1314917]

declare @t table(bid int,bname varchar(10),aid int,bset int,btime datetime)
insert into @t select 1,'rtrtr',1,0,null
insert into @t select 2,'trtt',1,1,'2008-03-04'
insert into @t select 3,'tret',1,1,'2008-04-01'
insert into @t select 4,'tretrt',1,1,'2008-04-20'
insert into @t select 5,'jjhj',2,0,null
insert into @t select 6,'kkjj',4,0,null
insert into @t select 7,'spdfff',2,1,'2008-04-01'
insert into @t select 8,'spdfff',5,1,'2008-04-01'

select top 3 * from @t a where bid in
(select top 1 bid from @t where aid=a.aid 
and isnull(btime,getdate())>=getdate()
order by bset desc,bid )
order by bid

[pt1314917]

declare @t table(bid int,bname varchar(10),aid int,bset int,btime datetime)
insert into @t select 1,'rtrtr',1,0,null
insert into @t select 2,'trtt',1,1,'2008-03-04'
insert into @t select 3,'tret',1,1,'2008-04-01'
insert into @t select 4,'tretrt',1,1,'2008-04-20'
insert into @t select 5,'jjhj',2,0,null
insert into @t select 6,'kkjj',4,0,null
insert into @t select 7,'spdfff',2,1,'2008-04-01'
insert into @t select 8,'spdfff',5,1,'2008-04-01'

select top 3 * from @t a where bid in
(select top 1 bid from @t where aid=a.aid 
and isnull(btime,getdate())>=getdate()
order by bset desc,bid )
order by bid

[pt1314917]

declare @t table(bid int,bname varchar(10),aid int,bset int,btime datetime)
insert into @t select 1,'rtrtr',1,0,null
insert into @t select 2,'trtt',1,1,'2008-03-04'
insert into @t select 3,'tret',1,1,'2008-04-01'
insert into @t select 4,'tretrt',1,1,'2008-04-20'
insert into @t select 5,'jjhj',2,0,null
insert into @t select 6,'kkjj',4,0,null
insert into @t select 7,'spdfff',2,1,'2008-04-01'
insert into @t select 8,'spdfff',5,1,'2008-04-01'

select top 3 * from @t a where bid in
(select top 1 bid from @t where aid=a.aid 
and isnull(btime,getdate())>=getdate()
order by bset desc,bid )
order by bid

[pt1314917]

declare @t table(bid int,bname varchar(10),aid int,bset int,btime datetime)
insert into @t select 1,'rtrtr',1,0,null
insert into @t select 2,'trtt',1,1,'2008-03-04'
insert into @t select 3,'tret',1,1,'2008-04-01'
insert into @t select 4,'tretrt',1,1,'2008-04-20'
insert into @t select 5,'jjhj',2,0,null
insert into @t select 6,'kkjj',4,0,null
insert into @t select 7,'spdfff',2,1,'2008-04-01'


select top 3 * from @t a where bid in
(select top 1 bid from @t where aid=a.aid 
and isnull(btime,getdate())>=getdate()
order by bset desc,bid )
order by bid

[pt1314917]

declare @t table(bid int,bname varchar(10),aid int,bset int,btime datetime)
insert into @t select 1,'rtrtr',1,0,null
insert into @t select 2,'trtt',1,1,'2008-03-04'
insert into @t select 3,'tret',1,1,'2008-04-01'
insert into @t select 4,'tretrt',1,1,'2008-04-20'
insert into @t select 5,'jjhj',2,0,null
insert into @t select 6,'kkjj',4,0,null
insert into @t select 7,'spdfff',2,1,'2008-04-01'


select top 3 * from @t a where bid in
(select top 1 bid from @t where aid=a.aid 
and isnull(btime,getdate())>=getdate()
order by bset desc,bid )
order by bid