导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

求一条查询语句

controstr 2007-12-06 11:57:07
表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
--可实际运行结果不是这样的,请高人指点
...全文
38 点赞 收藏 7
写回复
7 条回复
切换为时间正序
请发表友善的回复…
发表回复
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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告