{"id":234,"date":"2023-02-24T05:54:42","date_gmt":"2023-02-23T21:54:42","guid":{"rendered":"https:\/\/www.appblog.cn\/?p=234"},"modified":"2023-04-30T15:21:36","modified_gmt":"2023-04-30T07:21:36","slug":"mysql-union-operator","status":"publish","type":"post","link":"https:\/\/www.appblog.cn\/index.php\/2023\/02\/24\/mysql-union-operator\/","title":{"rendered":"MySQL UNION \u64cd\u4f5c\u7b26"},"content":{"rendered":"

MySQL UNION \u64cd\u4f5c\u7b26\u7528\u4e8e\u8fde\u63a5\u4e24\u4e2a\u4ee5\u4e0a\u7684 SELECT \u8bed\u53e5\u7684\u7ed3\u679c\u7ec4\u5408\u5230\u4e00\u4e2a\u7ed3\u679c\u96c6\u5408\u4e2d\u3002\u591a\u4e2a SELECT \u8bed\u53e5\u4f1a\u5220\u9664\u91cd\u590d\u7684\u6570\u636e\u3002<\/p>\n

\u8bed\u6cd5<\/h2>\n

MySQL UNION \u64cd\u4f5c\u7b26\u8bed\u6cd5\u683c\u5f0f\uff1a<\/p>\n

<\/p>\n

SELECT expression1, expression2, ... expression_n\nFROM tables\n[WHERE conditions]\nUNION [ALL | DISTINCT]\nSELECT expression1, expression2, ... expression_n\nFROM tables\n[WHERE conditions];<\/code><\/pre>\n
\u53c2\u6570\uff1a<\/p>\n
    \n
  • expression1, expression2, ... expression_n<\/code>: \u8981\u68c0\u7d22\u7684\u5217<\/li>\n
  • tables<\/code>: \u8981\u68c0\u7d22\u7684\u6570\u636e\u8868<\/li>\n
  • WHERE conditions<\/code>: \u53ef\u9009\uff0c \u68c0\u7d22\u6761\u4ef6<\/li>\n
  • DISTINCT<\/code>: \u53ef\u9009\uff0c\u5220\u9664\u7ed3\u679c\u96c6\u4e2d\u91cd\u590d\u7684\u6570\u636e\u3002\u9ed8\u8ba4\u60c5\u51b5\u4e0b UNION \u64cd\u4f5c\u7b26\u5df2\u7ecf\u5220\u9664\u4e86\u91cd\u590d\u6570\u636e\uff0c\u6240\u4ee5 DISTINCT \u4fee\u9970\u7b26\u5bf9\u7ed3\u679c\u6ca1\u5565\u5f71\u54cd<\/li>\n
  • ALL<\/code>: \u53ef\u9009\uff0c\u8fd4\u56de\u6240\u6709\u7ed3\u679c\u96c6\uff0c\u5305\u542b\u91cd\u590d\u6570\u636e<\/li>\n<\/ul>\n
    \u6f14\u793a\u6570\u636e\u5e93<\/h2>\n
    "websites" \u8868\u7684\u6570\u636e\uff1a<\/p>\n
    mysql> SELECT * FROM websites;\n+----+----------------+---------------------------+-------+---------+\n| id | name           | url                       | alexa | country |\n+----+----------------+---------------------------+-------+---------+\n| 1  | Google         | https:\/\/www.google.cm\/    | 1     | USA     |\n| 2  | \u6dd8\u5b9d           | https:\/\/www.taobao.com\/   | 13    | CN      |\n| 3  | APP\u5f00\u53d1\u6280\u672f\u535a\u5ba2| http:\/\/www.appblog.cn\/    | 8888  | CN      |\n| 4  | \u5fae\u535a           | http:\/\/weibo.com\/         | 20    | CN      |\n| 5  | Facebook       | https:\/\/www.facebook.com\/ | 3     | USA     |\n| 7  | stackoverflow  | http:\/\/stackoverflow.com\/ |   0   | IND     |\n+----+----------------+---------------------------+-------+---------+<\/code><\/pre>\n
    "apps" APP \u7684\u6570\u636e\uff1a<\/p>\n
    mysql> SELECT * FROM apps;\n+----+------------+-------------------------+---------+\n| id | app_name   | url                     | country |\n+----+------------+-------------------------+---------+\n|  1 | QQ APP     | http:\/\/im.qq.com\/       | CN      |\n|  2 | \u5fae\u535a APP   | http:\/\/weibo.com\/       | CN      |\n|  3 | \u6dd8\u5b9d APP   | https:\/\/www.taobao.com\/ | CN      |\n+----+------------+-------------------------+---------+\n3 rows in set (0.00 sec)<\/code><\/pre>\n
    SQL UNION \u5b9e\u4f8b<\/h2>\n
    \u4e0b\u9762\u7684 SQL \u8bed\u53e5\u4ece "websites" \u548c "apps" \u8868\u4e2d\u9009\u53d6\u6240\u6709\u4e0d\u540c\u7684country\uff08\u53ea\u6709\u4e0d\u540c\u7684\u503c\uff09\uff1a<\/p>\n
    SELECT country FROM Websites\nUNION\nSELECT country FROM apps\nORDER BY country;<\/code><\/pre>\n
    \u6267\u884c\u4ee5\u4e0a SQL \u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n
    mysql> SELECT country FROM Websites\n    -> UNION\n    -> SELECT country FROM apps\n    -> ORDER BY country;\n+---------+\n| country |\n+---------+\n| CN      |\n| IND     |\n| USA     |\n+---------+<\/code><\/pre>\n
    \u6ce8\u91ca\uff1aUNION \u4e0d\u80fd\u7528\u4e8e\u5217\u51fa\u4e24\u4e2a\u8868\u4e2d\u6240\u6709\u7684country\u3002\u5982\u679c\u4e00\u4e9b\u7f51\u7ad9\u548cAPP\u6765\u81ea\u540c\u4e00\u4e2a\u56fd\u5bb6\uff0c\u6bcf\u4e2a\u56fd\u5bb6\u53ea\u4f1a\u5217\u51fa\u4e00\u6b21\u3002UNION \u53ea\u4f1a\u9009\u53d6\u4e0d\u540c\u7684\u503c\u3002\u8bf7\u4f7f\u7528 UNION ALL \u6765\u9009\u53d6\u91cd\u590d\u7684\u503c\uff01<\/p>\n
    SQL UNION ALL \u5b9e\u4f8b<\/h2>\n
    \u4e0b\u9762\u7684 SQL \u8bed\u53e5\u4f7f\u7528 UNION ALL \u4ece "websites" \u548c "apps" \u8868\u4e2d\u9009\u53d6\u6240\u6709\u7684country\uff08\u4e5f\u6709\u91cd\u590d\u7684\u503c\uff09\uff1a<\/p>\n
    SELECT country FROM websites\nUNION ALL\nSELECT country FROM apps\nORDER BY country;<\/code><\/pre>\n
    \u6267\u884c\u4ee5\u4e0a SQL \u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n
    mysql> SELECT country FROM websites\n    -> UNION ALL\n    -> SELECT country FROM apps\n    -> ORDER BY country;\n+---------+\n| country |\n+---------+\n| CN      |\n| CN      |\n| CN      |\n| CN      |\n| CN      |\n| CN      |\n| IND     |\n| USA     |\n| USA     |\n+---------+<\/code><\/pre>\n
    \u5e26\u6709 WHERE \u7684 SQL UNION ALL<\/h2>\n
    \u4e0b\u9762\u7684 SQL \u8bed\u53e5\u4f7f\u7528 UNION ALL \u4ece "websites" \u548c "apps" \u8868\u4e2d\u9009\u53d6\u6240\u6709\u7684\u4e2d\u56fd(CN)\u7684\u6570\u636e\uff08\u4e5f\u6709\u91cd\u590d\u7684\u503c\uff09\uff1a<\/p>\n
    SELECT country, name FROM websites\nWHERE country='CN'\nUNION ALL\nSELECT country, app_name FROM apps\nWHERE country='CN'\nORDER BY country;<\/code><\/pre>\n
    \u6267\u884c\u4ee5\u4e0a SQL \u8f93\u51fa\u7ed3\u679c\u5982\u4e0b\uff1a<\/p>\n
    mysql> SELECT country, name FROM websites\n    -> WHERE country='CN'\n    -> UNION ALL\n    -> SELECT country, app_name FROM apps\n    -> WHERE country='CN'\n    -> ORDER BY country;\n+---------+-----------------+\n| country | name            |\n+---------+-----------------+\n| CN      | \u6dd8\u5b9d            |\n| CN      | QQ APP          |\n| CN      | APP\u5f00\u53d1\u6280\u672f\u535a\u5ba2 |\n| CN      | \u5fae\u535a APP        |\n| CN      | \u5fae\u535a            |\n| CN      | \u6dd8\u5b9d APP        |\n+---------+-----------------+<\/code><\/pre>\n
    \u603b\u7ed3<\/h2>\n
    UNION \u8bed\u53e5\uff1a\u7528\u4e8e\u5c06\u4e0d\u540c\u8868\u4e2d\u76f8\u540c\u5217\u4e2d\u67e5\u8be2\u7684\u6570\u636e\u5c55\u793a\u51fa\u6765\uff08\u4e0d\u5305\u62ec\u91cd\u590d\u6570\u636e\uff09
    \nUNION ALL \u8bed\u53e5\uff1a\u7528\u4e8e\u5c06\u4e0d\u540c\u8868\u4e2d\u76f8\u540c\u5217\u4e2d\u67e5\u8be2\u7684\u6570\u636e\u5c55\u793a\u51fa\u6765\uff08\u5305\u62ec\u91cd\u590d\u6570\u636e\uff09<\/p>\n
    \u4f7f\u7528\u5f62\u5f0f\u5982\u4e0b\uff1a<\/p>\n
    SELECT \u5217\u540d\u79f0 FROM \u8868\u540d\u79f0 UNION SELECT \u5217\u540d\u79f0 FROM \u8868\u540d\u79f0 ORDER BY \u5217\u540d\u79f0;\nSELECT \u5217\u540d\u79f0 FROM \u8868\u540d\u79f0 UNION ALL SELECT \u5217\u540d\u79f0 FROM \u8868\u540d\u79f0 ORDER BY \u5217\u540d\u79f0;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
    MySQL UNION \u64cd\u4f5c\u7b26\u7528\u4e8e\u8fde\u63a5\u4e24\u4e2a\u4ee5\u4e0a\u7684 SELECT \u8bed\u53e5\u7684\u7ed3\u679c\u7ec4\u5408\u5230\u4e00\u4e2a\u7ed3\u679c\u96c6\u5408\u4e2d\u3002\u591a\u4e2a SELE […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[13],"tags":[],"class_list":["post-234","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts\/234","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/comments?post=234"}],"version-history":[{"count":0,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/posts\/234\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/media?parent=234"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/categories?post=234"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.appblog.cn\/index.php\/wp-json\/wp\/v2\/tags?post=234"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}