3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
求一条查询语句
表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
--可实际运行结果不是这样的,请高人指点
...全文
请发表友善的回复…
发表回复
controstr 2007-12-06
叫你笑,
青锋-SS 2007-12-06
结贴给分.哈哈.
controstr 2007-12-06
谢谢,,哎~~~
青锋-SS 2007-12-06
鸟儿抢偶的分
chuifengde 2007-12-06
select * from (SELECT top 3 * from [Table] where type = 1 order by id desc)aaaa
union all
select * from (SELECT top 4 * from [Table] where type = 0 order by id desc)bbbb青锋-SS 2007-12-06
create table table1(
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 行受影响)青锋-SS 2007-12-06
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
union all
select * from (SELECT top 4 * from table1 where type = 0 order by id desc) t2
order by type desc,id desc