数据库字段列固定排序

最新推荐文章于 2025-08-16 16:27:13 发布

原创最新推荐文章于 2025-08-16 16:27:13 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

158 篇文章

订阅专栏

本文演示了如何使用SQL创建视图并展示部分数据，同时介绍了通过临时表整合特定条件的数据记录的方法。包括创建带有自增列主键的表、插入数据、使用临时表进行数据筛选等操作。

select * from PersonTable
--创建视图
create view myViewTest
as
select top 2 * from PersonTable

select * from myViewTest

select row_number()over(order by id asc) myId,* into #temp from PersonTable where name='张三' order by Name

insert into #temp select row_number()over(order by id asc) myId, Name,Age from PersonTable where name='李四' order by Name

insert into #temp select * from PersonTable where name='王五' order by Name

insert into #temp select * from PersonTable where name='赵六' order by Name

select * from #temp
select * from PersonTable

drop table #temp

--创建带有自增列主键的数据库表
CREATE TABLE [dbo].[PersonTable](
[ID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] [varchar](50) NULL,
[age] [int] NULL
)

truncate table PersonTable

insert into PersonTable (name,age) values ('张三',20)
insert into PersonTable (name,age) values ('李四',25)
insert into PersonTable (name,age) values ('王五',28)
insert into PersonTable (name,age) values ('赵六',29)

insert into PersonTable (name,age) values ('张三',30)
insert into PersonTable (name,age) values ('李四',35)
insert into PersonTable (name,age) values ('王五',38)
insert into PersonTable (name,age) values ('赵六',39)
select * from PersonTable
select top 0 * into #temp1 from PersonTable

--add #temp1 ID int identity(1,1)

insert into #temp1 select name,age from PersonTable where name='张三' order by Name

insert into #temp1 select name,age from PersonTable where name='李四' order by Name

insert into #temp1 select name,age from PersonTable where name='王五' order by Name

insert into #temp1 select name,age from PersonTable where name='赵六' order by Name

select * from #temp1

--drop table #temp1