Bài giảng Truy vấn nâng cao

TRUY VẤN NÂNG CAO 
Bảng tạm 
with tscv as ( 
	select manv, count(*) ts from phancong 
	 group by manv 
) 
select manv, tennv, 
	(select ts from tscv where manv=nv.manv) 
 	 / (select sum(ts) from tscv ) AS xxx 
from nhanvien nv 
-------------------------------------------------- 
select manv, count (*)/(select sum(ts) from tscv ) 
from phancong 
group by manv 
Truy vấn đệ quy 
create table quanhe_nhanvien ( manv char(5) primary key,tennv char(20 ), manvct char(5)) 
------------------------------- 
manv	tennv	manvct 
Nv001	A	null 
Nv002	B	null 
Nv003	C	Nv001 
Nv004	D	Nv001 
Nv005	E	Nv003 
Nv006	F	Nv005 
Truy vấn đệ quy 
with truy (manv,tennv,manvct,mucdo,cay,cayten) as ( 
	select	a.manv,a.tennv,a.manvct,1 as mucdo, 
	cast('' as varchar(max)) as cay, 
	cast('' as varchar(max)) as cayten 
	from	quanhe_nhanvien a 
	where	 a.manvct is null 
	union all 
	select	 
	b.manv,b.tennv,b.manvct, mucdo + 1 as mucdo, 
	( truy.cay + '\' + b.manvct) as cay, 
	 cayten + '\' + cast( truy.tennv as varchar(20)) 
	 	as cayten 
	from	quanhe_nhanvien b inner join truy 
	on truy.manv = b.manvct 
) 
select * from truy 
Truy vấn đệ quy 
with truy (manv,tennv,manvct,kq) as ( 
	select	a.manv,a.tennv,a.manvct, 
	cast('' as varchar(max)) as kq 
	from	quanhe_nhanvien a 
	where	a.manvct is null 
	union all 
	select	b.manv,b.tennv,b.manvct, 
	 	( truy.kq + '\' + b.manvct) as kq 
	from	quanhe_nhanvien b inner join truy 
	on truy.manv = b.manvct 
) 
select * from truy 
Truy vấn đệ quy (kết quả) 
Manv 
Tennv 
Manvct 
Mucdo 
cay 
cayten 
Nv001 
A 
null 
1 
Nv002 
B 
Null 
1 
Nv003 
C 
Nv001 
2 
\Nv001 
\A 
Nv004 
D 
Nv001 
2 
\Nv001 
\A 
Nv005 
E 
Nv003 
3 
\Nv001\Nv003 
\A\C 
Nv006 
F 
Nv005 
4 
\Nv001\Nv003\Nv005 
\A\C\E 
Truy vấn đệ quy 
with truy (manv,tennv,manvct,mucdo,cay,cayten) as ( 
	select	a.manv,a.tennv,a.manvct,1 as mucdo, 
	cast('' as varchar(max)) as cay, 
	cast('' as varchar(max)) as cayten 
	from	quanhe_nhanvien a 
	where	a.manvct = a.manv 
	union all 
	select	 
	 	b.manv,b.tennv,b.manvct,mucdo + 1 as mucdo, 
	(truy.cay + '\' + b.manvct) as cay, 
	cayten + '\' + cast(truy.tennv as varchar(20)) 
	as cayten 
	from	quanhe_nhanvien b inner join truy 
	on truy.manv = b.manvct 
	where 	b.manv != b.manvct 
) 
select * from truy 
Đây là trường hợp điều kiện dừng là mã nv cấp trên và mã nv đó giống nhau. 
Truy vấn chéo  (Pivot) 
create table baocaods 
(nam int, mahang char(5 ), sl int, daily char(5), 
 constraint kc_baocaods primary key (nam,mahang,daily)) 
--------------------------------------------------------- 
(1998,'G1',3,'D1') 
(1999,'G1',4,'D1') 
(1997,'G1',2,'D1') 
(1998,'G2',9,'D1') 
(1999,'G2',4,'D1') 
(1997,'G2',6,'D1') 
(1998,'G1',32,'D2') 
(1999,'G1',42,'D2') 
(1997,'G1',22,'D2') 
(1998,'G2',30,'D2') 
(1999,'G2',40,'D2') 
(1997,'G2',20,'D2 ') 
Truy vấn chéo (Pivot) 
select mahang,[1997],[1998],[1999] 
from (select mahang,sl,nam from baocaods) as a 
pivot (sum(sl) for nam in [ 1997],[1998],[1999])) as b 
------------------------------------------------------ 
[1997],[1998],[1999] : là các giá trị 
mahang,sl,nam : gồm đúng 3 cột gồm Header Row,Header column, Value. 
Mệnh đề From và Pivot phải đặt lại tên mới . 
------------------------------------------------------ 
Mahang 	 1997	1998	1999 
G1	 	24 	35	46 
G2	 	26 	39	 44 
Truy vấn  (Cross – outer apply) 
select * 
From nhanvien as nv cross apply 
	( select count(*) as ts from phancong as pc 
 	 where pc.manv = nv .manv 
	) as tongsoviec 
Làm lại truy vấn count cho từng manv 
(có thể thay câu count thành hàm tính toán cho ra Table) 
Select * 
from 	nhanvien as nv inner join 
	( select manv,count(*) ts from phancong 
	 group by manv) as tscv 
	On tscv.manv = nv.manv