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该字段进行排序后再插入,请问怎么写?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货