Oracle minus用法详解及应用实例

左手的ㄟ右手 2021-06-26 16:06 463阅读 0赞

## Oracle minus用法 ##

“minus”直接翻译为中文是“减”的意思，在Oracle中也是用来做减法操作的，只不过它不是传统意义上对数字的减法，而是对查询结果集的减法。`A minus B`就意味着将结果集A去除结果集B中所包含的所有记录后的结果，即在A中存在，而在B中不存在的记录。其算法跟Java中的`Collection`的`removeAll()`类似，即`A minus B`将只去除A跟B的交集部分，对于B中存在而A中不存在的记录不会做任何操作，也不会抛出异常。

Oracle的`minus`是按列进行比较的，**所以A能够minus B的前提条件是结果集A和结果集B需要有相同的列数**，且相同列索引的列具有相同的数据类型。此外，Oracle会对`minus`后的结果集进行去重，即如果A中原本多条相同的记录数在进行`A minus B`后将会只剩一条对应的记录，具体情况请看下面的示例。

下面我们来看一个`minus`实际应用的示例，假设我们有一张用户表`t_user`，其中有如下记录数据：

<table> 
 <thead> 
  <tr> 
   <th align="left">id</th> 
   <th align="left">no</th> 
   <th align="left">name</th> 
   <th align="left">age</th> 
   <th align="left">level_no</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">1</td> 
   <td align="left">1</td> 
   <td align="left">a</td> 
   <td align="left">25</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">2</td> 
   <td align="left">2</td> 
   <td align="left">b</td> 
   <td align="left">30</td> 
   <td align="left">2</td> 
  </tr> 
  <tr> 
   <td align="left">3</td> 
   <td align="left">3</td> 
   <td align="left">c</td> 
   <td align="left">35</td> 
   <td align="left">3</td> 
  </tr> 
  <tr> 
   <td align="left">4</td> 
   <td align="left">4</td> 
   <td align="left">d</td> 
   <td align="left">45</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">5</td> 
   <td align="left">5</td> 
   <td align="left">e</td> 
   <td align="left">30</td> 
   <td align="left">2</td> 
  </tr> 
  <tr> 
   <td align="left">6</td> 
   <td align="left">6</td> 
   <td align="left">f</td> 
   <td align="left">35</td> 
   <td align="left">3</td> 
  </tr> 
  <tr> 
   <td align="left">7</td> 
   <td align="left">7</td> 
   <td align="left">g</td> 
   <td align="left">25</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">8</td> 
   <td align="left">8</td> 
   <td align="left">h</td> 
   <td align="left">35</td> 
   <td align="left">2</td> 
  </tr> 
  <tr> 
   <td align="left">9</td> 
   <td align="left">9</td> 
   <td align="left">i</td> 
   <td align="left">20</td> 
   <td align="left">3</td> 
  </tr> 
  <tr> 
   <td align="left">10</td> 
   <td align="left">10</td> 
   <td align="left">j</td> 
   <td align="left">25</td> 
   <td align="left">1</td> 
  </tr> 
 </tbody> 
</table>

那么：

（1）

select id from t_user where id<6 minus select id from t_user where id between 3 and 7

的结果将为：

（2）

select age,level_no from t_user where id<8 minus select age,level_no from t_user where level=3

的结果为：

看到这样的结果，可能你会觉得有点奇怪，为何会是这样呢？我们来分析一下。首先，“`select age,level_no from t_user where id<8`”的结果将是这样的：

<table> 
 <thead> 
  <tr> 
   <th align="left">age</th> 
   <th align="left">level_no</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">25</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">30</td> 
   <td align="left">2</td> 
  </tr> 
  <tr> 
   <td align="left">35</td> 
   <td align="left">3</td> 
  </tr> 
  <tr> 
   <td align="left">45</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">30</td> 
   <td align="left">2</td> 
  </tr> 
  <tr> 
   <td align="left">35</td> 
   <td align="left">3</td> 
  </tr> 
  <tr> 
   <td align="left">25</td> 
   <td align="left">1</td> 
  </tr> 
 </tbody> 
</table>

然后，“`select age,level_no from t_user where level=3`”的结果将是这样的：

<table> 
 <thead> 
  <tr> 
   <th align="left">age</th> 
   <th align="left">level_no</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">35</td> 
   <td align="left">3</td> 
  </tr> 
  <tr> 
   <td align="left">35</td> 
   <td align="left">3</td> 
  </tr> 
  <tr> 
   <td align="left">20</td> 
   <td align="left">3</td> 
  </tr> 
 </tbody> 
</table>

然后，直接`A minus B`之后结果应当是：

<table> 
 <thead> 
  <tr> 
   <th align="left">age</th> 
   <th align="left">level_no</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="left">25</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">30</td> 
   <td align="left">2</td> 
  </tr> 
  <tr> 
   <td align="left">45</td> 
   <td align="left">1</td> 
  </tr> 
  <tr> 
   <td align="left">30</td> 
   <td align="left">2</td> 
  </tr> 
  <tr> 
   <td align="left">25</td> 
   <td align="left">1</td> 
  </tr> 
 </tbody> 
</table>

这个时候，我们可以看到结果集中存在重复的记录，进行去重后就得到了上述的实际结果。其实这也很好理解，因为`minus`的作用就是找出在A中存在，而在B中不存在的记录。

上述示例都是针对于单表的，很显然，使用`minus`进行单表操作是不具备优势的，其通常用于找出A表中的某些字段在B表中不存在对应记录的情况。比如我们拥有另外一个表`t_user2`，其拥有和`t_user`表一样的表结构，那么如下语句可以找出除id外，在`t_user`表中存在，而在`t_user2`表中不存在的记录。

select no,name,age,level_no from t_user minus select no,name,age,level_no from t_user2;