时间戳

首页 > 解决方案 > 如何使用 MySQL 8.0.17 版本合并两个不同表的所有行？

mysql - 如何使用 MySQL 8.0.17 版本合并两个不同表的所有行？

问题描述

这是我的 MySQL 数据库 8.0.17 的两个表

在这些表上，每个表的对应列是列tUnity

-- ----------------------------
-- Table structure for t_release
-- ----------------------------
DROP TABLE IF EXISTS `t_release`;
CREATE TABLE `t_release`  (
  `tID` int(11) NOT NULL AUTO_INCREMENT,
  `tUnity` varchar(255) DEFAULT NULL,
  `tMonthYear` varchar(255) DEFAULT NULL,
  `tHHrelease` datetime(0) DEFAULT NULL,
  `tHHapproved` datetime(0) DEFAULT NULL,
  PRIMARY KEY (`tID`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_release
-- ----------------------------
INSERT INTO `t_release` VALUES (1, 'D41', '1-2021', '2021-02-26 16:22:19', NULL);


-- ----------------------------
-- Table structure for t_unities
-- ----------------------------
DROP TABLE IF EXISTS `t_unities`;
CREATE TABLE `t_unities`  (
  `tID` int(11) NOT NULL AUTO_INCREMENT,
  `tUnity` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`tID`) USING BTREE
) ENGINE = InnoDB;

-- ----------------------------
-- Records of t_unities
-- ----------------------------
INSERT INTO `t_unities` VALUES (1, 'D40');
INSERT INTO `t_unities` VALUES (2, 'D41');
INSERT INTO `t_unities` VALUES (3, 'D42');
INSERT INTO `t_unities` VALUES (4, 'D43');
INSERT INTO `t_unities` VALUES (5, 'D44');
INSERT INTO `t_unities` VALUES (6, 'D45');
INSERT INTO `t_unities` VALUES (7, 'D46');
INSERT INTO `t_unities` VALUES (8, 'D47');
INSERT INTO `t_unities` VALUES (9, 'D48');
INSERT INTO `t_unities` VALUES (10, 'D49');

我需要合并这些表才能返回

+--------+------------+---------------------+-------------+--------+
| qUnity | tMonthYear | tHHrelease          | tHHapproved | tUnity |
+--------+------------+---------------------+-------------+--------+
| D40    |            |                     | NULL        | D40    |
| D41    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D42    |            |                     | NULL        | D42    |
| D43    |            |                     | NULL        | D43    |
| D44    |            |                     | NULL        | D43    |
| D45    |            |                     | NULL        | D45    |
| D46    |            |                     | NULL        | D46    |
| D47    |            |                     | NULL        | D47    |
| D48    |            |                     | NULL        | D48    |
| D49    |            |                     | NULL        | D49    |
+--------+------------+---------------------+-------------+--------+
10 rows in set (0.03 sec)

我已经尝试过这个 SQL 查询

SELECT DISTINCT
    q.tUnity AS qUnity,
    t.tMonthYear,
    t.tHHrelease,
    t.tHHapproved,
    t.tUnity AS tUnity
FROM
    t_release t
    LEFT JOIN t_unities q ON LEFT ( t.tUnity, 2 ) = LEFT ( q.tUnity, 2 ) 
WHERE
    LEFT ( t.tUnity, 2 ) = LEFT ( 'd400', 2 ) 
    AND tMonthYear = '1-2021';

但是这个 SQL 查询的返回是

+--------+------------+---------------------+-------------+--------+
| qUnity | tMonthYear | tHHrelease          | tHHapproved | tUnity |
+--------+------------+---------------------+-------------+--------+
| D40    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D41    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D42    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D43    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D44    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D45    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D46    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D47    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D48    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D49    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
+--------+------------+---------------------+-------------+--------+
10 rows in set (0.03 sec)

更新

    SELECT
        q.tUnity AS qUnity,
        t.tMonthYear,
        t.tHHrelease,
        t.tHHapproved,
        t.tUnity AS tUnity 
    FROM
        t_unities q
        LEFT OUTER JOIN t_release t ON t.tUnity=q.tUnity
    WHERE
        LEFT ( t.tUnity, 2 ) = LEFT ( 'd400', 2 ) 
        AND tMonthYear = '1-2021';
    
    +--------+------------+---------------------+-------------+--------+
    | qUnity | tMonthYear | tHHrelease          | tHHapproved | tUnity |
    +--------+------------+---------------------+-------------+--------+
    | D41    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
    +--------+------------+---------------------+-------------+--------+
    1 row in set (0.03 sec)

解决了

SELECT
    q.tUnity AS qUnity,
    t.tMonthYear,
    t.tHHrelease,
    t.tHHapproved,
    t.tUnity AS tUnity 
FROM
    t_unities q
    LEFT OUTER JOIN t_release t ON t.tUnity = q.tUnity 
    AND LEFT ( t.tUnity, 2 ) = LEFT ( 'd400', 2 ) 
    AND tMonthYear = '1-2021';

+--------+------------+---------------------+-------------+--------+
| qUnity | tMonthYear | tHHrelease          | tHHapproved | tUnity |
+--------+------------+---------------------+-------------+--------+
| D41    | 1-2021     | 2021-02-26 16:22:19 | NULL        | D41    |
| D40    | NULL       | NULL                | NULL        | NULL   |
| D42    | NULL       | NULL                | NULL        | NULL   |
| D43    | NULL       | NULL                | NULL        | NULL   |
| D44    | NULL       | NULL                | NULL        | NULL   |
| D45    | NULL       | NULL                | NULL        | NULL   |
| D46    | NULL       | NULL                | NULL        | NULL   |
| D47    | NULL       | NULL                | NULL        | NULL   |
| D48    | NULL       | NULL                | NULL        | NULL   |
| D49    | NULL       | NULL                | NULL        | NULL   |
+--------+------------+---------------------+-------------+--------+
10 rows in set (0.03 sec)

标签： mysql

解决方案

您应该LEFT加入t_unitiestot_release并设置ON子句中的所有条件：

SELECT 
    q.tUnity AS qUnity,
    t.tMonthYear,
    t.tHHrelease,
    t.tHHapproved,
    -- q.tUnity AS tUnity
FROM t_unities q LEFT JOIN t_release t
ON t.tUnity =  q.tUnity
AND LEFT(t.tUnity, 2) = LEFT('D400', 2) 
AND t.tMonthYear = '1-2021';

或者：

SELECT 
    q.tUnity AS qUnity,
    t.tMonthYear,
    t.tHHrelease,
    t.tHHapproved,
    -- q.tUnity AS tUnity
FROM t_unities q LEFT JOIN t_release t
ON t.tUnity =  q.tUnity
AND t.tMonthYear = '1-2021'
WHERE LEFT(q.tUnity, 2) = LEFT('D400', 2)

请参阅演示。
结果：

qUnity t月年 tHH释放 已批准
D40 空值 空值 空值
D41 1-2021 2021-02-26 16:22:19 空值
D42 空值 空值 空值
D43 空值 空值 空值
D44 空值 空值 空值
D45 空值 空值 空值
D46 空值 空值 空值
D47 空值 空值 空值
D48 空值 空值 空值
D49 空值 空值 空值

推荐阅读