MySQL UNION 查询

2019/05/14 00:45
阅读数 11

<table class="d-block"> <tbody class="d-block"> <tr class="d-block"> <td class="d-block comment-body markdown-body js-comment-body">

<p><code>UNION</code> 用来合并多个 SELECT 结果。</p> <p>考察如下两个表:</p> <div class="highlight highlight-source-shell"><pre><span class="pl-c"><span class="pl-c">#</span> t1</span> +----+---------+ <span class="pl-k">|</span> id <span class="pl-k">|</span> pattern <span class="pl-k">|</span> +----+---------+ <span class="pl-k">|</span> 1 <span class="pl-k">|</span> Divot <span class="pl-k">|</span> <span class="pl-k">|</span> 2 <span class="pl-k">|</span> Brick <span class="pl-k">|</span> <span class="pl-k">|</span> 3 <span class="pl-k">|</span> Grid <span class="pl-k">|</span> +----+---------+

<span class="pl-c"><span class="pl-c">#</span> t2</span> +----+---------+ <span class="pl-k">|</span> id <span class="pl-k">|</span> pattern <span class="pl-k">|</span> +----+---------+ <span class="pl-k">|</span> 1 <span class="pl-k">|</span> Divot <span class="pl-k">|</span> <span class="pl-k">|</span> A <span class="pl-k">|</span> Brick <span class="pl-k">|</span> <span class="pl-k">|</span> B <span class="pl-k">|</span> Grid <span class="pl-k">|</span> <span class="pl-k">|</span> C <span class="pl-k">|</span> Diamond <span class="pl-k">|</span> +----+---------+</pre></div>

