mysql - 对两个表进行分组，然后附加结果并删除重复项

Question

我有 2 个表，每个表都有多个列。如下所示，列标题的名称不同。我想对每个表的第一列和第二列进行 GROUP BY，然后在输出中，结果应该被附加，并且没有任何重复项。而且结果只需要前两列作为输出。

表名=STAT

Region   Brand  M08 
-----------------
P1       A      xx     
P1       A      yy    
P2       B      zz     
P2       C      yy      
P3       A      uu     
P3       B      ii    
P3       C      oo   

表名称 = MI

RegDes   P4Brand  M08 
-----------------   
P2       B        er    
P2       C        tr     
P3       A        ty     
P3       B        ew    
P4       C        yu  
P4       A        tr     
P4       B        ty     
P5       C        yu 

表名称 = EXPECTED_OUTPUT

Region   Brand   
-----------------  
P1       A              
P2       B          
P2       C         
P3       A          
P3       B         
P4       C     
P4       A         
P4       B        
P5       C    

我需要一些帮助来为此编写 SQL 脚本。我已经为 3 个表创建了架构。最终输出将插入表中:E​​XPECTED_OUTPUT

Answer 1

我会使用UNION ALL 将两个表合并为一个唯一的表。然后，将 SELECT * 包含在该查询周围并应用 GROUP BY

或者，我不会使用 GROUP BY，而是使用 SELECT DISTINCT *

或者，正如 @Usagi Miyamoto 在评论中所述，使用简单的 UNION (不带 ALL)

架构 (MySQL v5.7)

CREATE TABLE stat (
  `Region` VARCHAR(2),
  `Brand` VARCHAR(1),
  `M08` VARCHAR(2)
);

INSERT INTO stat
  (`Region`, `Brand`, `M08`)
VALUES
  ('P1', 'A', 'xx'),
  ('P1', 'A', 'yy'),
  ('P2', 'B', 'zz'),
  ('P2', 'C', 'yy'),
  ('P3', 'A', 'uu'),
  ('P3', 'B', 'ii'),
  ('P3', 'C', 'oo');



CREATE TABLE mi (
  `RegDes` VARCHAR(2),
  `P4Brand` VARCHAR(1),
  `M08` VARCHAR(2)
);

INSERT INTO mi
  (`RegDes`, `P4Brand`, `M08`)
VALUES
  ('P2', 'B', 'er'),
  ('P2', 'C', 'tr'),
  ('P3', 'A', 'ty'),
  ('P3', 'B', 'ew'),
  ('P4', 'C', 'yu'),
  ('P4', 'A', 'tr'),
  ('P4', 'B', 'ty'),
  ('P5', 'C', 'yu');

<小时/>

查询 #1 GROUP BY

SELECT * FROM 
(
    SELECT s.Region AS Region, s.Brand AS Brand
    FROM stat s
    UNION ALL
    SELECT m.RegDes AS Region, m.P4Brand AS Brand
    FROM mi m
) AS EXPECTED_OUTPUT
GROUP BY Region, Brand;

输出

| Region | Brand |
| ------ | ----- |
| P1     | A     |
| P2     | B     |
| P2     | C     |
| P3     | A     |
| P3     | B     |
| P3     | C     |
| P4     | A     |
| P4     | B     |
| P4     | C     |
| P5     | C     |

<小时/>

查询 #2 DISTINCT

SELECT DISTINCT * FROM 
(
    SELECT s.Region AS Region, s.Brand AS Brand
    FROM stat s
    UNION ALL
    SELECT m.RegDes AS Region, m.P4Brand AS Brand
    FROM mi m
) AS EXPECTED_OUTPUT;

输出

| Region | Brand |
| ------ | ----- |
| P1     | A     |
| P2     | B     |
| P2     | C     |
| P3     | A     |
| P3     | B     |
| P3     | C     |
| P4     | C     |
| P4     | A     |
| P4     | B     |
| P5     | C     |

<小时/>

查询#3 UNION

SELECT s.Region AS Region, s.Brand AS Brand
FROM stat s
UNION
SELECT m.RegDes AS Region, m.P4Brand AS Brand
FROM mi m;

输出

| Region | Brand |
| ------ | ----- |
| P1     | A     |
| P2     | B     |
| P2     | C     |
| P3     | A     |
| P3     | B     |
| P3     | C     |
| P4     | C     |
| P4     | A     |
| P4     | B     |
| P5     | C     |

<小时/>

View on DB Fiddle

<小时/>

要将结果插入表中，您可以使用 INSERT INTO SELECT 语句:

按查询分组

INSERT INTO EXPECTED_OUTPUT
SELECT * FROM 
(
    SELECT s.Region AS Region, s.Brand AS Brand
    FROM stat s
    UNION ALL
    SELECT m.RegDes AS Region, m.P4Brand AS Brand
    FROM mi m
) AS EXPECTED_OUTPUTDATAS
GROUP BY Region, Brand;

DISTINCT 查询

INSERT INTO EXPECTED_OUTPUT
SELECT DISTINCT * FROM 
(
    SELECT s.Region AS Region, s.Brand AS Brand
    FROM stat s
    UNION ALL
    SELECT m.RegDes AS Region, m.P4Brand AS Brand
    FROM mi m
) AS EXPECTED_OUTPUTDATAS;

UNION 查询

INSERT INTO EXPECTED_OUTPUT
SELECT s.Region AS Region, s.Brand AS Brand
FROM stat s
UNION
SELECT m.RegDes AS Region, m.P4Brand AS Brand
FROM mi m;