常见sql高级运用

edpdennis

墨衍会员 · AI 创作全网分发码龄19年

关注

原创最新推荐文章于 2025-08-19 13:46:59 发布 751 阅读 AI 写同款· GEO 优化 ›

AI权益加码！Claude Code、Cursor等20+工具免费用！购周边限时加赠Coding Plan Lite，畅享主流AI工具！学习进阶更高效！阅读详情

-- 常见 sql 高级运用

-- 本文主要讲述 sql server2005 在日常的运用

-- 我们建立一张部门表进行测试

if exists ( select * from sysobjects where [name]= 'partment' )

begin

drop table partment

end

create table partment

(

pid int identity ( 1, 1),

pname varchar ( 30) not null,

pperson int not null

)

-- 建立约束

alter table partment

add constraint primary key ( pid)

-- 添加数据

insert partment

select ' 技术部 ' , 80

union all select ' 行政部 ' , 20

union all select ' 企管部 ' , 70

union all select ' 商管部 ' , 60

select * from partment

-- 删除表中重复的行

/* 分析：

删除重复的行的过程中，主要在于找到数据表中的重复列，然后进行删除。

对本列在于找到部门名称相同的数据 , 通过聚合函数 max 、 group by 和 having 可以方便的找出重复的行。

select max(pid) as 部门编号 from partment group by pname having count(pname)>0

这样就有方法解决了

-- 解决方法

delete from partment

where pid not in ( select min ( pid) from partment group by pname having count ( pname)> 0)

在 ms sql 中我们知道 max 和 min 相反可以得到最大和最小的记录这里看需求，另外 top 亦可以得到 - 个结果，

这样我们就可以演变出其他方法。不过在 sql 里面不提倡运用 not 这样会降低效率。所有我们在这里运用 exists 来查询

提高效率。

-- 解决方法

delete p from partment as p

where exists( select pid from partment where pname = p. pname and pid > p. pid)

/* 在日常运用中会有客户要求报表不仅可以横看而且也可以竖看，这样我们就要将行和列转换，

由于表结构不能支持本例，这样我们用 alter 更改表结构 */

-- 增加表结构中的列

alter table [table_name]

add culumn_name column_type

alter table partment add company varchar ( 30)

update partment set company = 'xxxx 公司 '

-- 引申：更改数据表中的列： alter table partment alter column pperson varchar(30)

-- 行转换成列

-- 在这里我们用最适用的方法

declare @sql nvarchar ( 1000)

select @sql = 'select company as 公司 '

select @sql = @sql + ',sum( case when pname =''' + cast ( pname as varchar )+ ''' then pperson end) as ' + pname from partment group by pname

select @sql = @sql + ' from partment group by company'

print @sql

exec @sql

select company as 公司 ,

sum ( case when pname = ' 技术部 ' then pperson end ) as 技术部 ,

sum ( case when pname = ' 企管部 ' then pperson end ) as 企管部 ,

sum ( case when pname = ' 行政部 ' then pperson end ) as 行政部

from partment

group by company

declare @test table (id int,name varchar(20),quarter int,profile int)
insert into @test values(1,'a',1,1000)
insert into @test values(1,'a',2,2000)
insert into @test values(1,'a',3,4000)
insert into @test values(1,'a',4,5000)
insert into @test values(2,'b',1,3000)
insert into @test values(2,'b',2,3500)
insert into @test values(2,'b',3,4200)
insert into @test values(2,'b',4,5500)
select * from @test

--行转列
select id,name,
[1] as "一季度",
[2] as "二季度",
[3] as "三季度",
[4] as "四季度",
[5] as "5"
from
@test
pivot
(
sum(profile)
for quarter in
([1],[2],[3],[4],[5])
)
as pvt

declare @test2 table (id int,name varchar(20), Q1 int, Q2 int, Q3 int, Q4 int)
insert into @test2 values(1,'a',1000,2000,4000,5000)
insert into @test2 values(2,'b',3000,3500,4200,5500)
select * from @test2

--列转行
select id,name,quarter,profile
from
@test2
unpivot
(
profile
for quarter in
([Q1],[Q2],[Q3],[Q4])
)
as unpvt