yeskery

并集、交集、差集在 SQL 和 Java 中的实现

定义

  • 并集:给定两个集合A,B,把他们所有的元素合并在一起组成的集合,叫做集合A与集合B的并集,记作A∪B,读作A并B。
  • 交集:集合论中,设A,B是两个集合,由所有属于集合A且属于集合B的元素所组成的集合,叫做集合A与集合B的交集(intersection),记作A∩B。
  • 差集:一般地,记A,B是两个集合,则所有属于A且不属于B的元素构成的集合,叫做集合A减集合B(或集合A与集合B之差),类似地,对于集合A、B,我们把集合{x∣x∈A,且x∉B}叫做A与B的差集。

在 SQL 中的实现

数据表的准备

  1. /* 门店A销售记录表 */
  2. CREATE TABLE `sale_store_a`(
  3. `sale_date` DATE,
  4. `sale_money` FLOAT
  5. );
  6. INSERT INTO `sale_store_a` (`sale_date`, `sale_money`) VALUES ('2017-12-01', '60000');
  7. INSERT INTO `sale_store_a` (`sale_date`, `sale_money`) VALUES ('2017-12-02', '50000');
  8. INSERT INTO `sale_store_a` (`sale_date`, `sale_money`) VALUES ('2017-12-03', '65000');
  9. /* 门店B销售记录表 */
  10. CREATE TABLE `sale_store_b`(
  11. `sale_date` DATE,
  12. `sale_money` FLOAT
  13. );
  14. INSERT INTO `sale_store_b` (`sale_date`, `sale_money`) VALUES ('2017-12-02', '40000');
  15. INSERT INTO `sale_store_b` (`sale_date`, `sale_money`) VALUES ('2017-12-03', '50000');
  16. INSERT INTO `sale_store_b` (`sale_date`, `sale_money`) VALUES ('2017-12-04', '45000');

并集

两个店铺每天销售额汇总到一个表格

  1. SELECT `a`.`sale_date`,`a`.`sale_money` FROM `sale_store_a` AS `a`
  2. UNION ALL
  3. SELECT `b`.`sale_date`,`b`.`sale_money` FROM `sale_store_b` AS `b`;
  1. +------------+------------+
  2. | sale_date | sale_money |
  3. +------------+------------+
  4. | 2017-12-01 | 60000 |
  5. | 2017-12-02 | 50000 |
  6. | 2017-12-03 | 65000 |
  7. | 2017-12-02 | 40000 |
  8. | 2017-12-03 | 50000 |
  9. | 2017-12-04 | 45000 |
  10. +------------+------------+
  11. 6 rows in set (0.01 sec)

交集

只显示两个店铺都有销售额的数据汇总

  • 内连接实现
    1. SELECT `a`.*,`b`.* FROM `sale_store_a` AS `a` INNER JOIN `sale_store_b` AS `b` ON `a`.`sale_date` = `b`.`sale_date`;
  • 左外连接实现
    1. SELECT `a`.*,`b`.* FROM `sale_store_a` AS `a` LEFT OUTER JOIN `sale_store_b` AS `b` ON `a`.`sale_date` = `b`.`sale_date` WHERE `b`.`sale_date` IS NOT NULL;
  • 右外连接实现
    1. SELECT `a`.*,`b`.* FROM `sale_store_a` AS `a` RIGHT OUTER JOIN `sale_store_b` AS `b` ON `a`.`sale_date` = `b`.`sale_date` WHERE `a`.`sale_date` IS NOT NULL;
    以上三句查询结果均如下:
    1. +------------+------------+------------+------------+
    2. | sale_date | sale_money | sale_date | sale_money |
    3. +------------+------------+------------+------------+
    4. | 2017-12-02 | 50000 | 2017-12-02 | 40000 |
    5. | 2017-12-03 | 65000 | 2017-12-03 | 50000 |
    6. +------------+------------+------------+------------+
    7. 2 rows in set (0.00 sec)

    差集

    查看A店铺有销售额但B店铺没有销售额的汇总
    1. SELECT `a`.*,`b`.* FROM `sale_store_a` AS `a` LEFT OUTER JOIN `sale_store_b` AS `b` ON `a`.`sale_date` = `b`.`sale_date` WHERE `b`.`sale_date` IS NULL;
    1. +------------+------------+-----------+------------+
    2. | sale_date | sale_money | sale_date | sale_money |
    3. +------------+------------+-----------+------------+
    4. | 2017-12-01 | 60000 | NULL | NULL |
    5. +------------+------------+-----------+------------+
    6. 1 row in set (0.00 sec)
    查看B店铺有销售额但A店铺没有销售额的汇总
    1. SELECT `a`.*,`b`.* FROM `sale_store_a` AS `a` RIGHT OUTER JOIN `sale_store_b` AS `b` ON `a`.`sale_date` = `b`.`sale_date` WHERE `a`.`sale_date` IS NULL;
    1. +-----------+------------+------------+------------+
    2. | sale_date | sale_money | sale_date | sale_money |
    3. +-----------+------------+------------+------------+
    4. | NULL | NULL | 2017-12-04 | 45000 |
    5. +-----------+------------+------------+------------+
    6. 1 row in set (0.00 sec)
    查看当天只有一个店铺有销售额的汇总
    1. SELECT `a`.*,`b`.* FROM `sale_store_a` AS `a` LEFT OUTER JOIN `sale_store_b` AS `b` ON `a`.`sale_date` = `b`.`sale_date` WHERE `b`.`sale_date` IS NULL
    2. UNION
    3. SELECT `a`.*,`b`.* FROM `sale_store_a` AS `a` RIGHT OUTER JOIN `sale_store_b` AS `b` ON `a`.`sale_date` = `b`.`sale_date` WHERE `a`.`sale_date` IS NULL;
    1. +------------+------------+------------+------------+
    2. | sale_date | sale_money | sale_date | sale_money |
    3. +------------+------------+------------+------------+
    4. | 2017-12-01 | 60000 | NULL | NULL |
    5. | NULL | NULL | 2017-12-04 | 45000 |
    6. +------------+------------+------------+------------+
    7. 2 rows in set (0.01 sec)

