表table结构如下:
create table(
id bigint identity(1,1),
title nvarchar(30),
type tinyint default 0 -- 值为0或1没有别的值
)
-- 表内容
id title type
1 a 0
2 b 1
3 c 1
4 d 1
5 e 0
6 f 0
7 a 0
8 b 1
9 c 1
10 d 1
11 e 0
12 f 0
13 a 0
14 b 1
--要求显示如下结果
14 b 1
10 d 1
9 c 1
13 a 0
12 f 0
11 e 0
7 a 0
--我的sql如下
SELECT top 3 from table where type = 1 union all
SELECT top 4 from table where type = 0 order by type desc,id desc
--可实际运行结果不是这样的,请高人指点
create table(
id bigint identity(1,1),
title nvarchar(30),
type tinyint default 0 -- 值为0或1没有别的值
)
-- 表内容
id title type
1 a 0
2 b 1
3 c 1
4 d 1
5 e 0
6 f 0
7 a 0
8 b 1
9 c 1
10 d 1
11 e 0
12 f 0
13 a 0
14 b 1
--要求显示如下结果
14 b 1
10 d 1
9 c 1
13 a 0
12 f 0
11 e 0
7 a 0
--我的sql如下
SELECT top 3 from table where type = 1 union all
SELECT top 4 from table where type = 0 order by type desc,id desc
--可实际运行结果不是这样的,请高人指点
union all
select * from (SELECT top 4 * from table1 where type = 0 order by id desc) t2
order by type desc,id desc
id bigint identity(1,1),
title nvarchar(30),
type tinyint default(0)
)
go
insert into table1(title,type)
select 'a',0
union all select 'b',1
union all select 'c',1
union all select 'd',1
union all select 'e',0
union all select 'f',0
union all select 'a',0
union all select 'b',1
union all select 'c',1
union all select 'd',1
union all select 'e',0
union all select 'f',0
union all select 'a',0
union all select 'b',1
go
select * from table1
go
select * from (SELECT top 3 * from table1 where type = 1 order by id desc) t1
union all
select * from (SELECT top 4 * from table1 where type = 0 order by id desc) t2
order by type desc,id desc
go
drop table table1
go
---------结果
(14 行受影响)
id title type
-------------------- ------------------------------ ----
1 a 0
2 b 1
3 c 1
4 d 1
5 e 0
6 f 0
7 a 0
8 b 1
9 c 1
10 d 1
11 e 0
12 f 0
13 a 0
14 b 1(14 行受影响)id title type
-------------------- ------------------------------ ----
14 b 1
10 d 1
9 c 1
13 a 0
12 f 0
11 e 0
7 a 0(7 行受影响)
union all
select * from (SELECT top 4 * from [Table] where type = 0 order by id desc)bbbb