表联接

1.多表联接

CREATE TABLE sal(
empno NUMBER(5),
sal NUMBER,
sal_date DATE
);

INSERT INTO sal(empno,sal,sal_date)
SELECT empno,1000,to_date('2010-1-1','yyyy-mm-dd')
FROM emp;
--所有10部门的另外拿500块
INSERT INTO sal(empno,sal,sal_date)
SELECT empno,500,to_date('2010-1-1','yyyy-mm-dd')
FROM emp WHERE deptno=10;
INSERT INTO sal(empno,sal,sal_date)
SELECT empno,2000,to_date('2010-2-1','yyyy-mm-dd')
FROM emp;
COMMIT;
SELECT * FROM sal;

   1.1：select dept.deptno,dept.dname,emp.ename,sal.sal,sal.sal_date
from dept,emp,sal
where dept.deptno=emp.deptno and emp.empno=sal.empno
order by dept.deptno;

   1.2：select dept.deptno,dept.dname,ename,sal.sal,sal.sal_date
from emp join dept on dept.deptno=emp.deptno
         join sal on emp.empno=sal.empno;

--2、显示 某个月 拿过2笔工资以上的人

SELECT to_char(sal_date,'yyyy.mm') yf,empno,COUNT(*) cs
FROM sal
GROUP BY to_char(sal_date,'yyyy.mm'),empno
HAVING COUNT(*)>1

 

SELECT empno,ename FROM emp
WHERE empno IN (
  SELECT empno
  FROM sal
  GROUP BY to_char(sal_date,'yyyy.mm'),empno
  HAVING COUNT(*)>1
)

--如果还要知道是哪个月，领取了几笔
SELECT emp.empno,emp.ename,t.yf,t.cs FROM emp,
(
  SELECT to_char(sal_date,'yyyy.mm') yf,empno,COUNT(*) cs
  FROM sal
  GROUP BY to_char(sal_date,'yyyy.mm'),empno
  HAVING COUNT(*)>1
) t
WHERE emp.empno=t.empno