trungvtgl

ĐOÀN VĂN TRUNG --VIỄN THÔNG GIA LỘC---SDT: 03203606116


    CÁC LỆNH TRONG BÀN 119

    Share

    trunghd

    Tổng số bài gửi : 5
    Join date : 31/03/2013

    CÁC LỆNH TRONG BÀN 119

    Bài gửi  trunghd on Sat Apr 20, 2013 9:08 am

    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;


      Hôm nay: Sun Nov 19, 2017 12:41 am