
COMPUTER ENGINEERING
1999 25 12 vol.25 No.12 1999



ٲѯTMNϵͳʵ
С̡񼡡
TMN(Ź)ϵͳǱ֤ЧתزٵֶΡĿǰӦԼеTMNϵͳܱȽȫ棬һͻ⣬ݲѯܲ롣ľһһֶصƷʹѯٶȵõߡΪ˵ⷽ㣬һдԵ⣬йرȥѯ 
1 TMNϵͳԱȥĴ
ڵϵͳУҪļضΪÿ̨мȺÿһСʱ³мȺڱСʱڵĻݡϵͳһýṹ1ݱ洢ȥݡǽΪԭʼݱ
1 ԭʼݱṹ
ԭʼ--BD_QHW
ͳʱ
THSK-pkeyмȺ
ZJQBH-pkey
JHJBH-pheԺ
HSCS(int)ͨ
JTCS(int)ָ
......(int)
1999-1-1 0:0ZJQ_A1JHJ_A10002000......
1999-1-1 0:0ZJQ_A2JHJ_A9001900......
1999-1-1 0:0ZJQ_B1JHJ_B6001000......
1999-1-1 0:0ZJQ_A1JHJ_A11002000......
1999-1-1 0:0ZJQ_A2JHJ_A9501900......

ڱ1У"TJSK+ZJQBH+JHJBH"ɱΪ۴һбʾ"JHJ_A"мȺ"ZJQ_A1""1999-1-1 0:001999-1-1 0:59:59"һСʱڵĸݣбʾͬһмȺ"1999-1-1 1:001999-1-1 1:59:59"һСʱڵĸݡݴ洢һΪһͳڣһݱա
ȥݵĲѯҪ4֣
(1)Сʱβѯѯ"1999-2-1" "1999-2-10" ÿ 9 һСʱεĳмȺȥ(ۼӻƽ)ʹƵԼռ60%
(2)ղѯѯ "1999-2-1 " "1999-2-10 " һ10ĳмȺȥ(ۼӻƽ)ʹƵԼռ20%
(3)ʱѯ "1999-2-1 8:00" "1999-2-10 18:00" һʱĳмȺȥ(ۼӻƽ)ʹƵԼռ10%
(4)ѯ "1999-2-1 8:00" һСʱĳмȺȥݣʹƵԼռ10% 
2 ٲѯԱȥĴ
µƷ߲ѯٶȣر"Сʱβѯ"ٶȣΪõĲѯ
ݿϵͳУѯٶȼŻ⣬Ƶ⡣ӲﵽõʱõݱƾͳΪܵĸ;о80%ܸĽIOĸơˣIOرǴIO·Ƴ㡣
Ϊ˴ﵽһĿģ·ݴ洢ؼĸĽ
(1)"Χѯ"תΪ"ѯ"ԭϵͳ³д洢洢""ݡϵͳУĵһд洢ͳڵһСʱݣڶд洢ΪһСʱݼϵڶСʱݣƣҲ˵Ϊ洢ϼݣ2ʾ
2 洢ʽԱ
ͳʱԭ ϵ ͳ ϵ ͳ
1999-1-1 0:0010001000
1999-1-1 1:009001000 + 900 = 1900
1999-1-1 2:0010501900 + 1050 = 2950

ԽµĲѯɨʱ䷶Χֻѯʱյ㴦ݣӶ"Χѯ"תΪ"ѯ"
(2)ʱƬʽΪʵҵϣܼ򵥵ذձ2ʾСʱۼӣ뽫𲽻ܵĹ25ʱƬϽУͼ1ʾ 

ͼ1 ʱƬ
ͼ1У"Сʱ0ϼ"Ϊʱ䷶Χڵĵ0СʱƬӦݱΪ"ٲСʱ0""ٲСʱ0"棬һΪ"1999-1-1 0:000:59:59"ĻݣڶΪ"1999-1-2 0:000:59:59"Ļݣټϵһм"1999-1-1 0:00 0:59:59"Ļݣơһ24Сʱ24ʱƬֱӦݱ"ٲСʱ0"ֱ "ٲСʱ23"24ṹ3ʾ
3 ٲСʱ
ٲСʱ0--BD_QHWHOUR0
ͳʼʱ
TJSK-pkeyмȺ
ZJQBH-pkey
JHJBH-pkeyԺ
SHCS(int)ͨ
JTCS(int)
(int)(int)
COUNT
1999-1-1 0:00ZJQ_A1JHJ_A10002000...1
1999-1-1 0:00ZJQ_A2JHJ_A9001900...1
1999-1-2 0:00ZJQ_A1JHJ_A21004000...2
1999-1-2 0:00ZJQ_A2JHJ_A18503800...2

