Unionå 为è¦è¿è¡éå¤å¼æ«æï¼æ以æçä½ãå¦æå并没æå»æè¦å é¤éå¤è¡ï¼é£ä¹å°±ä½¿ç¨Union All
两个è¦èåçSQLè¯å¥ å段个æ°å¿
é¡»ä¸æ ·ï¼èä¸å段类åè¦âç¸å®¹âï¼ä¸è´ï¼ï¼
å¦ææ们éè¦å°ä¸¤ä¸ªselectè¯å¥çç»æä½ä¸ºä¸ä¸ªæ´ä½æ¾ç¤ºåºæ¥ï¼æ们就éè¦ç¨å°unionæè
union allå
³é®åãunion(æ称为èå)çä½ç¨æ¯å°å¤ä¸ªç»æå并å¨ä¸èµ·æ¾ç¤ºåºæ¥ã
unionåunion allçåºå«æ¯,unionä¼èªå¨å缩å¤ä¸ªç»æéåä¸çéå¤ç»æï¼èunion allåå°ææçç»æå
¨é¨æ¾ç¤ºåºæ¥ï¼ä¸ç®¡æ¯ä¸æ¯éå¤ã
Unionï¼å¯¹ä¸¤ä¸ªç»æéè¿è¡å¹¶éæä½ï¼ä¸å
æ¬éå¤è¡ï¼åæ¶è¿è¡é»è®¤è§åçæåºï¼
Union Allï¼å¯¹ä¸¤ä¸ªç»æéè¿è¡å¹¶éæä½ï¼å
æ¬éå¤è¡ï¼ä¸è¿è¡æåºï¼
Intersectï¼å¯¹ä¸¤ä¸ªç»æéè¿è¡äº¤éæä½ï¼ä¸å
æ¬éå¤è¡ï¼åæ¶è¿è¡é»è®¤è§åçæåºï¼
Minusï¼å¯¹ä¸¤ä¸ªç»æéè¿è¡å·®æä½ï¼ä¸å
æ¬éå¤è¡ï¼åæ¶è¿è¡é»è®¤è§åçæåºã
å¯ä»¥å¨æåä¸ä¸ªç»æéä¸æå®Order byåå¥æ¹åæåºæ¹å¼ã
ä¾å¦ï¼
select employee_id,job_id from employees
union
select employee_id,job_id from job_history
以ä¸å°ä¸¤ä¸ªè¡¨çç»æèåå¨ä¸èµ·ãè¿ä¸¤ä¸ªä¾åä¼å°ä¸¤ä¸ªselectè¯å¥çç»æä¸çéå¤å¼è¿è¡å缩ï¼ä¹å°±æ¯ç»æçæ°æ®å¹¶ä¸æ¯ä¸¤æ¡ç»æçæ¡æ°çåãå¦æå¸æå³ä½¿éå¤çç»ææ¾ç¤ºåºæ¥å¯ä»¥ä½¿ç¨union all,ä¾å¦ï¼
2.å¨oracleçscottç¨æ·ä¸æ表emp
select * from emp where deptno >= 20
union all
select * from emp where deptno <= 30
è¿éçç»æå°±æå¾å¤éå¤å¼äºã
æå
³unionåunion allå
³é®åéè¦æ³¨æçé®é¢æ¯ï¼
union å union allé½å¯ä»¥å°å¤ä¸ªç»æéå并ï¼èä¸ä»
ä»
æ¯ä¸¤ä¸ªï¼ä½ å¯ä»¥å°å¤ä¸ªç»æé串起æ¥ã
使ç¨unionåunion allå¿
é¡»ä¿è¯å个select éåçç»ææç¸å个æ°çåï¼å¹¶ä¸æ¯ä¸ªåçç±»åæ¯ä¸æ ·çãä½åååä¸ä¸å®éè¦ç¸åï¼oracleä¼å°ç¬¬ä¸ä¸ªç»æçååä½ä¸ºç»æéçååãä¾å¦ä¸é¢æ¯ä¸ä¸ªä¾åï¼
select empno,ename from emp
union
select deptno,dname from dept
æ们没æå¿
è¦å¨æ¯ä¸ä¸ªselectç»æéä¸ä½¿ç¨order byåå¥æ¥è¿è¡æåºï¼æ们å¯ä»¥å¨æå使ç¨ä¸æ¡order byæ¥å¯¹æ´ä¸ªç»æè¿è¡æåºãä¾å¦ï¼
select empno,ename from emp
union
select deptno,dname from dept
order by ename;
温馨提示:答案为网友推荐,仅供参考