yeskery

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

Category：软件开发 Tag：JavaMariaDB 2017-12-21 Views：512

定义

  • 并集：给定两个集合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。
如果在上面的求交集中，使用 in 和 exists来查询来写的话：
使用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 in和in互为逻辑取反，not exists和exists互为逻辑取反。
如果在上面的求差集中，使用 not in 和 not 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));
  5. //求并集
  6. result.clear();
  7. result.addAll(setA);
  8. result.addAll(setB);
  9. result.stream().forEach(i -> System.out.print(i + " "));
  10. System.out.println();
  12. //求交集
  13. result.clear();
  14. result.addAll(setA);
  15. result.retainAll(setB);
  16. result.stream().forEach(i -> System.out.print(i + " "));
  17. System.out.println();
  19. //求差集
  20. result.clear();
  21. result.addAll(setA);
  22. result.removeAll(setB);
  23. result.stream().forEach(i -> System.out.print(i + " "));

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

评论

Post comment Click Refresh verification code

Tip

The feature is not yet available

Close