if object_id('tb')is not null drop table tb go create table tb(ID int identity ,推荐排序 int,[name] varchar(10)) insert tb select 1,'A' insert tb select 5,'B' insert tb select 3,'C' insert tb select 2,'F' insert tb select 11,'G' insert tb select 12,'H' insert tb select 9,'I' select * from tb order by case when id in(1,2,3,4,5) then 推荐排序 else id end /*ID 推荐排序 name ----------- ----------- ---------- 1 1 A 4 2 F 3 3 C 2 5 B 6 12 H 7 9 I 5 11 G*/
select *, tid=1 from 文章表 where id in (select top 5 id from 文章表 order by 推荐 desc) union all select *, tid=2 from 文章表 where id not in (select top 5 id from 文章表 order by 推荐 desc) order by tid, id desc
order by case when
参考这样的写法.select * from (select top 100 * from authors where left(au_lname,1) = 'b' order by au_id) t union all select * from (select top 100 * from authors where left(au_lname,1) <> 'b' order by au_id desc) t
这个问题select 2次比较实用,推荐管推荐的,一般管一般的。
select 推荐,* from 文章表 where id in (select top 5 id from 文章表 order by 推荐 desc ) union all select 0 as 推荐 ,* from 文章表 where id not in (select top 5 id from 文章表 order by 推荐 desc ) order by 推荐 desc , id
select top 5 * from 文章表 order by 推荐 desc union all select * from 文章表 where id not in(select top 5 * from 文章表 order by 推荐 desc) order by id
select * from article where tjflag = 1 union all select * from article where tjflag != 1 order by id desc
前面一个写错了 应该是:select * from article where tjflag = 1 order by id desc union all select * from article where tjflag != 1 order by id desc
--测试数据 declare @t table ([id] int,tjflag int) insert @t select 1,0 union select 2,1 union select 3,1 union select 4,0 union select 5,1 union select 6,1 union select 7,0 union select 8,1 union select 9,1 union select 10,0 union select 11,1 union select 12,0 --查询 select * from @t order by case when [id] in (select top 5 [id] from @t order by tjflag desc,[id]) then 'a'+right('00000000'+cast([id] as varchar(10)),8) else 'b'+right('00000000'+cast([id] as varchar(10)),8) end /* id tjflag ----------- ----------- 2 1 3 1 5 1 6 1 8 1 1 0 4 0 7 0 9 1 10 0 11 1 12 0 */
没有显示全部的记录,不只有5条tjflag=1的记录
create table #tb2 (ID int identity(1,1) primary key,IsElite bit,[name] varchar(10)) insert #tb2 select 1,'A' insert #tb2 select 1,'B' insert #tb2 select 1,'C' insert #tb2 select 1,'F' insert #tb2 select 1,'G' insert #tb2 select 0,'H' insert #tb2 select 0,'I' select * from #tb2select top 5 * from #tb2 where IsElite=1 union all select * from #tb2 order by ID desc
--测试数据 declare @t table ([id] int,tjflag int) insert @t select 1,0 union select 2,1 union select 3,1 union select 4,0 union select 5,1 union select 6,1 union select 7,0 union select 8,1 union select 9,1 union select 10,0 union select 11,1 union select 12,0 --查询 select * from @t order by case when [id] in (select top 5 [id] from @t order by tjflag desc,[id]) then 'b'+right('00000000'+cast([id] as varchar(10)),8) else 'a'+right('00000000'+cast([id] as varchar(10)),8) end desc /* id tjflag ----------- ----------- 8 1 6 1 5 1 3 1 2 1 12 0 11 1 10 0 9 1 7 0 4 0 1 0 */
说实在的20楼的大哥写的东西我看不懂select top 5 * from article where tjflag = 1 order by id desc union all select * from article where id in (select id from article where tjflag != 1) order by id desc
go
create table tb(ID int identity ,推荐排序 int,[name] varchar(10))
insert tb select 1,'A'
insert tb select 5,'B'
insert tb select 3,'C'
insert tb select 2,'F'
insert tb select 11,'G'
insert tb select 12,'H'
insert tb select 9,'I'
select * from tb order by case when id in(1,2,3,4,5) then 推荐排序 else id end
/*ID 推荐排序 name
----------- ----------- ----------
1 1 A
4 2 F
3 3 C
2 5 B
6 12 H
7 9 I
5 11 G*/
where id in (select top 5 id from 文章表 order by 推荐 desc)
union all
select *, tid=2 from 文章表
where id not in (select top 5 id from 文章表 order by 推荐 desc)
order by tid, id desc
union all
select * from (select top 100 * from authors where left(au_lname,1) <> 'b' order by au_id desc) t
select 推荐,* from 文章表
where id in (select top 5 id from 文章表 order by 推荐 desc )
union all
select 0 as 推荐 ,* from 文章表
where id not in (select top 5 id from 文章表 order by 推荐 desc )
order by 推荐 desc , id
union all
select * from 文章表 where id not in(select top 5 * from 文章表 order by 推荐 desc) order by id
表
article
id tjflag
1 0
2 1
3 1
4 0
5 1
6 1
7 1
8 0
9 1
10 0
按id排序 前5条记录tjflag = 1
表:
article
id tjflag
1 0
2 1
3 1
4 0
5 1
6 1
7 0
8 1
9 1
10 0
11 1
12 0所有记录按id排序,但是前5条记录 tjflag =1
select * from article where tjflag = 1
union all
select * from article where tjflag != 1 order by id desc
应该是:select * from article where tjflag = 1 order by id desc
union all
select * from article where tjflag != 1 order by id desc
declare @t table ([id] int,tjflag int)
insert @t
select 1,0
union select 2,1
union select 3,1
union select 4,0
union select 5,1
union select 6,1
union select 7,0
union select 8,1
union select 9,1
union select 10,0
union select 11,1
union select 12,0
--查询
select * from @t
order by case when [id] in (select top 5 [id] from @t order by tjflag desc,[id])
then 'a'+right('00000000'+cast([id] as varchar(10)),8)
else 'b'+right('00000000'+cast([id] as varchar(10)),8) end
/*
id tjflag
----------- -----------
2 1
3 1
5 1
6 1
8 1
1 0
4 0
7 0
9 1
10 0
11 1
12 0
*/
没有显示全部的记录,不只有5条tjflag=1的记录
insert #tb2 select 1,'A'
insert #tb2 select 1,'B'
insert #tb2 select 1,'C'
insert #tb2 select 1,'F'
insert #tb2 select 1,'G'
insert #tb2 select 0,'H'
insert #tb2 select 0,'I'
select * from #tb2select top 5 * from #tb2 where IsElite=1
union all
select * from #tb2 order by ID desc
declare @t table ([id] int,tjflag int)
insert @t
select 1,0
union select 2,1
union select 3,1
union select 4,0
union select 5,1
union select 6,1
union select 7,0
union select 8,1
union select 9,1
union select 10,0
union select 11,1
union select 12,0
--查询
select * from @t
order by case when [id] in (select top 5 [id] from @t order by tjflag desc,[id])
then 'b'+right('00000000'+cast([id] as varchar(10)),8)
else 'a'+right('00000000'+cast([id] as varchar(10)),8) end desc
/*
id tjflag
----------- -----------
8 1
6 1
5 1
3 1
2 1
12 0
11 1
10 0
9 1
7 0
4 0
1 0
*/
union all
select * from article where id in (select id from article where tjflag != 1) order by id desc