小花爸技術網站

SELECT zy01,zw02,zy02,substr(zy02,4,1),gaz03,gap02,gbd04
  FROM zy_file,gaz_file,zw_file,gap_file,gbd_file
 WHERE zy02=gaz01 
   AND gaz02=0
   AND zy01=zw01
   AND zy02=gap01 AND gap06='N'
   AND gap02=gbd01 AND gbd03 = 0 AND gbd02 = 'standard'
   AND zy01 NOT IN ('999','CLASS-A','CLASS-B','SOC-TEST','S_MIS01') 
ORDER BY zy01,substr(zy02,4,1),zy02,gap02


SELECT zy01,zw02,zy02,
       CASE WHEN substr(zy02,1,1)='m' OR substr(zy02,1,1)='n' THEN 'MENU'
            WHEN substr(zy02,1,2)='cl' OR substr(zy02,1,2)='p_' OR substr(zy02,1,3)='web' OR zy02='udm_tree' THEN 'SYS' 
            ELSE substr(zy02,4,1) END as TYPE,
       gaz03,gap02,
       CASE WHEN (SELECT t.gbd04 FROM gbd_file t WHERE t.gbd01=d.gap02 AND t.gbd02=a.zy02 AND t.gbd03=0) != NULL 
            THEN (SELECT t.gbd04 FROM gbd_file t WHERE t.gbd01=d.gap02 AND t.gbd02=a.zy02 AND t.gbd03=0) 
            ELSE d.gbd04 END as "Name",
       CASE WHEN LENGTH((SELECT k.zy03 FROM zy_file k WHERE k.zy01=a.zy01 AND k.zy02=a.zy02 AND k.zy03 like ('%'||d.gap02||'%'))) >0
            THEN 'Y' 
            ELSE '' END as "Chose"   
  FROM zy_file a,gaz_file b,zw_file c,
       (SELECT gap01,gap02,gbd04 FROM gap_file,gbd_file 
         WHERE gap06='Y' AND gap02=gbd01 
           AND gbd03 = 0 AND gbd02 = 'standard') d
 WHERE a.zy02=b.gaz01 AND b.gaz02=0
   AND a.zy01=c.zw01
   AND a.zy02=d.gap01(+) --AND d.gap06='Y' 
   AND a.zy01 NOT IN ('999','CLASS-A','CLASS-B','SOC-TEST','S_MIS01')  
ORDER BY a.zy01,SUBSTR(a.zy02,4,1),a.zy02,d.gap02   


SELECT zu01,zu04,zx02,zx03,SUM(zu05),COUNT(*) 
  FROM zu_file,zx_file 
 WHERE zu04=zx01 
   AND zu02 < TO_DATE('2014/05/01','yyyy/mm/dd')
   AND zx03 NOT IN ('A1300') AND zx01 NOT IN ('dsc','toptest','topgui')
 GROUP BY zu01,zu04,zx02,zx03
ORDER BY zu01,zu04


進階版
******************************************************************
SELECT zy01,zy02,SUM(zu05),SUM(l_count)
  FROM (SELECT x.zy01,x.zxw01,x.zy02,y.zu05,y.l_count 
            FROM (SELECT a.zy01,b.zxw01,a.zy02
                         FROM (SELECT zy01,zy02 FROM zy_file,zw_file 
                                WHERE zw01=zy01 AND zwacti = 'Y' 
                                   AND substr(zy02,1,1) not in ('m','n')
                                AND zy02 not in ('udm_tree','aoos901','p_contview','p_favorite','p_load_msg','p_view','udm_tree','weblogin','webpasswd')) a,
                              (SELECT zxw01,zxw04 FROM zxw_file,zw_file,zx_file 
                                WHERE zxw04=zw01 AND zwacti='Y'
                                   AND zxw01 = zx01 AND (zx18 is null or zx18 = '')
                                   AND zx03 NOT IN ('A1300') AND zx01 NOT IN ('dsc','toptest','topgui')
                                   AND  zxw03 = '1') b
                      WHERE a.zy01 = b.zxw04) x left join 
                   (SELECT zu01,zu04,SUM(zu05) as zu05,COUNT(*) as l_count 
                       FROM zu_file 
                      --WHERE zu02 < TO_DATE('2014/05/01','yyyy/mm/dd')
                      GROUP BY zu01,zu04) y on x.zxw01=y.zu04 AND x.zy02=y.zu01) 
 GROUP BY zy01,zy02 order by zy02,zy01


select zu01,count(*) From zu_file,zx_file 
 where zu04=zx01  and substr(zu04,1,3) not in ('SOC') and zx03 NOT IN ('A1300') AND zx01 NOT IN ('dsc','toptest','topgui')
   AND zu01 not in('udm_tree','aoos901','p_contview','p_favorite','p_load_msg','p_view','udm_tree','weblogin','webpasswd') 
 group by zu01 order by zu01


select * From zu_file where zu01 = 'aapr910'
*****************************************************************