<p>一个 union 示例:</p> <div class="highlight highlight-source-sql"><pre>mysql<span class="pl-k">&gt;</span> <span class="pl-k">select</span> <span class="pl-k">*</span> <span class="pl-k">from</span> t1 <span class="pl-k">union</span> <span class="pl-k">select</span> <span class="pl-k">*</span> <span class="pl-k">from</span> t2; <span class="pl-k">+</span><span class="pl-c"><span class="pl-c">--</span>--+---------+</span> | id | pattern | <span class="pl-k">+</span><span class="pl-c"><span class="pl-c">--</span>--+---------+</span> | <span class="pl-c1">1</span> | Divot | | <span class="pl-c1">2</span> | Brick | | <span class="pl-c1">3</span> | Grid | | A | Brick | | B | Grid | | C | Diamond | <span class="pl-k">+</span><span class="pl-c"><span class="pl-c">--</span>--+---------+</span> <span class="pl-c1">6</span> rows <span class="pl-k">in</span> <span class="pl-k">set</span> (<span class="pl-c1">0</span>.<span class="pl-c1">00</span> sec)</pre></div> <p>默认情况下 UNION 结果中已经去重,所以无须指定 DISTINCT。如果想保留所有结果可指定 <code>ALL</code>。</p> <div class="highlight highlight-source-shell"><pre>mysql<span class="pl-k">&gt;</span> SELECT <span class="pl-k">*</span> FROM t1 UNION ALL SELECT <span class="pl-k">*</span> FROM t2<span class="pl-k">;</span> +----+---------+ <span class="pl-k">|</span> id <span class="pl-k">|</span> pattern <span class="pl-k">|</span> +----+---------+ <span class="pl-k">|</span> 1 <span class="pl-k">|</span> Divot <span class="pl-k">|</span> <span class="pl-k">|</span> 2 <span class="pl-k">|</span> Brick <span class="pl-k">|</span> <span class="pl-k">|</span> 3 <span class="pl-k">|</span> Grid <span class="pl-k">|</span> <span class="pl-k">|</span> 1 <span class="pl-k">|</span> Divot <span class="pl-k">|</span> <span class="pl-k">|</span> A <span class="pl-k">|</span> Brick <span class="pl-k">|</span> <span class="pl-k">|</span> B <span class="pl-k">|</span> Grid <span class="pl-k">|</span> <span class="pl-k">|</span> C <span class="pl-k">|</span> Diamond <span class="pl-k">|</span> +----+---------+ 7 rows <span class="pl-k">in</span> <span class="pl-c1">set</span> (0.00 sec)</pre></div> <p>查询语句中可混合使用 <a href="https://dev.mysql.com/doc/refman/8.0/en/union.html" rel="nofollow"><code>UNION ALL</code></a> 和 <a href="https://dev.mysql.com/doc/refman/8.0/en/union.html" rel="nofollow"><code>UNION DISTINCT</code></a>,右边的 <code>UNION DISTINCT</code> 替覆盖掉左边 <code>UNION ALL</code>。</p> <p>结果中的列名将使用第一个 SELECT 语句中定义的列名。各 SELECT 结果中对应位置的列其数据类型应该保持一致。如果不一致,MySQL 会根据结果中的数据类型及长度进行兼容的转换。</p> <p>UNION 语句中只最后一个 SELECT 可指定 INTO OUTFILE。但其实整个 UNION 查询的结果都是存入这个文件的。<br> UNION 中不能搭配使用 HIGH_PRIORITY。如果该关键词指定在第一个 SELECT 身上,不会生效,指定在其他 SELECT 上会报语法错误。</p> <p>结合使用 <code>ORDER BY</code> 或 <code>LIMIT</code> 时,应使用括号将 SELECT 语句包裹。</p> <div class="highlight highlight-source-sql"><pre>(<span class="pl-k">SELECT</span> a <span class="pl-k">FROM</span> t1 <span class="pl-k">WHERE</span> a<span class="pl-k">=</span><span class="pl-c1">10</span> <span class="pl-k">AND</span> B<span class="pl-k">=</span><span class="pl-c1">1</span> <span class="pl-k">ORDER BY</span> a <span class="pl-k">LIMIT</span> <span class="pl-c1">10</span>) <span class="pl-k">UNION</span> (<span class="pl-k">SELECT</span> a <span class="pl-k">FROM</span> t2 <span class="pl-k">WHERE</span> a<span class="pl-k">=</span><span class="pl-c1">11</span> <span class="pl-k">AND</span> B<span class="pl-k">=</span><span class="pl-c1">2</span> <span class="pl-k">ORDER BY</span> a <span class="pl-k">LIMIT</span> <span class="pl-c1">10</span>);</pre></div> <p>对 UNION 结果进行整体排序和数量限制:</p> <div class="highlight highlight-source-sql"><pre>(<span class="pl-k">SELECT</span> a <span class="pl-k">FROM</span> t1 <span class="pl-k">WHERE</span> a<span class="pl-k">=</span><span class="pl-c1">10</span> <span class="pl-k">AND</span> B<span class="pl-k">=</span><span class="pl-c1">1</span>) <span class="pl-k">UNION</span> (<span class="pl-k">SELECT</span> a <span class="pl-k">FROM</span> t2 <span class="pl-k">WHERE</span> a<span class="pl-k">=</span><span class="pl-c1">11</span> <span class="pl-k">AND</span> B<span class="pl-k">=</span><span class="pl-c1">2</span>) <span class="pl-k">ORDER BY</span> a <span class="pl-k">LIMIT</span> <span class="pl-c1">10</span>;</pre></div> <p>SELECT 中指定了别名时,ORDER BY 应该使用该别名,而不是真实的列名。</p> <div class="highlight highlight-source-sql"><pre><span class="pl-c"><span class="pl-c">--</span> ✅</span> (<span class="pl-k">SELECT</span> a <span class="pl-k">AS</span> b <span class="pl-k">FROM</span> t) <span class="pl-k">UNION</span> (<span class="pl-k">SELECT</span> ...) <span class="pl-k">ORDER BY</span> b; <span class="pl-c"><span class="pl-c">--</span> 🚨 Unknown column 'a' in 'order clause'</span> (<span class="pl-k">SELECT</span> a <span class="pl-k">AS</span> b <span class="pl-k">FROM</span> t) <span class="pl-k">UNION</span> (<span class="pl-k">SELECT</span> ...) <span class="pl-k">ORDER BY</span> a;</pre></div> <h2>相关资源</h2> <ul> <li><a href="https://dev.mysql.com/doc/refman/8.0/en/union.html" rel="nofollow">MySQL Manual - 13.2.10.3 UNION Syntax </a></li> </ul> </td> </tr> </tbody> </table>

展开阅读全文
打赏
0
0 收藏
分享
加载中
更多评论
打赏
0 评论
0 收藏
0
分享
返回顶部
顶部