常见sql高级运用

 

 -- 常见 sql 高级运用

  

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

 

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

  

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

begin

 drop table partment

end

go

 

create table partment

(

 pid int  identity ( 1, 1),

 pname varchar ( 30) not null,

 pperson int not null

)

go 

-- 建立约束

alter table partment

 add constraint primary key ( pid)

 

-- 添加数据

insert partment

   select ' 技术部 ' , 80

union all select ' 行政部 ' , 20

union all select ' 企管部 ' , 70

union all select ' 商管部 ' , 60

go

select * from partment

go 

-- 删除表中重复的行

 /* 分析：

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

     对本列在于找到部门名称相同的数据 , 通过聚合函数 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)

   go

 

/*

    在 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)

go

 

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

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

 

-- 增加表结构中的列

/*

   alter table [table_name]

             add culumn_name column_type

*/

alter table partment add  company varchar ( 30)

go

update partment set company = 'xxxx 公司 '

go

 -- 引申：更改数据表中的列： 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