CÁC LỆNH TRONG BÀN 119

Kiem tra dung luong cap va cap chua dung
select * from cap where dung_luong=20 and da_dung=0;
Kiem tra so luong thue bao cap theo tung tram trong chuong trinh qlmc
select thue_bao_cap.ma_tram,thue_bao_cap,count (*) from thue_bao_cap group by ma_tram;

Thong ke dung luong cap da su dung:
select dung_luong, ma_cap,da_dung,con_lai,chieu_dai from cap where ma_tram=1 and dung_luong=50;

thong ke dung luong cap da duoc sap xep tu lon den be:
select dung_luong,ma_cap,da_dung,con_lai,chieu_dai from cap where ma_tram=1 order by dung_luong desc;

chuyen sang excel
spool c:\ten_file
select dung_luong,da_dung,con_lai,ma_cap from cap where ma_tram=1 order by ma_cap desc;

select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc;

Kiem tra dung luong cap va cap chua dung
select * from cap where dung_luong=20 and da_dung=0;

THONG KE thue bao CUA CAC tram
select ma_tram,count (*) from thue_bao_cap group by ma_tram;

Thong ke tæng chiÒu dµi c¸p
select sum(chieu_dai) from cap;

THONG KE DUNG LUONG CAP GOC
select sum(dung_luong) from cap where muc_mang=1;

dung luong cap da su dung:
select dung_luong, ma_cap,da_dung,con_lai,chieu_dai from cap where ma_tram=1 and dung_luong=50;

thong ke dung luong cap da duoc sap xep tu lon den be:
select dung_luong,ma_cap,da_dung,con_lai,chieu_dai from cap,ket_cuoi where cap ma_tram=1 order by dung_luong desc;
select
select dung_luong,ma_cap,da_dung,con_lai,chieu_dai from cap where ma_tram=2 order by dung_luong desc;

chuyen sang excel
spool c:\ten_file
select dung_luong,da_dung,con_lai,ma_cap from cap where ma_tram=1 order by ma_cap desc;

tæng chiÒu dµi c¸p gèc
select sum(chieu_dai) from cap where muc_mang=1;
dung l­îng cac soi c¸p gèc vµ ten cac soi cap goc
select dung_luong,ma¬_cap from cap where muc_mang=1 and ma_tram=1;
select dung_luong,ma¬_cap from cap where muc_mang=1 and ma_tram=2;
select dung_luong,ma¬_cap from cap where muc_mang=1;
tæng dung l­îng c¸p gèc cña tõng tæng ®ai vµ toµn m¹ng
select sum(dung_luong) from cap where muc_mang=1 and ma_tram=1;
select sum(dung_luong) from cap where muc_mang=1 and ma_tram=2;
select sum(dung_luong) from cap where muc_mang=1;
tæng dung l­îng c¸p gèc
select sum(dung_luong) from cap;
tên
desc cap
desc cable





Thong ke toan mang
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung ,cap.con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc;
Thong ke 01 soi cap
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and cap.ma_cap like ’CA10%’;


select dung_luong,da_dung,con_lai,ma_cap from cap where ma_tram=1 order by ma_cap desc;


Thong ke nhieu soi cap
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and (cap.ma_cap like ’CA10%’ or cap.ma_cap like ‘CA02%’ or cap.ma_cap like ‘CA06%’……………………..) order by cap.ma_cap;

SELECT CAP CAC NHOM
1 - Nhom khanh
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and (cap.ma_cap like ’CA01%’ or cap.ma_cap like ‘CA06%’ or cap.ma_cap like ‘CA13%’ or cap.ma_cap like ‘CA18%’ or cap.ma_cap like ‘CA26%’) order by cap.ma_cap;

2 – Nhom nen
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and (cap.ma_cap like ’CA02%’ or cap.ma_cap like ’CA08%’ or cap.ma_cap like ’CA09%’ or cap.ma_cap like ’CA10%’ or cap.ma_cap like ’CA11%’) order by cap.ma_cap;
3 – Nhom huan
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and (cap.ma_cap like ’CA04%’ or cap.ma_cap like ‘CA05%’ or cap.ma_cap like ‘CA05/3B%’or cap.ma_cap like ‘CA25%’) order by cap.ma_cap;

SELECT TO HUNG
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and (cap.ma_cap like ’CA01%’ or cap.ma_cap like ‘CA02%’ or cap.ma_cap like ‘CA05’ or cap.ma_cap like ‘CA06%’ or cap.ma_cap like ‘CA08%’ or cap.ma_cap like ‘CA09%’ or cap.ma_cap like ’CA10%’ or cap.ma_cap like ‘CA11%’ or cap.ma_cap like ‘CA13%’ or cap.ma_cap like ‘CA15%’ or cap.ma_cap like ‘CA17%’ or cap.ma_cap like ‘CA18%’ or cap.ma_cap like ‘CA25%’ or cap.ma_cap like ‘CA26%’ or cap.ma_cap like ‘CA27%’) order by cap.ma_cap;


