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

2009-09-18 05:21:14 178 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

MySQL新手第一张表：从CREATE TABLE到INSERT实战指南在关系型数据库中，数据表是结构化存储的核心载体，其设计直接决定查询效率、数据一致性和系统可维护性。CREATE TABLE语句定义了字段类型、约束规则与物理存储特性，而INSERT INTO则实现数据写入，二者构成数据库操作的最小闭环。掌握主键（primary key）的唯一性与索引价值、utf8mb4字符集对中文及emoji的完整支持、NOT NULL与DEFAULT的空值治理策略，是避免乱码、截断、报错的关键技术基础。这些能力广泛应用于学生课程成绩信息实体表设计、电商订单建模、用户行为日志归档等典型场景阅读详情



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