Dunk

use test_pub

--select into
--注意!不會將Primary key 及index設定到新的表格!
--資料復原步驟
--1. Backup(備份)
if exists(select name from sys.tables where name='BRANCHBACKUP')
	drop table BRANCHBACKUP
select  * into BRANCHBACKUP
from BRANCH
select * from BRANCH
select * from BRANCHBACKUP

--2.Disaster(災難)
update BRANCH set BRNAME = 'aa'
select * from BRANCH

--3.RollBack(回復)使用inner join update資料
update BRANCH  set BRNAME = b.BRNAME
from BRANCH a 
inner join BRANCHBACKUP b on a.BRNO=b.BRNO
select * from BRANCH

--將資料備份(包括PK及Index)
--1.先建立表格(可以從SSMS的select to產生)
if exists(select name from sys.tables where name='BRANCHBACKUPFULL')
	drop table BRANCHBACKUPFULL

CREATE TABLE [dbo].[BRANCHBACKUPFULL](
	[BRNO] [char](4) COLLATE Chinese_Taiwan_Stroke_BIN NOT NULL,
	[BRNAME] [varchar](30) COLLATE Chinese_Taiwan_Stroke_BIN NOT NULL,
 CONSTRAINT [PK_brnBACKUPFULL] PRIMARY KEY NONCLUSTERED 
(
	[BRNO] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

select * from BRANCHBACKUPFULL
--2. 使用insert into... select * from ...
insert into BRANCHBACKUPFULL
select * from BRANCH

select * from BRANCHBACKUPFULL




--將人員依分行排序並顯示列數
Select ROW_NUMBER() over (Partition by BRNO Order By STAFF ) as rowno,
STAFF,BRNO
From EMPLOYEE

--identity
if exists(select name from sys.tables where name='t1')
	drop table t1
-- identity(x,y) x:種子號碼,y:增量
create table t1
(
	number int identity(1,1) primary key,
	contentvalue  char(10)
)
select * from t1

--新增資料不必輸入identity的值
insert into t1(contentvalue) values('a')
insert into t1(contentvalue) values('b')
select * from t1
--設定是否可以手動輸入identity為是
Set IDENTITY_INSERT t1 on
--這行會失敗
insert into t1(contentvalue) values('a')
--成功
insert into t1(number,contentvalue) values(30,'b')
select * from t1

--設定是否可以手動輸入identity為否
Set IDENTITY_INSERT t1 off
insert into t1(contentvalue) values('a')
insert into t1(contentvalue) values('b')
--流水號會從最大值開始遞增
select * from t1

--重設流水號
truncate table t1

--子查詢
--1.IN
--查詢第一區所轄的職員
select BRNO,STAFF, NAME 
FROM EMPLOYEE
where BRNO in (select BRNO from crc_rel_br where CRCNO = '3027')
order by STAFF 

--2.table(一定要給別名)
--查詢第一區所轄的職員
select a.BRNO,STAFF, NAME 
FROM EMPLOYEE a
inner join (select BRNO from crc_rel_br where CRCNO = '3027') b on a.BRNO = b.BRNO
order by STAFF 

--3.column
--找尋各CRC所轄分行代號最大及最小的的分行代碼
select a.CRCNO , (select max(BRNO)   from crc_rel_br  c where a.CRCNO=c.CRCNO ) as MaxBrno ,
(select min(BRNO)  from crc_rel_br  c where a.CRCNO=c.CRCNO ) as MinBrno
from crc_rel_br a
group by a.CRCNO 

--使用CTE
--顯設行員的職務名稱(JOBCODE)
with JOBCODE_temp(jid,jname)
as
(
	select JOBCODE, JOBNAME
	from JOBCODE
),

 EMPData(EmpId,EmpName,jid)
as
(
	select STAFF ,NAME,JOBCODE
	from  EMPLOYEE
)

select  b.EmpId,b.EmpName, a.jname
from JOBCODE_temp a 
inner join EMPData b on a.jid = b.jid


--RECURSIVE CTE
--電子表單簽核流程顯示
if exists(select name from sys.tables where name='TreeNode')
	drop table TreeNode
create table TreeNode
(
	number int  primary key,
	contentvalue  varchar(20),
    NextDealer int
)

insert into TreeNode
select  5,'申請電子表單',null union 
select  4,'經辦',5 union 
select  3,'一階主管',4 union 
select  2,'二階主管',3 union 
select  1,'總行',2
select * from TreeNode
go
--numbers:最後簽核人員, lvl:簽核深度
with TreeNodeTraversal(numbers,lvl,tree)
as
(
	select number,  0 , cast(contentvalue as varchar(50))
	from TreeNode
    where NextDealer is not null
    union all
	select a.number,  lvl+1 , cast(tree + '=>'+a.contentvalue as varchar(50))
	from TreeNode a 
    inner join TreeNodeTraversal b on a.NextDealer	 = b.numbers	
)
select * from TreeNodeTraversal where (numbers = 1 and lvl=3) or (numbers = 2 and lvl=2) or(numbers = 3 and lvl=1)
order by lvl




--ROW_NUMBER
--列出第十到二十筆的員工資料
with EMPDATA(rno,staff,name)
as
(
select ROW_NUMBER() over ( order by STAFF) as rno ,STAFF,NAME
from EMPLOYEE 
)
select * from EMPDATA where rno between 10 and 20

--RANK() (會跳號)
--列出每個程式員換版單數量並排名
with TotalVersionNum(empid,name,total)
as
(
	select EmpID,  c.NAME,sum(1) as total
	from test_eform..VersionControl b 
    inner join test_pub..EMPLOYEE c
	on b.EmpID =c.STAFF
	group by EmpID, NAME
)
select RANK() over( order by total desc) as rkno,  empid , name ,total
from TotalVersionNum 

--DENSE_RANK()(不會跳號)
--列出各CRC轄下所有分行
select  '第'+cast (DENSE_RANK() over( order by CRCNO) as varchar) +'區營運處' as rkno, BRNO
from crc_rel_br

--NTILE分組
--將每個程式員換版單數量由高到低分成五組,並計算每組平均數
with TotalVersionNum(empid,name,total)
as
(
	select EmpID,  c.NAME,sum(1) as total 
	from test_eform..VersionControl b 
    inner join test_pub..EMPLOYEE c
	on b.EmpID =c.STAFF
	group by EmpID, NAME
)
select rkno,avg(total) as average
from
(select NTILE(5) over( order by total desc) as rkno,  total
from TotalVersionNum) c  
group by rkno

--partition by ... order by...
--將行員依照區營運處及分行代號由小到大分組
select b.CRCNO,RANK() over (Partition by b.CRCNO order by a.BRNO) as rkno
,    a.BRNO,a.STAFF,a.NAME
from EMPLOYEE a
inner join crc_rel_br b on a.BRNO = b.BRNO

select b.CRCNO ,DENSE_RANK() over (Partition by b.CRCNO order by a.BRNO) as rkno
,  a.BRNO,a.STAFF,a.NAME
from EMPLOYEE a
inner join crc_rel_br b on a.BRNO = b.BRNO

--累加計算
--將行員總數依照分行代號由小到大累加計算
with TotalWorkers(rno,brno,total)
as
(
Select ROW_NUMBER() over (order By BRNO) as RNO, BRNO,sum(1) 
From EMPLOYEE
where BRNO <>'' and STATUS='1'
group by BRNO
)
select rno, brno,(select sum(total) from TotalWorkers where rno<=p.rno) as total
from TotalWorkers p

--刪除所有暫存資料
if exists(select name from sys.tables where name='BRANCHBACKUP')
	drop table BRANCHBACKUP
if exists(select name from sys.tables where name='BRANCHBACKUPFULL')
	drop table BRANCHBACKUPFULL
if exists(select name from sys.tables where name='TreeNode')
	drop table TreeNode
if exists(select name from sys.tables where name='t1')
	drop table t1