求一个表的sql查询语句

[Ianlan]

设计一个表的sql查询,想达到这样的效果，
要求按照三种不同方式排序好的查询结果,如（
按照时间排序1：a1,a2,a3, ... an.
按照代号排序2：b1,b2,b3, ... bn.
按照出生年月排序3：c1,c2,c3, ... cn.
）
再连接成这样的集：
a1,b1,c1,a2,b2,c2,a3,b3,c3,...an,bn,cn.
用一个sql语句完成，希望高手给于解答，要求用一个select 语句完成，
用指针的我已实现，用用一个select 语句我想了好久，解决不了，请教了，

[lynx1111]

select identity(int,1,1) flag1,除标识以外的字段 into #1 from xx order by 时间

select identity(int,1,1) flag1,除标识以外的字段 into #2 from xx order by 代号

select identity(int,1,1) flag1,除标识以外的字段 into #3 from xx order by 出生年月



select * from (
select *,flag1,1 flag2 from #1
union all
select *,flag1,2 from #2
union all
select *,flag1,3 from #3
) tem order by flag1,flag2

[pengdali]

select * from (select top 9999999 * from xx order by 时间) a
union all
select * from (select top 9999999 * from xx order by 代号) a
union all
select * from (select top 9999999 * from xx order by 出生年月) a

[friendliu]

select top N * from tablename order by time
union all
select top N * from tablename order by num
union all
select top N * from tablename order by birthday

[Ianlan]

to大力：我试过了，排出来的结果顺序是a1,a2,a3,...an,b1,b2,b3,...bn,c1,c2,c3,...cn
to无为：union all语句只有最后的select 能加order by子句

[nboys]

使用union连接连接多个数据集

[Ianlan]

使用union 相同的记录就只保留一个了，要求不是这样的,要用union all的

[pengdali]

select * from (
select * from (select top 9999999 *,编号 flag1,1 flag2 from xx order by 时间) a
union all
select * from (select top 9999999 *,编号,2 from xx order by 代号) a
union all
select * from (select top 9999999 *,编号,3 from xx order by 出生年月) a
) tem order by flag1,flag2

[Ianlan]

up

[pengdali]

select identity(int,1,1) flag1,* into #1 from xx order by 时间

select identity(int,1,1) flag1,* into #2 from xx order by 代号

select identity(int,1,1) flag1,* into #3 from xx order by 出生年月



select * from (
select *,flag1,1 flag2 from #1
union all
select *,flag1,2 from #2
union all
select *,flag1,3 from #3
) tem order by flag1,flag2

[lynx1111]

UP

[lynx1111]

[Ianlan]

to大力:无法使用 SELECT INTO 语句向表 '#1' 中添加标识列，该表中已有继承了标识属性的列 'Zid'。
而且希望用的是一个sql 查询

[Ianlan]

昨天下午开了一下午会，没能及时来看看。可是问题还没解决，
下面我给出实际的表
CREATE TABLE [dbo].[users] (
[userID] [int] IDENTITY (1, 1) NOT NULL ,
[name] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[psword] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[regitime] [smalldatetime] NULL ,
[phoneNO] [nvarchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[dwname] [nvarchar] (30) COLLATE Chinese_PRC_CI_AS NULL ,
[address] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[company] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO

分别以name,regitime,company排序，得到前面要求的sql语句

[pengdali]

select identity(int,1,1) flag1,cast(userid as int) userid,[name],psword,[regitime],[phoneNO],[dwname],[address],[company] into #1 from xx order by 时间

select identity(int,1,1) flag1,cast(userid as int) userid,[name],psword,[regitime],[phoneNO],[dwname],[address],[company] into #2 from xx order by 代号

select identity(int,1,1) flag1,cast(userid as int) userid,[name],psword,[regitime],[phoneNO],[dwname],[address],[company] into #3 from xx order by 出生年月



select * from (
select *,flag1,1 flag2 from #1
union all
select *,flag1,2 from #2
union all
select *,flag1,3 from #3
) tem order by flag1,flag2

[Ianlan]

错误提示：
服务器: 消息 8156，级别 16，状态 1，行 9
多次为 'tem' 指定了列 'flag1'。

最主要的时间太长了，42s呢，用指针一秒够了啊
过一会儿再来看看，再解决不了就结贴了，谢谢大家

[Ianlan]

补充：记录有30000条，前面的三条select费时9s

[Ianlan]

问题没有解决，怎么结贴啊