• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

关于读出数据的问题

makar138 2008-03-27 07:31:56
数据库中的两个表

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语句该怎么写呢?谢谢
...全文
14 点赞 收藏 7
写回复
7 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
pt1314917 2008-03-27


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 2008-03-27


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 2008-03-27

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 2008-03-27

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 2008-03-27

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 2008-03-27

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
回复
kaikai_kk 2008-03-27
结果和a表没关系啊

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

显示结果第6条怎么能读出来?
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2008-03-27 07:31
社区公告
暂无公告