select top 7 B.catname,A.* from category A left join article B
on A.catid=B.catid
请试一试!
on A.catid=B.catid
请试一试!
解决方案 »
- sql2008能通过T-SQL语句创建dbf格式的表吗?
- 这句动态语句应该怎么写?
- 批量删除数据库中的一段代码
- 请问高手,把二进制的txt文件导入sql server 2005有哪几种方法?
- 把某些列转成行
- 跟着前辈大牛们学习了这么久,CSDN的处女贴,来个99乘法表,像大牛们学习!
- 如何理解我的数据库db与master、msdb、model之间的关系及如何备份及还原master、msdb、model
- 请问如何在一个表里限定只显示某段日期范围内的记录?
- 关于数据库设计的问题?
- 能否用T-SQL获取文件的路径?
- 如果存储过程的结果是一个表,那么我怎样才能将两个存储过程的结果表做inner join联接呢?谢谢
- 请问高手如何将数据库中指定的表备份成mdf格式!
select top 7 B.catname,A.* from article A left join category B
on A.catid=B.catid
select top 7 * from [article]
where catid=(select catid from [category] where catname like '%诗歌%')
a.*
from
article a,
category c
where
a.catid = c.catid
and
c.catname in('诗歌','小说','散文')
and
a.id in(select top 7 id from article where catid=a.catid)
order by
a.catid,a.id
--创建测试环境
create table [article] (id int,catid int,author varchar(5),title varchar(10),body varchar(200))
insert into [article] select 1,2,'鲁迅','狂人日记','今天我吃人了'
union all select 2,1,'冯至','十四行集','给我狭窄的心,一个大的宇宙'
union all select 3,1,'于坚','避雨之树','大雨……'create table [category] (catid int,catname varchar(10))
insert into [category] select 1,'诗歌'
union all select 2,'小说'
union all select 3,'散文'
union all select 4,'戏剧'--查询语句
select top 7 * from [article]
where catid in (select catid from [category] where charindex(','+catname+',',',诗歌,小说,')>0)--结果id catid author title body
----------- ----------- ------ --------
1 2 鲁迅 狂人日记 今天我吃人了
2 1 冯至 十四行集 给我狭窄的心,一个大的宇宙
3 1 于坚 避雨之树 大雨……(所影响的行数为 3 行)--删除测试环境
drop table [article]
drop table [category]
where (select count(1) from [article] where catid=t.catid and id<=t.id)<=7
order by catid,id
select top 7 B.catname,A.* from article A left join category B
on A.catid=B.catid where catname like'%诗歌%'
union all
select top 7 B.catname,A.* from article A left join category B
on A.catid=B.catid where catname like'%散文%'
union all
select top 7 B.catname,A.* from article A left join category B
on A.catid=B.catid where catname like'%小说%'
union all
select top 7 B.catname,A.* from article A left join category B
on A.catid=B.catid where catname like'%戏剧%'
……
select * from [article] t
where (select count(1) from [article] where catid=t.catid and id<=t.id)<=7 and
catid in (select catid from [category] where catname in ('诗歌','小说','散文'))
order by catid,id
select catid from category where catname in ('诗歌','小说')
declare @catid varchar(20)
open curAfetch next from curA into @catid
while @@fetch_status = 0
begin
insert into #article select top 7 * from article where catid=@catid
fetch next from curA into @catid
end---楼主要的结果
select * from #article