STT
C¸p
gèc
§• dïng
Dung l­îng
§Þa chØ
Nhãm ®¶m nhiÖm
Ghi chó
01 A 01 187 200p GiÕng trßn M¸y háng T4
02 A 02 475 600p M¸y kÐo - Nhãm nÒn :136
03 A 05 179 300p S©n kho ®¸ - Nhãm khanh: 109
04 A 06 199 200p Thanh thuû - Nhãm hu©n : 58
05 A 08 305 600p Thuû n«ng Céng tæ 1: 303 m¸y
06 A 09 115 200p Bµ dung = 5,39 %
07 A 10 756 1000p ThÇn kinh
08 A 11 175 200p ¤ng §­¬ng
09 A 13 527 600p GiÕng trßn
10 A15 689 1000p ThÇn kinh
11 A17 765 1000p L­¬ng thùc
12 A 18 130 200p An t©n
13 A 25 114 500p L­¬ng thùc
14 A 26 298 1000p GiÕng trßn
15 A 27 705 1000p Gia l­¬ng
Céng Tæ hïng qu¶n lý 5.619 m¸y 8.600 ®«i Sè liÖu 2.05.2006
01 A03 600p Qu¸n ch¾m
02 A04 200p ¤ng thµnh
03 A07 100p UBNDHuyÖn
04 A12 1000p C©u gç
05 A14 600p Cèng ch¾m
06 A16 600p Ng• 4 Tr¹m thuÕ
07 A31 100p HuyÖn ®éi
08 A33 200p Nhµ c­a xÎ
Nam qu¶n 3.400 ®«i
Tr¹m 01 TT©m

12.000 ®«i gèc

Sè liÖu 24.04.2006


SELECT TO NAM

select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and (cap.ma_cap like ’CA03%’ or cap.ma_cap like ‘CA04%’ or cap.ma_cap like ‘CA07%’ or cap.ma_cap like ‘CA12%’ or cap.ma_cap like ‘CA14%’ or cap.ma_cap like ‘CA16%’ or cap.ma_cap like ’CA31%’ or cap.ma_cap like ‘CA33%’ or cap.ma_cap like ‘CB01%’ or cap.ma_cap like ’CB02%’ or cap.ma_cap like ‘CB03%’ or cap.ma_cap like ‘CB05%’ or cap.ma_cap like ‘CB06%’ or cap.ma_cap like ‘CB07%’ or cap.ma_cap like‘CB08%’ or cap.ma_cap like ‘CB08B%’ or cap.ma_cap like ‘CB09%’ or cap.ma_cap like ‘CB10%’) order by cap.ma_cap;


stt
C¸p
gèc
§• dïng
Dung l­îng
§Þa chØ
Nhãm phô tr¸ch
Ghi chó
01 A03 370 600p Qu¸n ch¾m M¸y háng tæ 2 T4
02 A04 97 200p ¤ng thµnh - Nhãm ¸nh :121
03 A07 47 100p UBNDHuyÖn - Nhãm nghÜa : 81
04 A12 854 1000p C©u gç - Nhãm nghiÖp: 148
05 A14 249 600p Cèng ch¾m Céng tæ 1 háng: 350
06 A16 474 600p Ng• 4 Tr¹m thuÕ = 7,6 %
07 A31 69 100p HuyÖn ®éi
08 A33 118 200p Nhµ c­a xÎ
Tr¹m gia léc 3.400 ®«i
09 B01 187 200p Ph­¬ng b»ng
10 B02 191 300p Cæng b­u ®iÖn
11 B03 226 300p CÇu trµng
12 B05 326 1000p Cçu trµng
13 B06 473 600p RÏ ®øc x­¬ng
14 B07 94 100p Tr¹m b¬m ®­¬i
15 B08 576 600p CÇu trµng
16 B09 77 100p TÇng 2 B­u ®iÖn
17 B10 134 200p Th«n C¸y
Tram §T 4.562 m¸y 3.500 ®«i Sè liÖu 2.05.2006
Céng Tæ nam qu¶n lý 6.900 ®«i


4 – Nhom anh
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and (cap.ma_cap like ’CA03%’ or cap.ma_cap like ‘CA07%’ or cap.ma_cap like ‘CA16%’ or cap.ma_cap like ‘CA31%’ or cap.ma_cap like ‘CA33%’ or cap.ma_cap like ‘CB01%’) order by cap.ma_cap;
5 – Nhom nghiep
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and (cap.ma_cap like ’CB03%’ or cap.ma_cap like ‘CB06%’ or cap.ma_cap like ‘CB08%’ or cap.ma_cap like ‘CB08B%’) order by cap.ma_cap;
6 – Nhom nghia
select cap.ma_cap,ket_cuoi.dia_chi,cap.dung_luong,cap.chieu_dai,cap.da_dung,con_lai from cap,ket_cuoi where cap.kc_den=ket_cuoi.ma_kc and (cap.ma_cap like ’CA12%’ or cap.ma_cap like ‘CB02%’ or cap.ma_cap like ‘CB04%’ or cap.ma_cap like ‘CB05%’ or cap.ma_cap like ‘CB07%’ or cap.ma_cap like ‘CB09%’ or cap.ma_cap like ‘CB10%’) order by cap.ma_cap;