1 ÒýÑÔ
Öйú±¾ÍÁ³ÌÐòÔ±Âí¸ù·åÍÆ³öµÄ¸öÈË×÷Æ·----ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷£¬ÖÐÎİ汾¡¶DB ²éѯ·ÖÎöÆ÷¡·¡¢Ó¢Îİ汾¡¶DB
Query Analyzer¡·¡£
ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷¼¯¹þÏ£¼¼Êõ¡¢Á´±íµÈ¶àÖÖÊý¾Ý½á¹¹ÓÚÒ»Ì壬ʹÓÃÏȽøÏµÍ³¿ª·¢¼¼Êõ£¬¾Àú4ÄêµÄÑо¿¡¢¿ª·¢¡¢²âÊÔÖÜÆÚºóÔÚ2006ÄêÃæÊÀ¡£Ö®ºó7ÄêÀ´Ò»Ö±ÔÚ½øÐв»¶ÏµØÍêÉÆ¡¢Éý¼¶£¬µ½Ä¿Ç°ÎªÖ¹£¬×îа汾Ϊ5.04
¡£¡°ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷¡±ºËÐIJ¿·Ö¾Í¾ßÓг¤´ï5Íò¶àÐдúÂëµÄ¹¤×÷Á¿£¬Ê¹µÃÆä¾ßÓÐÇ¿´óµÄ¹¦ÄÜ¡¢ÓѺõIJÙ×÷½çÃæ¡¢Á¼ºÃµÄ²Ù×÷ÐÔ¡¢¿çÔ½¸÷ÖÖÊý¾Ý¿âƽ̨ÄËÖÁÓÚEXCELºÍÎı¾Îļþ¡£
Äã¿ÉÒÔͨ¹ýËü²éѯODBCÊý¾ÝÔ´£¨°üÀ¨ÊÀÃæÉÏËùÓеÄÊý¾Ý¿â¡¢TXT/CSVÎļþ¡¢EXCELÎļþ£©µÄÊý¾Ý¡£Äã¿ÉÒÔͬʱִÐжàÌõDMLÓï¾äÄËÖÁ´æÖü¹ý³Ì£¬½á¹û»áÒÔÄãÉ趨µÄ±í¸ñ¡¢Îı¾¿ò¡¢ÎļþÀ´·µ»Ø¡£´ÓÊý¾Ý¿âµ¼³öǧÍòÌõÊý¾Ýʱ£¬Ð§ÂÊÓëDBMSûÓÐÊ²Ã´Çø±ð¡£
±¾ÎĽ«ÒÔ5.04°æ±¾ÎªÀý£¬Ïêϸ²ûÊö¡°ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷¡±ÖÐÎİ汾¡¶DB
²éѯ·ÖÎöÆ÷¡·ÔÚ Îı¾Îļþ ´¦Àí·½Ãæ·Ç³£Ç¿´óµÄ¹¦ÄÜ£¬Äã¿ÉÒÔÖ±½ÓÓÃSQLÓï¾äÀ´·ÃÎÊÕâЩÎı¾Îļþ£¬·ÃÎÊ250ÍòÌõ¼Ç¼µÄÎļþµÄ¸´ÔӵĹØÁª²Ù×÷£¬Ò²²»¹ýÓÃʱ59ÃëÖÓ¡£Òª×¢ÒâµÄÊÇ£¬Îı¾ÎļþµÄµÚÒ»ÐÐÐèÒªÓÐÁÐÃû¡£
2 ²úÆ·»ñµÃµÄ³É¾Í¼°·¢Õ¹Àú³Ì
ÖÐÎİ汾¡¶DB ²éѯ·ÖÎöÆ÷¡·ÔÚÖйشåÔÚÏß ÏÂÔØÁ¿³¬¹ý10Íò ¶à´Î£¬Î»¾ÓÕû¸öÊý¾Ý¿âÀàÅÅÐаñÖÐǰ20λ¡£
ÔÚ¡¶³ÌÐòÔ±¡·2007µÚ2ÆÚµÄ¡°Ð²úÆ·&¹¤¾ßµãÆÀ¡±²¿·Ö£¬±à¼¡°ÌرðÍÆ¼ö¡±ÁË¡°ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷¡±·¢²¼¡£±¾ÆÚÖ»µãÆÀÁË5¸ö¹¤¾ß£¬·Ö±ðÊÇ¡°Adobe
Acrobat 8 ÖÐÎİ桱¡¢¡°Ñ¸À×ËÑË÷ 1.7 аæÉÏÏß¡±¡¢¡°Google ×ÀÃæËÑË÷ 5.0 ÖÐÎÄ·¢²¼¡±¡¢¡°BEA
·¢²¼ WebLogic SIPServer 3.0¡±ºÍÌØ±ðÍÆ¼ö¡°ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷¡±·¢²¼¡£Ç°Ãæ4¸ö¶¼ÊǹúÄÚÍâ´óÐÍÈí¼þ¹«Ë¾µÄ²úÆ·£¬Ö»ÓС°ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷¡±ÊǸöÈË´´×÷µÄÈí¼þ¡£
½ØÖ¹µ½2013Äê4ÔÂ17ÈÕ£¬ÔÚBaiduÉÏËÑË÷¹Ø¼ü×Ö"ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷"£¬ËÑË÷½á¹û´ï318Íò¡£ÔÚBaiduÉÏËÑË÷¹Ø¼ü×Ö"DB²éѯ·ÖÎöÆ÷"¡¢"DBQuery
Analyzer"£¬ËÑË÷½á¹û·Ö±ðÔÚ104Íò¡¢16Íò×óÓÒ£»ÔÚGoogleÉÏËÑË÷¡°DB ²éѯ·ÖÎöÆ÷¡±¡¢¡°DBQuery
Analyzer¡±£¬½á¹û·Ö±ð´ï104Íò¡¢44ÍòÖ®¶à¡£
±¾ÈË׫дÁ˹ØÓÚ¡°ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷¡±Óйؼ¼ÊõµÄ64ƪÎÄÕ£¬·¢²¼ÔÚ¡¶µçÄÔ±à³Ì¼¼ÇÉÓëά»¤¡·¡¢¡¶Èí¼þ¡·¡¢¡¶¼ÆËã»úʱ´ú¡·¡¢¡¶µçÄÔ±à³Ì¼¼ÇÉÓëά»¤¡·¡¢°Ù¶ÈÎĿ⡢CSDN×ÊÔ´¡¢ºÍ±¾È˵ÄËĴ󲩿ÍÉÏ£¨CSDN²©¿Í¡¢ÐÂÀ˲©¿Í¡¢QQ¿Õ¼äºÍËѺü²©¿ÍÉÏ£©¡£
3 ΪºÎÓá¶DB ²éѯ·ÖÎöÆ÷¡·À´·ÃÎÊÎı¾ÎļþÀ´½â¾öʵ¼ÊÎÊÌâ
Ôڹ㶫ÁªºÏµç×Ó·þÎñ¹É·ÝÓÐÏÞ¹«Ë¾ÊµÊ©Ò»ÕÅÍøµÄ¹ý³ÌÖУ¬ÓÉÓÚ·¶ÎÉÏ´«µÄÁ÷Ë®´æÔÚ´óÁ¿µÄÒì³££¬µ¼Ö°´ÕÕÕý³£µÄ½áËãÁ÷³ÌÎÞ·¨¿ìËٵؽøÐз¶ÎµÄ½áËã¡£Òò´Ë£¬ÎªÁ˽øÐм°Ê±µÄ½áË㣬¹ã¶«Ê¡¸ßËÙ¹«Â·¹«Ë¾ÊÚȨÏȽ«Òì³£Á÷Ë®½øÐкöÂÔ£¬ºóÆÚÔÙ½«ÕâЩÁ÷Ë®½øÐÐÉÏ´«¡¢Ð޸ġ¢²ð·Ö½áËã¡£
×î½ü£¬ÎªÁËʵʩºöÂÔÁ÷Ë®µÄ»Ø´«£¬±¾ÈË×öµÄµÚÒ»²½¾ÍÊÇ´ÓºöÂÔµÄÁ÷Ë®Öа´ÕÕ£¨Á÷ˮֻÓÐÔÚ¹ÜÀíµã´æÔÚ¡¢ÖÐÐĺ͹ÜÀíµã¶¼´æÔÚ£©ÕâÁ½ÖÖÇé¿ö£¬ÔÙ°´ÕÕÁ÷Ë®½ð¶îСÓÚ0¡¢µÈÓÚ0¡¢´óÓÚ0ÈýÀà½øÐÐͳ¼Æ³Ę́ÕË£¨6¸öÎļþ£©£¬È»ºóÒµÎñ×飬ÔÙ´ÓÕâЩ·ÖÀàÅú´Î£¨6¸öÎļþ£©ÖÐÈ·ÈÏÄÄЩÅú´ÎÐèÒª»Ø´«£¨ÐγÉ2
¸öÎļþ£¬ Á÷Ë®´óÓÚ0.csv ºÍ Á÷ˮСÓÚ0.csv £©¡£
×îÖÕ£¬±¾ÈË»¹Òª¸ù¾ÝÕâЩÕâÁ½¸öÎļþÖÐÊÇ·ñ¡°»Ø´«¡±£¬ÔÙ´Ó½áËãϵͳÖиù¾Ý¸´ÔÓµÄÂß¼£¬ÕÒ³öÒª»Ø´«µÄÁ÷Ë®ºÅ²¢°´ÕÕÇøÓò·Ö³É4¸öÎļþ¡£
ÔÚ±¾´Î´¦ÀíÖУ¬
Ô´´¦ÀíÎļþ£ºÁ÷Ë®´óÓÚ0.csv ¡¢ Á÷ˮСÓÚ0.csv ¡¢tb_road¡¢ ¹ÜÀíµã´æÔÚÖÐÐIJ»´æÔÚµÄÁ÷Ë®_ËùÓÐ.txt
ºÍ ¹ÜÀíµãÓëÖÐÐͼ´æÔڵIJ¿·Ö_ËùÓÐ.txt Õâ5¸öÎļþ¡£
ºóÁ½¸öÎļþÊÇ´Ó½áËãϵͳÊý¾Ý¿â·þÎñÆ÷ÖÐͨ¹ý¸´ÔÓµÄÂß¼´¦ÀíÉú³ÉµÄÖмä½á¹ûÎļþ¡£
Êä³ö½á¹û£ºÒª»Ø´«µÄÁ÷Ë®ºÅ²¢°´ÕÕÇøÓò·Ö³É4¸öÎļþ¡£
½â¾ö·½°¸£ºÒò´Ë£¬ÊäÈëÌõ¼þ¾ö¶¨ÒªÃ´ÔÚ½áËãÊý¾Ý¿âϵͳÖн¨Á¢ Êý¾Ý±í À´Íê³ÉÌõ¼þµÄÅжϣ»ÒªÃ´½«½á¹ûÈ«²¿ºöÂÔµÄÁ÷Ë®¡¢ÐèÒª¹ØÁªµÄÊý¾Ý±í£¨tb_road£©µ¼³ö³É.CSV/.TXT
Îļþ£¬ÔÙʹÓá¶DB ²éѯ·ÖÎöÆ÷¡·Ç¿´ó¡¢¸ßЧµÄ.CSV/.TXT ·ÃÎʹ¦ÄÜÀ´ÊµÊ©Õû¸ö»Ø´«Á÷Ë®ÎļþµÄÉú³É¡£
tb_road ±íÖ»ÊÇ´ÓÉú²ú»úÊý¾Ý¿âÖе¼³ötb_road±í¾ÍÐÐÁË¡£
ͼ2 Êý¾ÝÍÚ¾òÉú³ÉÎı¾Îļþ¡°¹ÜÀíµãÓëÖÐÐͼ´æÔÚ²¿·Ö_ËùÓÐ.txt¡±

