常见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