原问题在这里还没解决
http://community.csdn.net/Expert/topic/4724/4724033.xml?temp=.3273584如何把article表字段 articleid
字段 titleimg varchar(300)求一sql语句,取m条数据,把最靠上的n条titleimg不是null的排在上面,其他的m-n条不管titleimg是什么值随在这条数据之后是这样比如
create table #t(articleid int identity(1,1),titleimg varchar(20),title varchar(20))
insert into #t (titleimg,title) values (null,'a')
insert into #t (titleimg,title) values (null,'b')
insert into #t (titleimg,title) values ('','c')
insert into #t (titleimg,title) values ('img1.gif','d')
insert into #t (titleimg,title) values ('img2.gif','e')
insert into #t (titleimg,title) values ('','f')
select * from #t order by articleid desc
drop table #t要求的结果是如果指定按aritcleid asc排序则显示的结果是articleid titleimg title
----------- -------------------- --------------------
4 img1.gif d
1 NULL a
2 NULL b
3 c
5 img2.gif e
6 f如果按aritcleid desc排序则显示的结果是
articleid titleimg title
----------- -------------------- --------------------
5 img2.gif e
6 f
4 img1.gif d
3 c
2 NULL b
1 NULL a(所影响的行数为 6 行)
用文字说明就是把按照你的[任意指定的排序方式排序]之后,[位列最高的那个tielimg不是null也不是''的]放到最上面,其他的保持不变就是这么一个需求,能写出来吗?我自己写了一个存储过程,但是是不对的,只是让大家看看我的思路。
/*--news文章系统得到文章的列表,显示n个文章,有标题图片的m个排在上面,排序的方式是自由的,
不管按照哪种方式排序,按照这种方式排序的有标题图片的放在最上面,其余的也是按照这种方式排列,但是不考虑是否有标题图片。可以理解成要取n条文章,那么先从所有文章里面按照指定的排序方式取出(top m)个有标题图片的,然后还是按照这个排序方式取出n-m个不管有没有标题图片的,当然这m个和这n-m个是不能重复的,这是我的思路,不知道有没有更好的思路。
下面是我的实现,但是union all之后顺序不对了。请指点。
--*/
create procedure p_news_ArticleListGet
@n int,
@m int,
@orderby varchar(100)
as
select top @m * from (
select top @n * from #t order by @orderby desc) a where titleimg is not null and titleimg!=''
union all
select top @n * from #t where articleid not in (
select top @m articleid from (
select top @n * from #t order by @orderby desc
) a where titleimg is not null and titleimg!=''
) order by @orderby descgo
http://community.csdn.net/Expert/topic/4724/4724033.xml?temp=.3273584如何把article表字段 articleid
字段 titleimg varchar(300)求一sql语句,取m条数据,把最靠上的n条titleimg不是null的排在上面,其他的m-n条不管titleimg是什么值随在这条数据之后是这样比如
create table #t(articleid int identity(1,1),titleimg varchar(20),title varchar(20))
insert into #t (titleimg,title) values (null,'a')
insert into #t (titleimg,title) values (null,'b')
insert into #t (titleimg,title) values ('','c')
insert into #t (titleimg,title) values ('img1.gif','d')
insert into #t (titleimg,title) values ('img2.gif','e')
insert into #t (titleimg,title) values ('','f')
select * from #t order by articleid desc
drop table #t要求的结果是如果指定按aritcleid asc排序则显示的结果是articleid titleimg title
----------- -------------------- --------------------
4 img1.gif d
1 NULL a
2 NULL b
3 c
5 img2.gif e
6 f如果按aritcleid desc排序则显示的结果是
articleid titleimg title
----------- -------------------- --------------------
5 img2.gif e
6 f
4 img1.gif d
3 c
2 NULL b
1 NULL a(所影响的行数为 6 行)
用文字说明就是把按照你的[任意指定的排序方式排序]之后,[位列最高的那个tielimg不是null也不是''的]放到最上面,其他的保持不变就是这么一个需求,能写出来吗?我自己写了一个存储过程,但是是不对的,只是让大家看看我的思路。
/*--news文章系统得到文章的列表,显示n个文章,有标题图片的m个排在上面,排序的方式是自由的,
不管按照哪种方式排序,按照这种方式排序的有标题图片的放在最上面,其余的也是按照这种方式排列,但是不考虑是否有标题图片。可以理解成要取n条文章,那么先从所有文章里面按照指定的排序方式取出(top m)个有标题图片的,然后还是按照这个排序方式取出n-m个不管有没有标题图片的,当然这m个和这n-m个是不能重复的,这是我的思路,不知道有没有更好的思路。
下面是我的实现,但是union all之后顺序不对了。请指点。
--*/
create procedure p_news_ArticleListGet
@n int,
@m int,
@orderby varchar(100)
as
select top @m * from (
select top @n * from #t order by @orderby desc) a where titleimg is not null and titleimg!=''
union all
select top @n * from #t where articleid not in (
select top @m articleid from (
select top @n * from #t order by @orderby desc
) a where titleimg is not null and titleimg!=''
) order by @orderby descgo
create table t(articleid int identity(1,1),titleimg varchar(20),title varchar(20))
go
insert into t (titleimg,title) values (null,'a')
insert into t (titleimg,title) values (null,'b')
insert into t (titleimg,title) values ('','c')
insert into t (titleimg,title) values ('img1.gif','d')
insert into t (titleimg,title) values ('img2.gif','e')
insert into t (titleimg,title) values ('','f')
go--存储过程
create procedure p_news_ArticleListGet
@n int,
@m int,
@orderby varchar(100),
@desc varchar(100)='asc' --加个参数
as
declare @sql varchar(3000)
set @sql='
select articleid,titleimg,title
from (
select * from (select top '+cast(@n as varchar)+' *,1 as ordertype from t where titleimg is not null and titleimg!='''' order by '+@orderby+' '+@desc+') as a
union all
select * from (
select top '+cast(@m-@n as varchar)+' *,2 as ordertype from t
where articleid not in (
select top '+cast(@n as varchar)+' articleid from t
where titleimg is not null and titleimg!=''''
order by '+@orderby+' '+@desc+'
)
order by '+@orderby+' '+@desc+'
) as b
) as c
order by ordertype,'+@orderby+' '+@desc--print @sql
exec (@sql)go--调用
exec p_news_ArticleListGet 1,5,'articleid'--结果
articleid titleimg title
----------- -------------------- --------------------
4 img1.gif d
1 NULL a
2 NULL b
3 c
5 img2.gif e(所影响的行数为 5 行)--调用
exec p_news_ArticleListGet 1,6,'articleid','desc'--结果
articleid titleimg title
----------- -------------------- --------------------
5 img2.gif e
6 f
4 img1.gif d
3 c
2 NULL b
1 NULL a(所影响的行数为 6 行)
insert into #t (titleimg,title) values (null,'a')
insert into #t (titleimg,title) values (null,'b')
insert into #t (titleimg,title) values ('','c')
insert into #t (titleimg,title) values ('img1.gif','d')
insert into #t (titleimg,title) values ('img2.gif','e')
insert into #t (titleimg,title) values ('','f')select * from #t order by case articleid when null then 1 else 0 end descdrop table #t
----------- -------------------- --------------------
1 NULL a
2 NULL b
3 c
4 img1.gif d
5 img2.gif e
6 f
insert into #t (titleimg,title) values (null,'a')
insert into #t (titleimg,title) values (null,'b')
insert into #t (titleimg,title) values ('','c')
insert into #t (titleimg,title) values ('img1.gif','d')
insert into #t (titleimg,title) values ('img2.gif','e')
insert into #t (titleimg,title) values ('','f')
--select * from #t order by articleid descdeclare @ID intselect top 1 @id=articleid from #t where (titleimg is not null) and (titleimg <>'') order by articleid desc
declare @t table(articleid int,titleimg varchar(20),title varchar(20))
declare @r table(articleid int,titleimg varchar(20),title varchar(20))insert into @t
select top 1 * from #t where (titleimg is not null) and (titleimg <>'') order by articleid descinsert into @r
select * from #t where articleid not in(@id) order by articleid desc
select * from @t
union all
select * from #t where articleid not in(@id)
select * from @t
union all
select * from @rdrop table #t--------------------------------
1 NULL a
2 NULL b
3 c
4 img1.gif d
6 f
-------------------------
5 img2.gif e
6 f
4 img1.gif d
3 c
2 NULL b
1 NULL a