4 5.04ÖÐÎİ汾¡¶DB ²éѯ·ÖÎöÆ÷¡·ÎªÀý
ÏÂÃæÎÒÃǾÍÒÔ¡°ÍòÄÜÊý¾Ý¿â²éѯ·ÖÎöÆ÷¡±µÄÖÐÎİ汾¡¶DB ²éѯ·ÖÎöÆ÷¡· 5.04ΪÀý£¬ÒÔWindows
2000Server²Ù×÷ϵͳΪƽ̨£¬ÏÈ´´½¨»ùÓÚĿ¼ ¡°D:\ODBC_TXT_CSV¡±ÖÐ .TXT/.CSV
ÎļþµÄODBCÊý¾ÝÔ´ ¡°odbc_txt_csv¡±£¬È»ºóÔÙͨ¹ý DB ²éѯ·ÖÎöÆ÷ 5.04À´·ÃÎÊÕâЩĿ¼ÏµÄÎļþ¡£
²Ù×÷ϵͳ£º Windows2000 Server²Ù×÷ϵͳ
CPU£º2.8 GHZ µ¥ºË
Äڴ棺1GB
ǰ̨³ÌÐò£º DB ²éѯ·ÖÎöÆ÷ 5.04
ºǫ́Îļþ£º .TXT/.CSV
ͼ3 ´´½¨»ùÓÚ .CSV/.TXT ÎļþµÄODBCÊý¾ÝÔ´£¨Ò»£©

