背著手扇扇子的人
          往事隨風......前事如夢......
          posts - 35,  comments - 17,  trackbacks - 0

          有人問這樣的sql該怎么實現:
          表數據和結構
          ?? ?CODE?NAME????B01????S01????B02????S02
          ????1??????????張三???????數學????80??
          ????1??????????張三????????????????????????????語文????75
          ????2??????????王五???????數學????70??
          ????2??????????王五????
          ????3??????????李四???????數學????50??
          ????3??????????李四???????????????????????????語文????88

          希望查詢出如下結果:
          ?? ?CODE?SUM_STR(NAME)????B01????SUM_STR(S01)????B02????SUM_STR(S02)
          ????1????????????????張三????????????????????數學????????????????80?????????? 語文????????????75
          ????2????????????????王五????????????????????數學????????????????70??
          ????3????????????????李四????????????????????數學????????????????50?????????? 語文?????????????88
          這個問題可以采用自定義的聚集函數來實現:

          create ? or ? replace ?type?strcat_type? as ?object?(
          ????cat_string?
          varchar2 ( 4000 ),
          ????static?
          function ?ODCIAggregateInitialize(cs_ctx? In ?Out?strcat_type)? return ? number ,
          ????member?
          function ?ODCIAggregateIterate(self? In ?Out?strcat_type,value? in ? varchar2 )? return ?

          number ,
          ????member?
          function ?ODCIAggregateMerge(self? In ?Out?strcat_type,ctx2? In ?Out?strcat_type)?

          return ? number ,
          ????member?
          function ?ODCIAggregateTerminate(self? In ?Out?strcat_type,returnValue?Out?

          varchar2 ,flags? in ? number )? return ? number
          )
          /


          ------------------------------------

          create ? or ? replace ?type?body?strcat_type? is
          ??static?
          function ?ODCIAggregateInitialize(cs_ctx? IN ?OUT?strcat_type)? return ? number
          ??
          is
          ??
          begin
          ??????cs_ctx?:
          = ?strcat_type(? null ?);
          ??????
          return ?ODCIConst.Success;
          ??
          end ;

          ??member?
          function ?ODCIAggregateIterate(self? IN ?OUT?strcat_type,
          ???????????????????????????????????????value?
          IN ? varchar2 ?)
          ??
          return ? number
          ??
          is
          ??
          begin
          ??????
          if ?self.cat_string? is ? null ? then
          ?????????self.cat_string?:
          = ?value;
          ??????
          end ? if ;
          ??????
          return ?ODCIConst.Success;
          ??
          end ;

          ??member?
          function ?ODCIAggregateTerminate(self? IN ?Out?strcat_type,
          ?????????????????????????????????????????returnValue?OUT?
          varchar2 ,
          ?????????????????????????????????????????flags?
          IN ? number )
          ??
          return ? number
          ??
          is
          ??
          begin
          ??????returnValue?:
          = ?self.cat_string;
          ??????
          return ?ODCIConst.Success;
          ??
          end ;

          ??member?
          function ?ODCIAggregateMerge(self? IN ?OUT?strcat_type,
          ?????????????????????????????????????ctx2?
          IN ?Out?strcat_type)
          ??
          return ? number
          ??
          is
          ??
          begin
          ?????? if self.cat_string is null then
          ?????????????????? self.cat_string :=? ctx2.cat_string;
          ????????? end if;
          ?????? return ?ODCIConst.Success;
          ??
          end ;

          end ;
          /

          -------------------

          CREATE ? OR ? REPLACE ? FUNCTION ?sum_str(input? varchar2 ?)
          RETURN ? varchar2
          PARALLEL_ENABLE?AGGREGATE?USING?strcat_type;
          /

          -------最后查詢語句:

          select ?code,sum_str(name),?sum_str(b01)?b01,sum_str(s01)?,sum_str(b02)?b02,sum_str(s02)
          from ?javaeye? group ? by ?code? order ? by ?code
          posted @ 2009-01-05 21:55 kebo 閱讀(979) | 評論 (4)編輯 收藏
          定義標注的樣式,這個決定標注顯示的方式,必須定義好
          ?1
          ?$package("com.bct.map");
          ?2?com.bct.map.EncoderMarkerStyle?=?{
          ?3?????'bigEncoder':{
          ?4?????????graphicWidth:24,
          ?5?????????graphicHeight?:?24,
          ?6?????????graphicXOffset?:?-12,
          ?7?????????graphicYOffset?:?-24,
          ?8?????????externalGraphic?:?"scripts/map/img/channel2.png"
          ?9?????},
          10?????'smallEncoder':{
          11?????????graphicWidth:16,
          12?????????graphicHeight?:?16,
          13?????????graphicXOffset?:?-8,
          14?????????graphicYOffset?:?-16,
          15?????????externalGraphic?:?"scripts/map/img/channel.gif"
          16?????},
          17?????'selectStyle':{
          18?????????pointerEvents:?"visiblePainted",
          19?????????border:"border:25?outset?#ff88ff",
          20?????????cursor:?"pointer",
          21?????????graphicWidth:24,
          22?????????graphicHeight?:?24,
          23?????????graphicXOffset?:?-12,
          24?????????graphicYOffset?:?-24,
          25?????????externalGraphic?:?"scripts/map/img/channel2.png"????
          26?????},
          27?????styleMap:?new?OpenLayers.StyleMap({
          28?????????????????????"select":?new?OpenLayers.Style({pointRadius:?24})
          29?????})
          30?}

          marker層,擴展vector層,通過point和style達到marker的效果
          ??1?$package("com.bct.map");
          ??2?$import("com.bct.map.EncoderMarkerStyle");
          ??3?com.bct.map.MarkerVectorLayer?=?OpenLayers.Class(OpenLayers.Layer.Vector,{
          ??4?????/**
          ??5??????*?parameters
          ??6??????*?attribute?filer對象
          ??7??????*/
          ??8?????getFeatureByAttribute?:function(attributes){
          ??9?????????var?feature?=?null;
          ?10?????????for(var?i=0;i<this.features.length;?++i){
          ?11?????????????var?attri?=?this.features[i].attributes;
          ?12?????????????var?find?=?false;
          ?13?????????????for(var?j?in?attributes){
          ?14?????????????????if(attributes[j]?==?attri[j]){
          ?15?????????????????????find?=?true;
          ?16?????????????????}
          ?17?????????????}
          ?18?????????????if(find){
          ?19?????????????????return?this.features[i];?
          ?20?????????????}????????????
          ?21?????????}
          ?22?????
          ?23?????},
          ?24?????addEncorderFeature:function(encNode,location){
          ?25?????????if(encNode&&this.repetitiveCheck(encNode.id)){
          ?26?????????????return;
          ?27?????????}
          ?28?????????var?attributes?=?OpenLayers.Util.extend({},?encNode.attributes);
          ?29?????????var?enc_point?=?new?OpenLayers.Geometry.Point(location.lon,location.lat);
          ?30?????????var?enc_Feature?=?new?OpenLayers.Feature.Vector(enc_point,attributes,com.bct.map.EncoderMarkerStyle['smallEncoder']);
          ?31?????????this
          .addFeatures([enc_Feature]);
          ?32?????????if(encNode.attributes['lon']&&encNode.attributes['lat']&&encNode.attributes['lon'].length>0){
          ?33?????????????return;
          ?34?????????}
          ?35?????????this.updateChannel(encNode.id,location.lon,location.lat);
          ?36?????},
          ?37?????addDeptFeature:function(deptNode,location){
          ?38?????????if(deptNode&&this.repetitiveCheck(deptNode.id)){
          ?39?????????????return;
          ?40?????????}
          ?41?????????var?attributes?=?OpenLayers.Util.extend({},?deptNode.attributes);
          ?42?????????var?enc_point?=?new?OpenLayers.Geometry.Point(location.lon,location.lat);
          ?43?????????var?enc_Feature?=?new?OpenLayers.Feature.Vector(enc_point,attributes,com.bct.map.EncoderMarkerStyle['smallEncoder']);
          ?44?????????
          ?45?????????this.addFeatures([enc_Feature]);
          ?46?????????
          ?47?????},
          ?48?????repetitiveCheck:function(entity_id){
          ?49?????????if(this.getFeatureByAttribute({id:entity_id})){
          ?50?????????????return?true;
          ?51?????????}
          ?52?????????return?false;
          ?53?????},
          ?54?????updateChannel:function(channel_id,lon,lat){
          ?55?????????Ext.Ajax.request({
          ?56????????????????url:?'deviceVideoEncoder.do?method=updateLonlat&id='+channel_id+"&lon="+lon+"&lat="+lat
          ?57?????????});
          ?58?????},
          ?59?????channelMarkerClick:function()?{
          ?60?????????var?features?=?this.selectedFeatures;
          ?61?????????if(features.length?>=0&&features[0])?{
          ?62?????????????feature?=?features[0];????????????
          ?63?????????????var?treeNodeAttribute?=?feature.attributes;
          ?64?????????????var?vedioPopForm?=?new?Ext.FormPanel({
          ?65????????????????????????????????????frame:true,
          ?66?????????????????????????????????labelAlign:?'top',
          ?67?????????????????????????????????bodyStyle:'padding:5px',
          ?68?????????????????????????????????width:?400,
          ?69?????????????????????????????????height:200,
          ?70?????????????????????????????????layout:?'fit',
          ?71?????????????????????????????????items:[{
          ?72?????????????????????????????????????????????xtype:'fieldset',
          ?73?????????????????????????????????????????????title:?'攝像頭信息',
          ?74?????????????????????????????????????????????autoHeight:true,
          ?75?????????????????????????????????????????????autoWidth:true,
          ?76?????????????????????????????????????????????html:"<p><font?color='red'?size='2'>名稱:"+treeNodeAttribute['text']
          ?77?????????????????????????????????????????????+"</font></p><p><font?color='red'?size='2'>通道號:"+treeNodeAttribute['channelNumber']
          ?78?????????????????????????????????????????????+"</font></p><p><font?color='red'?size='2'>設備名稱:"+treeNodeAttribute['deviceunitName']
          ?79?????????????????????????????????????????????+"</font></p><p><font?color='red'?size='2'>所屬部門:"+treeNodeAttribute['deptName']
          ?80?????????????????????????????????????????????+"</font></p><p><font?color='red'?size='2'>經緯度:"+treeNodeAttribute['lon']+","+treeNodeAttribute['lat']
          ?81?????????????????????????????????????}]
          ?82?????????????});
          ?83?????????????var?win?=?new?Ext.Window({
          ?84?????????????????width?:?420,
          ?85?????????????????height:?220,
          ?86?????????????????items?:?vedioPopForm
          ?87?????????????});
          ?88?????????????win.show();????????????
          ?89?????????}
          ?90?????},
          ?91?????cartoonFeature?:function(feature){
          ?92?????????this.drawFeature(feature,com.bct.map.EncoderMarkerStyle['bigEncoder']);
          ?93?????????var?runner?=?new?Ext.util.TaskRunner(1000);
          ?94?????????var?task?=?{
          ?95?????????????run:this.drawFeature,
          ?96?????????????scope:this,
          ?97?????????????args:[feature,com.bct.map.EncoderMarkerStyle['smallEncoder']],
          ?98?????????????interval:?1000
          ?99?????????}
          100?????????runner.start(task);
          101?????},
          102?????removeSelectFeature:function(){
          103?????????var?features?=?this.selectedFeatures;
          104?????????for(var?i=features.length-1;?i>=0;?i--)?{
          105?????????????feature?=?features[i];
          106?????????????this.updateChannel(feature.attributes['id'],"","");
          107?????????}
          108?????????this.destroyFeatures(this.selectedFeatures);
          109?????},
          110?????monitorSelectFeature:function(){????????
          111?????????var?features?=?this.selectedFeatures;
          112?????????if(features.length?>=0&&features[0])?{
          113?????????????feature?=?features[0];????????????
          114?????????????var?treeNodeAttribute?=?feature.attributes;
          115?????????????var?objId="mapAVShow"+treeNodeAttribute['id'];
          116?????????????var?win?=?new?Ext.Window({
          117?????????????????width?:?420,
          118?????????????????height:?420,
          119?????????????????html:"<div?id='mapEncoder'?width='100%'?height='100%'><object?width='100%'?height='100%'?id='"+objId+"'?classid='clsid:574B47E8-A366-4AB9-B2EA-57F145CA3780'></object></div>"
          120?????????????});????????????
          121?????????????win.show();
          122?????????????Ext.lib.Ajax.request('GET','channel.do?method=getSiteId&accept=json&id='+treeNodeAttribute['id'],
          123???????????????????????????????{success:?function(o){
          124?????????????????????????????????????????var?encoderObj;
          125?????????????????????????????????????????encoderObj=Ext.util.JSON.decode(o.responseText);
          126?????????????????????????????????????????$import("com.bct.monitor.mapAVShow");
          127?????????????????????????????????????????var?avshowObj=document.getElementById(objId);
          128?????????????????????????????????????????var?avshow=new?com.bct.monitor.mapAVShow(avshowObj,
          129?????????????????????????????????????????encoderObj[0].siteId,encoderObj[0].enCoderId,encoderObj[0].diveceUnitTypeId,'');
          130?????????????????????????????????????????avshow.startVideo();
          131?????????????????????????????????????????win.on("destroy",function?del(){
          132??????????????????????????????????????????????????????????avshow.stopVideo();
          133?????????????????????????????????????????});
          134?????????????????????????????????}
          135???????????????????????????????});?????????????
          136?????????}
          137?????}
          138?});


          posted @ 2008-09-04 14:12 kebo 閱讀(3956) | 評論 (1)編輯 收藏

          <2008年9月>
          31123456
          78910111213
          14151617181920
          21222324252627
          2829301234
          567891011

          常用鏈接

          留言簿(1)

          隨筆分類

          隨筆檔案

          文章檔案

          相冊

          收藏夾

          朋友

          搜索

          •  

          積分與排名

          • 積分 - 23213
          • 排名 - 1598

          最新評論

          閱讀排行榜

          評論排行榜

          主站蜘蛛池模板: 堆龙德庆县| 门头沟区| 嵊州市| 新宁县| 东乡族自治县| 岐山县| 土默特左旗| 清镇市| 磐安县| 栾城县| 丹寨县| 来凤县| 延津县| 石柱| 夏津县| 高碑店市| 沙雅县| 庆安县| 林芝县| 黎城县| 奉化市| 常宁市| 嘉峪关市| 长乐市| 夹江县| 甘南县| 富裕县| 项城市| 垦利县| 沁阳市| 上犹县| 台前县| 武夷山市| 常宁市| 东阳市| 长治市| 石嘴山市| 宕昌县| 重庆市| 株洲市| 孙吴县|