查詢各部門中工資比本部門平均工資高的員工的員工號, 姓名和工資

SELECT A.deptno,A.ename,A.sal FROM EMP A,(select deptno,AVG(SAL) AS "平均工資" from emp group by deptno) B

WHERE A.DEPTNO=B.deptno AND SAL>b.平均工資

image.png

查詢

SELECT /*+parallel(8)*/AC02.AAA027,
             AC02.AAB034,
             PRM_ISSUE,
             ab01.aab019,
             AC01.AAC084,
             AC01.AAC004,
             AC02.AAC031,
             AC01.AAC028,
             AC01.AAC161,
             COUNT(DISTINCT AC02.AAC001)
        FROM AC02, v_ab01 AB01, AC01, AB02
       WHERE AC01.AAC001 = AC02.AAC001
         AND AB01.AAB001 = AC02.AAB001
         AND AC02.AAB001 = AB02.AAB001
         AND AC02.AAE140 = AB02.AAE140
         AND ac02.aae140 = '110'
         and ac02.aac031 <> '3'
         and ac01.aac084 = '0'
         AND AC01.AAC032 = '1'
         AND NOT EXISTS(SELECT 1
                              FROM AC60
                             WHERE AAC001 = AC01.AAC001
                               AND SUBSTR(AAC121, 1, 1) IN ('2', '3')
                               AND AAE116 IN ('1', '2'))
         and exists(select 1 from sicp4_collectioncenter.V_AC43ACI8 
                     where aac001=ac01.aac001 and aae140='110')
         AND SUBSTR(TO_CHAR(AC02.AAC049),0,6) <= 統(tǒng)計(jì)年月
         AND NOT EXISTS(SELECT 1 FROM tjbtq WHERE AAC001=AC01.AAC001)
       GROUP BY AC02.AAA027,
                AC02.AAB034,
                ab01.aab019,
                AC02.AAE140,
                AC01.AAC084,
                AC01.AAC004,
                AC02.AAC031,
                AC01.AAC028,
                AC01.AAC161;

UPDATE A表
   SET 字段 =
       (SELECT 字段B FROM B表 WHERE AAC001 = A表.AAC001)
 WHERE EXISTS (SELECT 1 FROM B表 WHERE AAC001 = A表.AAC001);
 
 


 update hjian_2 set HJIAN=(SELECT DISTINCT HJIAN FROM hjian WHERE AAC001 = hjian_2.AAC001)
 
 WHERE EXISTS (SELECT 1 FROM hjian WHERE AAC001 = hjian_2.AAC001);

SELECT ic91.*,
                                case
                                  when prm_aab301 like '21%' then
                                   aae269
                                  else
                                   nvl(aae382, 0) - lag(aae382, 1, 0)
                                   over(order by aae001, aae041) -
                                   nvl(aae381, 0)
                                end lx,
                                case
                                  when prm_aab301 like '21%' then
                                   aae269
                                  else
                                   nvl(aae273, 0) - lag(aae273, 1, 0)
                                   over(order by aae001, aae041) -
                                   nvl(aae264, 0)
                                end grlx
                           FROM IC91
                          WHERE AAC001 = PRM_AAC001
                            AND AAZ341 = PRM_AAZ341
                            AND aae382 > 0
                          ORDER BY AAE001, AAE041

select ename,sal from
   (
     select ename,sal,rownum r from  (select ename,sal from emp order by sal desc)
   )
  where r<=3

select a.aab001,(select aab004 from sicp4_basicinfocenter.abp1 where aab001 = a.aab001) 單位名稱,aae002,aae003,aab031,aae020,aae023,c.bab082,a.aab034 from sicp4_collectioncenter.ab07 a,lnsi_commonbusiemp.aba1 c WHERE a.aab001 = c.aab001 and
AAE003 BETWEEN 202002 AND 202012 AND AAE002<202103 AND AAA115='20' and aae491 = '1' and aae132 =  '1' and bab082 in ('2','3','4') and aae020 <> '0';

SELECT P.AAB001 單位編號 , P.aab004 AS 單位名稱,P. AAE004 AS 聯(lián)系人, P.AAE005 AS 聯(lián)系電話, G.MS AS 最后繳納時(shí)間,G.aab119 AS 繳納人數(shù),
decode(P.AAB034,'210200000000','大連市',
                   '210202000000','中山區(qū)',
                   '210203000000','西崗區(qū)',
                   '210204000000','沙河口區(qū)',
                   '210211000000','甘井子區(qū)',
                   '210212000000','旅順口區(qū)',
                   '210213000000','金州區(qū)',
                   '210214000000','普蘭店區(qū)',
                   '210224000000','長?？h',
                   '210240000000','開發(fā)區(qū)',
                   '210241000000','保稅區(qū)',
                   '210242000000','長興島臨港工業(yè)區(qū)',
                   '210243000000','花園口經(jīng)濟(jì)區(qū)',
                   '210244000000','金普新區(qū)',
                   '210281000000','瓦房店市',
                   '210282000000','普蘭店市',
                   '210283000000','莊河市',
                   '210299000000','大連市市本級',
                   '210201000000','大連市本級') AS 經(jīng)辦機(jī)構(gòu)
  FROM SICP4_BASICINFOCENTER.AB01 P,
       (SELECT E.AAB119, F.ms,F.aab001
          FROM SICP4_COLLECTIONCENTER.AB07 E,
               (SELECT AAB001, MAX(AAE003) AS MS
                  FROM SICP4_COLLECTIONCENTER.AB07
                 WHERE AAB001 IN
                       (SELECT AAB001
                          FROM SICP4_BASICINFOCENTER.AB01
                         WHERE AAB001 IN (2102071705969))
                 GROUP BY AAB001) F
         WHERE E.AAB001 = F.AAB001
           AND E.AAE003 = F.MS) G WHERE P.AAB001 = G.AAB001;
       

select aab001,aab119,aae003 from sicp4_collectioncenter.ab07 a  where  aab001 in(2102000192627,
2102000192645,
2102000193106,
2102000191305,
2102000000000,
2102000190402,
2102000190720,
2102000191602,
2102000190401,
2102000192692,
2102000192706,
2102000192628,
2102000192802,
2102000192900,
2102000191405,
2102000192726) and aae491 = '1'
and aae003 = (select max(aae003) from sicp4_collectioncenter.ab07 where aab001 = a.aab001)

WITH TEMP_A AS
 (SELECT AAB001, MAX(AAE003) AS AAE003
    FROM SICP4_COLLECTIONCENTER.AB07
   WHERE AAB001 IN (2102000192627,
                    2102000192645,
                    2102000193106,
                    2102000191305,
                    2102000000000,
                    2102000190402,
                    2102000190720,
                    2102000191602,
                    2102000190401,
                    2102000192692,
                    2102000192706,
                    2102000192628,
                    2102000192802,
                    2102000192900,
                    2102000191405,
                    2102000192726)
   GROUP BY AAB001)
SELECT A. AAB001, A.AAE003, SUM(AAB119) RS
  FROM TEMP_A A, SICP4_COLLECTIONCENTER.AB07 B
 WHERE A.AAB001 = B.AAB001 AND A.AAE003 = B.AAE003
 GROUP BY A.AAB001, A.AAE003;