ͼ4 ´´½¨»ùÓÚ .CSV/.TXT ÎļþµÄODBCÊý¾ÝÔ´£¨¶þ£©

ͼ5 µÇ¼odbc_txt_csv£¬²»ÓÃÊäÈëÓû§ÃûºÍ¿ÚÁî

ͼ6 ¶ÔÏóä¯ÀÀÆ÷À´²é¿´Êý¾ÝÔ´ odbc_txt_csv

ͼ7 Windows×ÊÔ´¹ÜÀíÆ÷ÖУ¬ODBCÊý¾ÝÔ´odbc_txt_csv¶ÔÓ¦µÄĿ¼ÏÂËùÓеÄÎļþ

ͼ8 ÓÃSQLÓï¾äÀ´·ÃÎÊ .txtºÍ .csvÎļþ£¬À´Éú³É¸÷ÇøÓòÐèÒª»Ø´«µÄÁ÷Ë®

ͼ8Öеĸ´ÔÓµÄSQLÓï¾äÈçÏ£º
select listno,'2014-06-16'
from
(
select distinct b.LISTNO,b.roadno
--select count(*) asrec_num,sum(b.CASHMONEY)/100 as CASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY
from
(
selectw.roadno,w.squaddate,w.roadname,w.outvehclass,
sum(w.rec_count) as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) as etcmoney1
from
(
select *
FROM [Á÷Ë®´óÓÚ0.csv]
where (±¸×¢ is null) and »ØËͱêÖ¾='»ØËÍ' and(cashmoney+etcmoney)<0
union all
select *
FROM [Á÷ˮСÓÚ0.csv]
where (±¸×¢ is null) and »ØËͱêÖ¾='»ØËÍ' and(cashmoney+etcmoney)<0
) w
group byw.roadno,w.squaddate,w.roadname,w.outvehclass
) a,[¹ÜÀíµãÓëÖÐÐͼ´æÔÚ²¿·Ö_ËùÓÐ.txt] b
where ( (b.CASHMONEY+b.ETCMONEY)<0 ) and a.roadno=b.roadno anda.SQUADDATE=b.SQUADDATE
anda.OUTVEHCLASS=b.OUTVEHCLASSNAME
union
select distinct b.LISTNO,b.roadno
--select count(*) asrec_num,sum(b.CASHMONEY)/100 as CASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY
from
(
selectw.roadno,w.squaddate,w.roadname,w.outvehclass,
sum(w.rec_count) as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) as etcmoney1
from
(
select *
FROM [Á÷Ë®´óÓÚ0.csv]
where (±¸×¢ is null) and »ØËͱêÖ¾='»ØËÍ' and(cashmoney+etcmoney)>0
union all
select *
FROM [Á÷ˮСÓÚ0.csv]
where (±¸×¢ is null) and »ØËͱêÖ¾='»ØËÍ' and(cashmoney+etcmoney)>0
) w
group byw.roadno,w.squaddate,w.roadname,w.outvehclass
) a,[¹ÜÀíµãÓëÖÐÐͼ´æÔÚ²¿·Ö_ËùÓÐ.txt] b
where ( (b.CASHMONEY+b.ETCMONEY)>0 ) and a.roadno=b.roadno anda.SQUADDATE=b.SQUADDATE
anda.OUTVEHCLASS=b.OUTVEHCLASSNAME
union
select distinct b.LISTNO,b.roadno
--select count(*) asrec_num,sum(b.CASHMONEY)/100 as CASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY
from
(
selectw.roadno,w.squaddate,w.roadname,w.outvehclass,
sum(w.rec_count) as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) as etcmoney1
from
(
select *
FROM [Á÷Ë®´óÓÚ0.csv]
where (±¸×¢='Ö»¹ÜÀíµã´æÔÚ') and »ØËͱêÖ¾='»ØËÍ' and(cashmoney+etcmoney)<0
union all
select *
FROM [Á÷ˮСÓÚ0.csv]
where (±¸×¢='Ö»¹ÜÀíµã´æÔÚ') and »ØËͱêÖ¾='»ØËÍ' and(cashmoney+etcmoney)<0
) w
group byw.roadno,w.squaddate,w.roadname,w.outvehclass
) a,[¹ÜÀíµã´æÔÚÖÐÐIJ»´æÔÚµÄÁ÷Ë®_ËùÓÐ.txt] b
where ((b.CASHMONEY+b.ETCMONEY)<0 ) and a.roadno=b.roadno anda.SQUADDATE=b.SQUADDATE
anda.OUTVEHCLASS=b.OUTVEHCLASSNAME
union
select distinct b.LISTNO,b.roadno
--select count(*) asrec_num,sum(b.CASHMONEY)/100 as CASHMONEY,sum(b.ETCMONEY)/100 as ETCMONEY
from
(
selectw.roadno,w.squaddate,w.roadname,w.outvehclass,
sum(w.rec_count) as rec_count1,sum(w.cashmoney) as cashmoney1,sum(w.etcmoney) as etcmoney1
from
(
select *
FROM [Á÷Ë®´óÓÚ0.csv]
where (±¸×¢='Ö»¹ÜÀíµã´æÔÚ') and »ØËͱêÖ¾='»ØËÍ' and(cashmoney+etcmoney)>0
union all
select *
FROM [Á÷ˮСÓÚ0.csv]
where (±¸×¢='Ö»¹ÜÀíµã´æÔÚ') and »ØËͱêÖ¾='»ØËÍ' and(cashmoney+etcmoney)>0
) w
group byw.roadno,w.squaddate,w.roadname,w.outvehclass
) a,[¹ÜÀíµã´æÔÚÖÐÐIJ»´æÔÚµÄÁ÷Ë®_ËùÓÐ.txt] b
where ((b.CASHMONEY+b.ETCMONEY)>0 ) and a.roadno=b.roadno anda.SQUADDATE=b.SQUADDATE
anda.OUTVEHCLASS=b.OUTVEHCLASSNAME
) www
where roadno in (select roadno from [tb_road.txt] where areano=4407)
|
ͼ9 Ò»¿ªÊ¼Ö´ÐÐͼ8ÖеÄSQLÓï¾äʱ£¬±¾»úCPUʹÓÃÂÊÁ¢¿ÌÅÊÉýÖÁ97%

