Oracle SQL多列子查询

Oracle SQL 多列子查询

（1）查找0201教研室与0101教研室职称和工资完全相同的教师

在teacher表内查找0201教研室中与0101教研室的职称和工资完全相同的教师。

SELECT tname，titleno，sal，rno FROM teacher

WHERE rno='0201' AND (titleno，sal) IN

(SELECT titleno，sal FROM teacher WHERE rno='0101')

运行结果为：

消息102，级别15，状态1，第2行

'，' 附近有语法错误。

之所以出错，是因为SQL Server只支持单列查询而不支持多列查询。

（2）查找部门20与部门30工资与补助完全相同的雇员。

在emp表内查找部门20与部门30工资与补助完全相同的雇员信息。

SELECT ename，job，sal，comm，deptno

FROM emp

WHERE deptno=20 AND (sal，comm) IN

(SELECT sal，comm FROM emp WHERE deptno=30)

因为emp表中无相应的数据，所以运行结果为空

此时，WHERE后的(sal,comm)放在括号中，子查询用sal、comm与之完全匹配，如果只要求sal或comm其中的一个与部门30中的匹配即可，则SQL代码如下所示：

SELECT ename，job，sal，comm，deptno

FROM emp

WHERE deptno=20 AND sal IN (SELECT sal FROM emp WHERE deptno=30)

AND comm IN (SELECT comm FROM emp WHERE deptno=30) （3）显示工资高于本部门平均工资的员工信息。

SELECT a.ename，a.sal，a.deptno

FROM emp a

WHERE a.sal >

(SELECT AVG(sal) FROM emp b WHERE b.deptno = a.deptno)

运行结果为：

ENAME SAL DEPTNO

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

ALLEN 1600.00 30

JONES 2975.00 20

BLAKE 2850.00 30

SCOTT 3000.00 20

KING 5000.00 10

FORD 3000.00 20

6 rows selected

如果再同时显示各部门的平均工资，则代码如下所示：

SELECT a.ename，a.sal，a. deptno，b.salavg

FROM emp a，(SELECT deptno，AVG(sal) salavg

FROM emp GROUP BY deptno) b

WHERE a.sal > b.salavg AND a. deptno = b. deptno

运行结果为：

ENAME SAL DEPTNO SALAVG

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

ALLEN 1600.00 30 1566.66666

JONES 2975.00 20 2175

BLAKE 2850.00 30 1566.66666 SCOTT 3000.00 20 2175 KING 5000.00 10 2916.66666 FORD 3000.00 20 2175

6 rows selected