create table student(id number primary key,name varchar2(20),gender varchar2(2));
创建表student,包含两个主要字段name 和gender.
插入几条数据。
ID NAME GENDER
---------- -------------------- --
1 张三 男
2 李四 男
3 李四 男
4 李四 女
5 张三 女
6 张三 男
可以看到id(1,6)的name和gender相同,id(2,3)的name和gender也相同。
现在提个需求:列出name和gender都相同的记录。写sql
SQL> SELECT s.name,s.gender
2 FROM student s
3 WHERE ((SELECT COUNT(*)
4 FROM student
5 WHERE name = s.name and gender = s.gender) > 1)
6 ORDER BY name,gender DESC;
NAME GE
-------------------- --
李四 男
李四 男
张三 男
张三 男
本题目思路是连表查询,在where子句中使用子查询,分离出两个字段作为筛选条件,而后直接得出所有记录。
scott中的emp表:
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- --------- ------
7369 SMITH CLERK 7902 1980-12-17 800.00 20
7499 ALLEN SALESMAN 7698 1981-2-20 1600.00 300.00 30
7521 WARD SALESMAN 7698 1981-2-22 1250.00 500.00 30
7566 JONES MANAGER 7839 1981-4-2 2975.00 20
7654 MARTIN SALESMAN 7698 1981-9-28 1250.00 1400.00 30
7698 BLAKE MANAGER 7839 1981-5-1 2850.00 30
7782 CLARK MANAGER 7839 1981-6-9 2450.00 10
7788 SCOTT ANALYST 7566 1987-4-19 3000.00 20
7839 KING PRESIDENT 1981-11-17 5000.00 10
7844 TURNER SALESMAN 7698 1981-9-8 1500.00 0.00 30
7876 ADAMS CLERK 7788 1987-5-23 1100.00 20
7900 JAMES CLERK 7698 1981-12-3 950.00 30
7902 FORD ANALYST 7566 1981-12-3 3000.00 20
7934 MILLER CLERK 7782 1982-1-23 1300.00 10
需求:删除表中job相同并且empno最大的记录。(一条语句)
方案:delete from emp where empno in (select max(empno) from emp group by job having count(1) >1);