查詢各部門中工資比本部門平均工資高的員工的員工號(hào), 姓名和工資
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 單位編號(hào) , 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','長(zhǎng)呵城龋縣',
'210240000000','開(kāi)發(fā)區(qū)',
'210241000000','保稅區(qū)',
'210242000000','長(zhǎng)興島臨港工業(yè)區(qū)',
'210243000000','花園口經(jīng)濟(jì)區(qū)',
'210244000000','金普新區(qū)',
'210281000000','瓦房店市',
'210282000000','普蘭店市',
'210283000000','莊河市',
'210299000000','大連市市本級(jí)',
'210201000000','大連市本級(jí)') 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;
