[SQL]上課練習四

2011-09-29
1456
0
SQL
[SQL]上課練習四

use test_pub
go
--partition by ... order by
--將每家分行取前三位最資深的職員(員編最小)
with EmpData
as
(
select NAME,STAFF,BRNO,
Rank() over(partition by BRNO order by STAFF ) as rno 
from EMPLOYEE where STATUS='1'
)
select * from EmpData where rno <=3 order by BRNO

--去除重複的資料行(無PK key)
--建立測試資料
if exists(select name from sys.tables where name='DUPLICATETABLE')
	drop table DUPLICATETABLE
create table DUPLICATETABLE
(
	name varchar(10),
	position varchar(20)
)
select * from DUPLICATETABLE

insert into DUPLICATETABLE
select '李大貓','老闆' union 
select '張大牛','員工' 
--重複塞入資料
insert into DUPLICATETABLE
select * from DUPLICATETABLE

select * from DUPLICATETABLE
--根據資料分partition
--partition by 後面跟所有的欄位
--order by 可以選擇任意欄位
with Dupdata(name,position,rno)
as
(
select name, position , 
ROW_NUMBER() over(partition by name, position order by name) as rno
from DUPLICATETABLE
)
delete from Dupdata where rno >1

select * from DUPLICATETABLE

if exists(select name from sys.tables where name='DUPLICATETABLE')
	drop table DUPLICATETABLE

--去除重複的資料行(有PK key(流水號))
--建立測試資料
if exists(select name from sys.tables where name='DUPLICATETABLEINDEX')
	drop table DUPLICATETABLEINDEX
create table DUPLICATETABLEINDEX
(
	batchno int identity(1,1) primary key not null,
	name varchar(10),
	position varchar(20)
)
select * from DUPLICATETABLEINDEX

insert into DUPLICATETABLEINDEX values ('李大貓','老闆')
insert into DUPLICATETABLEINDEX values ('張大牛','員工' )

--重複塞入資料
insert into DUPLICATETABLEINDEX
select name, position from DUPLICATETABLEINDEX

select * from DUPLICATETABLEINDEX
--根據資料分partition
--partition by 後面跟所有的欄位(PK key不可以包含)
--order by 可以選擇任意欄位
with Dupdata(name,position,rno)
as
(
select name, position , 
ROW_NUMBER() over(partition by name, position order by name) as rno
from DUPLICATETABLEINDEX
)
delete from Dupdata where rno >1

select * from DUPLICATETABLEINDEX

if exists(select name from sys.tables where name='DUPLICATETABLEINDEX')
	drop table DUPLICATETABLEINDEX

--case
--直接指定欄位值
--分辨在職離職
select STAFF, NAME,
case STATUS when '1' then '在職'
						 when '2' then '離職'
						 else '未知'
end as 任免狀態
 from EMPLOYEE
--將欄位計算
--判斷職等
select STAFF, NAME,
case  when CAST(GRADE as int) > 5   and  CAST(GRADE as int) < 10 then '大於五職等'
		  when CAST(GRADE as int) > 10 then '大於十職等'
		  else '未知'
end as 職等描述
 from EMPLOYEE

--樞紐計算(使用 case)
--將每個人在2011年的換版數量依照月份顯示
use test_eform
go
with VersionCal(empid,mn,total)
as
(
select EmpID, MONTH(UpdateTime) as mn , sum(1) as total 
from VersionControl
where YEAR(UpdateTime)=2011
group by EmpID,MONTH(UpdateTime)
)
select empid,
sum(case  when mn =1 then total else 0 end) as mn1,
sum(case  when mn =2 then total else 0 end) as mn2,
sum(case  when mn =3 then total else 0 end) as mn3,
sum(case  when mn =4 then total else 0 end) as mn4,
sum(case  when mn =5 then total else 0 end) as mn5,
sum(case  when mn =6 then total else 0 end) as mn6,
sum(case  when mn =7 then total else 0 end) as mn7,
sum(case  when mn =8 then total else 0 end) as mn8,
sum(case  when mn =9 then total else 0 end) as mn9,
sum(case  when mn =10 then total else 0 end) as mn10,
sum(case  when mn =11 then total else 0 end) as mn11,
sum(case  when mn =12 then total else 0 end) as mn12
from  VersionCal
group by empid

