SQL UNION ºÍUNION ALL ²Ù×÷·û
SQL UNION ²Ù×÷·û
UNION ²Ù×÷·ûÓÃÓںϲ¢Á½¸ö»ò¶à¸ö SELECT Óï¾äµÄ½á¹û¼¯¡£
Çë×¢Ò⣬UNION ÄÚ²¿µÄ SELECT Óï¾ä±ØÐëÓµÓÐÏàͬÊýÁ¿µÄÁС£ÁÐÒ²±ØÐëÓµÓÐÏàËƵÄÊý¾ÝÀàÐÍ¡£Í¬Ê±£¬Ã¿Ìõ SELECT Óï¾äÖеÄÁеÄ˳Ðò±ØÐëÏàͬ¡£
SQL UNION Óï·¨
SELECT column_name(s) from table_name1
UNION
SELECT column_name(s) from table_name2
×¢ÊÍ£ºÄ¬Èϵأ¬UNION ²Ù×÷·ûÑ¡È¡²»Í¬µÄÖµ¡£Èç¹ûÔÊÐíÖظ´µÄÖµ£¬ÇëʹÓà UNION ALL¡£
SQL UNION ALL Óï·¨
SELECT column_name(s) from table_name1
UNION ALL
SELECT column_name(s) from table_name2
ÁíÍ⣬UNION ½á¹û¼¯ÖеÄÁÐÃû×ÜÊǵÈÓÚ UNION ÖеÚÒ»¸ö SELECT Óï¾äÖеÄÁÐÃû¡£
ÏÂÃæµÄÀý×ÓÖÐʹÓõÄÔʼ±í£º
Employees_China:
E_IDE_Name
01
Zhang, Hua
02
Wang, Wei
03
Carter, Thomas
04
Yang, Ming
Employees_USA:
E_IDE_Name
01
Adams, John
02
Bush, George
03
Carter, Thomas
04
Gates, Bill
ʹÓà UNION ÃüÁî
ʵÀý
ÁгöËùÓÐÔÚÖйúºÍÃÀ¹úµÄ²»Í¬µÄ¹ÍÔ±Ãû£º
SELECT E_Name from Employees_China
UNION
SELECT E_Name from Employees_USA
½á¹û
E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Gates, Bill
×¢ÊÍ£ºÕâ¸öÃüÁîÎÞ·¨ÁгöÔÚÖйúºÍÃÀ¹úµÄËùÓйÍÔ±¡£ÔÚÉÏÃæµÄÀý×ÓÖУ¬ÎÒÃÇÓÐÁ½¸öÃû×ÖÏàͬµÄ¹ÍÔ±£¬ËûÃǵ±ÖÐÖ»ÓÐÒ»¸öÈ˱»ÁгöÀ´ÁË¡£UNION ÃüÁîÖ»»áÑ¡È¡²»Í¬µÄÖµ¡£
UNION ALL
UNION ALL ÃüÁîºÍ UNION ÃüÁºõÊǵÈЧµÄ£¬²»¹ý UNION ALL ÃüÁî»áÁгöËùÓеÄÖµ¡£
SQL Statement 1
UNION ALL
SQL Statement 2
ʹÓà UNION ALL ÃüÁî
ʵÀý£º
ÁгöÔÚÖйúºÍÃÀ¹úµÄËùÓеĹÍÔ±£º
SELECT E_Name from Employees_China
UNION ALL
SELECT E_Name from Employees_USA
½á¹û
E_Name
Zhang, Hua
Wang, Wei
Carter, Thomas
Yang, Ming
Adams, John
Bush, George
Carter, Thomas
Gates, Bill
ÔÌûµØÖ·£ºhttp://www.w3school.com.cn/sql/sql_union.asp
Ïà¹ØÎĵµ£º
Oracle spool Ó÷¨Ð¡½á[°ëת°ë¼Ó]
¹ØÓÚSPOOL(SPOOLÊÇSQLPLUSµÄÃüÁ²»ÊÇSQLÓï·¨ÀïÃæµÄ¶«Î÷¡£)
¶ÔÓÚSPOOLÊý¾ÝµÄSQL£¬×îºÃÒª×Ô¼º¶¨Òå¸ñʽ£¬ÒÔ·½±ã³ÌÐòÖ±½Óµ¼Èë,SQLÓï¾äÈ磺
select empno||','||ename||','||sal from emp;
spool³£ÓõÄÉèÖÃ
set colsep' ';¡¡¡¡¡¡ //ÓòÊä³ö·Ö¸ô·û
set echo off;¡¡¡¡¡¡¡¡//ÏÔʾstartÆô¶¯µ ......
ÉÏ΢ÈíÍøÕ¾ÏÂÔØsqlserver2005 jdbc driver °ü
1¡¢½«microsoft sql server 2005 jdbc driver\sqljdbc_1.2\chs\sqljdbc.jar°ü copyµ½·¢²¼ÏµÍ³ µÄ web-inf\libĿ¼ÖÐ
2¡¢½«microsoft sql server 2005 jdbc driver\sqljdbc_1.2\chs\auth\x86\sqljdbc_auth.dll Îļþcopyµ½ windows ......
Student(S#,Sname,Sage,Ssex) ѧÉú±í
Course(C#,Cname,T#) ¿Î³Ì±í
SC(S#,C#,score) ³É¼¨±í
Teacher(T#,Tname) ½Ìʦ±í
ÎÊÌ⣺
1¡¢²éѯ“001”¿Î³Ì±È“002”¿Î³Ì³É¼¨¸ßµÄËùÓÐѧÉúµÄѧºÅ£»
select a.S# from (select s#,score from SC where C#='001') a,(sele ......