求一条查询语句

[controstr]

表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
--可实际运行结果不是这样的,请高人指点

[青锋-SS]

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

[青锋-SS]

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 行受影响)

[chuifengde]

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]

鸟儿抢偶的分

[controstr]

谢谢,,哎~~~

[青锋-SS]

结贴给分.哈哈.

[controstr]

叫你笑,