--樞紐計算(使用 Pivot)
--將每個人在2011年的換版數量依照月份顯示
use test_eform
go
with VersionCalPivot(mn,empid,total)
as
(
select MONTH(UpdateTime) as mn , EmpID, sum(1) as total 
from VersionControl
where YEAR(UpdateTime)=2011
group by MONTH(UpdateTime) , EmpID
)
select * from VersionCalPivot PIVOT(
	SUM(total) for 
	mn in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) a 
order by empid

--函式,宣告及一些程式流程
--DECLARE
--顯示員工總數
use test_pub
go
declare @num int
select @num = count(*) from EMPLOYEE where STATUS='1'
print @num

--IF...ELSE
--檢查員工身分證字號是否符合
declare @ID  nvarchar(10)
set @ID = 'A123456789'
if(Exists(select * from EMPLOYEE where ID=@ID))
	select '存在' as 是否有該員工
else
	select '不存在' as 是否有該員工

--while
--顯示十次
declare @counter int
set @counter = 10
while @counter>0
begin
	print @counter
	set @counter = @counter-1
End  

--FUNCTION
Select CEILING(2.5)
Select CEILING(-2.5)

Select FLOOR(2.5)
Select FLOOR(-2.5)

Select ROUND(2.5, 0, 0)
Select ROUND(-2.5, 0, 0)

Select LEFT('ABCDEFG', 3)
Select RIGHT('ABCDEFG', 3)
Select SUBSTRING('ABCDEFG', 3,4)
Select 'a' + RTRIM('    ABCD    ') + 'a'
Select 'a' + LTRIM('    ABCD    ') + 'a'
--SQL沒有TRIM,要將LTRIM及RTRIM一起使用
Select RTRIM(LTRIM('    ABCD    '))

--自定義function
--取得今天是那個月份的
drop function GetMonth
go
create function GetMonth() returns int
as
begin
	return MONTH(Getdate())
end
go
select dbo.GetMonth()

--傳回表格
use test_pub
go
drop function GetEmpData
go
create function GetEmpData() returns table
as
	return (select * from EMPLOYEE)

go
select * from dbo.GetEmpData()


--store procedure
create procedure GetEmpName
@STAFF as char(6)
as
select NAME from EMPLOYEE where STAFF=@STAFF
go
exec GetEmpName '123456'
--刪除store procedure
drop procedure GetEmpName

--trigger(用於新增,修改,刪除)
--建立一個暫存表格儲從權限異動的資料資料
use SC
go
if exists(select name from sys.tables where name='SC_ROL_USR_LOG')
	drop table SC_ROL_USR_LOG

create table SC_ROL_USR_LOG
(
	[ROL_ID] [nvarchar](20) ,
	[USR_ID] [nvarchar](20) ,
    [ACTION] [nvarchar](20),	
	modifydate datetime default getdate()
)
select * from SC_ROL_USR_LOG
go
--監看新增資料的trigger
Create Trigger trigerRol_USR_ADD
on SC_ROL_USR
After Insert
as
insert into SC_ROL_USR_LOG(ROL_ID,USR_ID,ACTION)
select ROL_ID,USR_ID,'新增' from inserted
Go
--監看刪除資料的trigger
Create Trigger trigerRol_USR_DEL
on SC_ROL_USR
After Delete
as
insert into SC_ROL_USR_LOG(ROL_ID,USR_ID,ACTION)
select ROL_ID,USR_ID,'刪除' from deleted
Go
--測試tringer
insert into SC_ROL_USR(ROL_ID,USR_ID) values('default','123456')

select * from  SC_ROL_USR_LOG

delete from SC_ROL_USR where USR_ID='123456'

select * from  SC_ROL_USR_LOG

--啟用/停用 trigger
enable Trigger trigerRol_USR_ADD on SC_ROL_USR
Disable Trigger trigerRol_USR_ADD on SC_ROL_USR


--刪除tringger
drop trigger trigerRol_USR_ADD
drop trigger trigerRol_USR_DEL
回首頁