3У"TJSK+ZJQBH+JHJBH"ɱΪ۴
˵һ㣬ʵУԭ򽻻ܻȱʧĳСʱݣΪʹƽֵȷֶ"-COUNT"¼³ÿһмȺʵݴٲСʱ⻹һ"ٲձ"ṹ3ͬУһд洢"1999-1-1"ȫ24СʱۼӵĽڶд洢"1999-1-2"ȫ24СʱۼӵĽټϵһУ"1999-1-1"ȫݣơ 
3 ѯܶԱ
ǰܵҪIOĸƣˣѯõҳĿԽ٣IOԽ٣ѯɵʱԽ̡ʵʶԱУݿΪSybase11.5ԭʼݱ50¼ʱ"1999-1-1 0:00""1999-7-1 0:00 "
(1)Сʱβѯ
磬ѯJHJ_A"1999-1-10""1999-1-20"ÿ9ȥݺϼֵ
1) ԭʼвѯ
select ZJQBH, sum(SHCS), sum(JTCS) from BD_
QHW
where JHJBH = 'JHJ_A' 
and ( TJSK > '1999-1-10 0:00'and TJSK < '1999-1-20 23:59 ' ) 
and datepart(hour, TJSK) = 9 
group by ZJQBH 
2) ٲвѯ
.ȡʱ
select ZJQBH, SHCS, JTCS from BD_
QHWHOUR9
where JHJBH = 'JHJ_A' 
and ( TJSK > '1999-1-9 0:00'and TJSK < '1999-1-9 23:59 ' )
.ȡʱյ
select ZJQBH, SHCS, JTCS from BD_QHWHOUR9 
where JHJBH = 'JHJ_A' 
and ( TJSK > '1999-1-20 0:00'and TJSK < '1999-1-20 23:59 ' ) 
.
յֵȥмȺӦֵ
3)ܶԱ
Ƚַɲѯȡҳ()ɻƳͼ2ߣ

ͼٲѯ
--------------ԭʼѯ

ͼ2ԿٲѯõұȶԭʼѯʱȵȡԽԽҳӦʱԼӳ
(2)ղѯ
ԭʼѯȡʱڵҳٲѯֻȡ"ٲպϼ"еյ㴦ݣߡ
(3)ʱѯ
磬ѯ "1999-2-1 8:00" "1999-2-10 18:00" Ļ֮ͣΪΣ"1999-2-1 8:001999-2-1 23:00""1999-2-10 0:001999-2-10 18:00"ն"1999-2-21999-2-9"Уնν"ٲպϼ"ȡõߡ
(4)ѯ
ѯܴٲеõԸơ
4 ԿٲѯĽһ̽
ٲάϵͳԶɣÿһ¼򴥷ٲά̡ͨύȷԭʼٲݵһԡ
ٲķԭʼĻ24СʱϼƼ1պϼƱСʱϼƱպϼƱĴСΪԭʼ1/24ϵͳ25/24Ĵ洢ռ䡣Ŀǰ۸£ԿռΪۻȡֵõġ⣬ݱݵãΪٲɵõԭʼݡ
ٲʹݷɢֲӶСͻĸʡ
ǰУֻǱȽ˶ȡҳĶ٣ûָǴ̶߼SQL Server ŻΪ̶ĴΪ18߼ĴΪ2ԭʼľ޴ԣ޷ȫڸٻϣˣҳӴ̶ȡٲСöࡣûĲѯϰߣ"Сʱβѯ"ֻԻæʱ9㡢3СʱνУˣԽæʱӦСʱٲڸٻϣӶкܸߵCacheʡ
5 
õݿԼйеϵͳţݿϵͳЧеҪ֤Ļݴ洢ٲѯɹںʵTMNϵͳСһʵֹУᵽϵͳӦõʵںһӦóɹҪ֤
ο
1 صۺܼϵͳ(ڲ).ʡʵ֣ ԰1996
2 ɺ.Sybaseԭ:߼ϵͳܵ.йˮˮ 磬1998
3 []Paulsell K.Sybase SQL Server11 ܼŻ.ϣ Թ˾1998