ͼ10 Éú³ÉÖÐÆ¬ÇøÐèÒª»Ø´«µÄÁ÷Ë®Îļþ£¬¹²ÓÐ6Íò¶àÌõ¼Ç¼

ͼ11 ´Ó½áËãÖÐÐÄÊý¾Ý¿âÖÐÓÃÍÚ¾òÉú³ÉµÄËùÓкöÂÔµÄÁ÷Ë®£¬Á½¸öÎļþ¹²250¶àÍòÌõ¼Ç¼

½áÂÛ£º
¶ÔÓÚ¡°½»»¥Ê½¡±ÕâÖÖÊý¾Ý´¦ÀíµÄ·½Ê½À´Ëµ£¬½«½á¹ûµ¼³ö£¬Óá¶DB ²éѯ·ÖÎöÆ÷¡·µÄÇ¿´ó¡¢¸ßЧµÄÎı¾ÎļþµÄ´¦Àí¹¦ÄÜÀ´·ÖÎö´¦Àí£¬Ò²ÊÇÒ»ÖÖ²»´íµÄÑ¡Ôñ¡£²¢ÇÒ¡¶DB
²éѯ·ÖÎöÆ÷¡·µÄЧÂʷdz£Ö®¸ß¡¢´¦Àí·Ç³£·½±ã£¬¿ÉÒÔ½«Ô´Îļþµ±³ÉÒ»¸öÊý¾Ý¿âÖеÄÊý¾Ý±íÒ»ÑùÓñê×¼SQLÓï¾äÀ´½øÐзÃÎÊ¡£
±¾È˵ÄPC»úÖ»²»¹ýÄÚ´æ1GBµÄ2005ÄêµÄDELL ̨ʽPC»ú£¬¶Ô250ÍòÌõ¼Ç¼µÄÎļþ½øÐйØÁª·ÃÎʵÄʱºò£¬Ò²Ö»ÊÇÓò»µ½59ÃëµÄʱ¼ä¾ÍÉú³ÉÁËÒ»¸öÇøÓòµÄ½á¹ûÎļþ£¬ÆÚ¼äCPUʹÓÃÂʸߴï97%
¡£ |