in 和 exists

in是把外表和内表作hash连接,而exists是对外表作loop循环,每次loop循环再对内表进行查询,一直以来认为exists比in效率高的说法是不准确的。
如果查询的两个表大小相当,那么用in和exists差别不大
如果两个表中一个较小一个较大,则子查询表大的用exists,子查询表小的用in
如果在上面的求交集中,使用 inexists来查询来写的话:
使用in来求交集

  1. SELECT `a`.* FROM `sale_store_a` AS `a` WHERE `a`.`sale_date` IN (SELECT `sale_date` FROM `sale_store_b`);

使用exists来求交集

  1. SELECT `a`.* FROM `sale_store_a` AS `a` WHERE EXISTS (SELECT `sale_date` FROM `sale_store_b` WHERE `sale_date` = `a`.`sale_date`);

上面两个查询语句的结果如下:

  1. +------------+------------+
  2. | sale_date | sale_money |
  3. +------------+------------+
  4. | 2017-12-02 | 50000 |
  5. | 2017-12-03 | 65000 |
  6. +------------+------------+
  7. 2 rows in set (0.00 sec)

说明

  1. 如果表 sale_store_a 的数据量很小,而 sale_store_b 的数据量很大,则上面的语句中使用了exists的语句查询效率高,因为是使用了数据量大的 sale_date 字段来循环比较了数据量较小的sale_date 字段。

  2. 如果表 sale_store_a 的数据量很大,而 sale_store_b 的数据量很小,则上面的语句中使用了in的语句查询效率高,因为是使用了数据量大的 sale_date 字段来循环比较了数据量较小的sale_date 字段。

not in 和 not exists

not inin互为逻辑取反,not existsexists互为逻辑取反。
如果在上面的求差集中,使用 not innot exists来查询来写的话:
使用not in来求差集

  1. SELECT `a`.* FROM `sale_store_a` AS `a` WHERE `a`.`sale_date` NOT IN (SELECT `sale_date` FROM `sale_store_b`);

使用not exists来求差集

  1. SELECT `a`.* FROM `sale_store_a` AS `a` WHERE NOT EXISTS (SELECT `sale_date` FROM `sale_store_b` WHERE `sale_date` = `a`.`sale_date`);

上面两个查询语句的结果如下:

  1. +------------+------------+
  2. | sale_date | sale_money |
  3. +------------+------------+
  4. | 2017-12-01 | 60000 |
  5. +------------+------------+
  6. 1 row in set (0.00 sec)

在性能方面参考上面的说明。

in 与 = 的关系

in 实际上就是多个 = 的或运算

  1. SELECT `a`.* FROM `sale_store_a` AS `a` WHERE `a`.`sale_date` IN ('2017-12-01','2017-12-02');
  1. SELECT `a`.* FROM `sale_store_a` AS `a` WHERE `a`.`sale_date` = '2017-12-01' OR `a`.`sale_date` = '2017-12-02';

上面两个查询的结果的都如下:

  1. +------------+------------+
  2. | sale_date | sale_money |
  3. +------------+------------+
  4. | 2017-12-01 | 60000 |
  5. | 2017-12-02 | 50000 |
  6. +------------+------------+
  7. 2 rows in set (0.00 sec)

在 Java 中的实现

在Java中,集合类Set可以达到去重复的效果,所以在求并集、交集、差集只需要使用Set类即可:

  1. Set<Integer> result = new HashSet<>();
  2. Set<Integer> setA = new HashSet<>(Arrays.asList(1, 2, 3));
  3. Set<Integer> setB = new HashSet<>(Arrays.asList(2, 3, 4));
  4. //求并集
  5. result.clear();
  6. result.addAll(setA);
  7. result.addAll(setB);
  8. result.stream().forEach(i -> System.out.print(i + " "));
  9. System.out.println();
  10. //求交集
  11. result.clear();
  12. result.addAll(setA);
  13. result.retainAll(setB);
  14. result.stream().forEach(i -> System.out.print(i + " "));
  15. System.out.println();
  16. //求差集
  17. result.clear();
  18. result.addAll(setA);
  19. result.removeAll(setB);
  20. result.stream().forEach(i -> System.out.print(i + " "));

本文部分内容内容来自:https://www.cnblogs.com/seasons1987/archive/2013/07/03/3169356.html

评论

发表评论 点击刷新验证码

提示

该功能暂未开放