Bài giảng Cơ sở dữ liệu - Chương 4: 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