insert to table select * from table 无法排序!

[unixpeter]

GO
/****** 对象:  Table [dbo].[t_class]    脚本日期: 09/18/2009 17:19:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[t_class](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[name] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
	[parentID] [int] NULL,
	[Orders] [int] NULL,
	[updatedate] [datetime] NULL CONSTRAINT [DF_t_class_updatedate]  DEFAULT (getdate()),
	[addDate] [datetime] NULL,
	[intro] [nvarchar](1024) COLLATE Chinese_PRC_CI_AS NULL,
	[isRecommand] [bit] NULL,
	[isDisplayAds] [bit] NULL,
	[filepath] [varchar](128) COLLATE Chinese_PRC_CI_AS NULL,
	[type] [nvarchar](50) COLLATE Chinese_PRC_CI_AS NULL,
 CONSTRAINT [PK_t_class] PRIMARY KEY CLUSTERED 
(
	[id] ASC
) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

--SQL SERVER 2000
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[p_classQuery]
	-- Add the parameters for the stored procedure here
	@parentID int
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(100))
	DECLARE @Level int
	SET @Level=0
	INSERT @t_Level SELECT id,@Level,right('000'+ltrim(ID),3)
	FROM t_class
	WHERE parentID =@parentID  -------modify 
	WHILE @@ROWCOUNT>0
		BEGIN
			SET @Level=@Level+1
			INSERT @t_Level SELECT a.ID,@Level,ltrim(b.Sort)+right('000'+ltrim(a.ID),3)
			FROM t_class a,@t_Level b	
			WHERE a.parentID=b.ID
			AND b.Level=@Level-1
			order by a.orders desc		
		END
	--显示结果
	SELECT a.*,b.level,b.sort
	FROM t_class a,@t_Level b
	WHERE a.ID=b.ID
	ORDER BY b.Sort
END

为什么order by a.orders desc不起作用？我想在插入数据之前对orders该字段进行排序后再插入，请问怎么写？

[unixpeter]

数据库是 SQL SERVER 2000的，会不会是数据库不支持这样的写法？

[csdyyr]

select 时再排序不行？

[翼帆]

貌似可以吧。。要是万一不行，就select top 10000 ... order by ...

[xiequan2]

insert to table select * from table order by order by a.orders desc正常是启作用的

至于你的为什么不起作用，估计是每一条一条的insert ,order by a.orders desc了也没有用

[billpu]

数据库里的记录本身是无序排列,要靠标识

[unixpeter]

那要怎么写才能现实先按ORDERS排序后再插进去？

[unixpeter]

top

[csdyyr]

临时表+聚集索引(ORDERS)