不使用集合运算符(UNION)组合选择查询的结果 [英] Combining results of select queries without using set operators(UNION)

问题描述

是否可以不使用联合运算符组合 sql 中的查询结果(与 union/union all 的工作方式相同)

解决方案

假设您有两个表要UNION

CREATE TABLE Table1(`id` int, `name` varchar(32));创建表 Table2(`id` int, `name` varchar(32));

和样本数据

表 1:

<前>|身份证 |姓名 ||----|-------||1 |姓名1 ||2 |姓名2 ||3 |姓名3 |

表 2:

<前>|身份证 |姓名 ||----|--------||11 |姓名11 ||22 |姓名22 ||33 |名称33 ||1 |姓名1 |

模拟UNION ALL

SELECT COALESCE(t1.id, t2.id) id,COALESCE(t1.name, t2.name) 名称从(SELECT TABLE_NAME <>'table1' n来自 INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = SCHEMA()AND TABLE_NAME IN('table1', 'table2')) t 左连接 table1 t1ON t.n = 0 LEFT JOIN table2 t2ON t.n = 1

输出:

<前>|身份证 |姓名 ||----|--------||1 |姓名1 ||2 |姓名2 ||3 |姓名3 ||11 |姓名11 ||22 |姓名22 ||33 |名称33 ||1 |姓名1 |

要模拟 UNION，您只需添加 DISTINCT

SELECT DISTINCT COALESCE(t1.id, t2.id) id,COALESCE(t1.name, t2.name) 名称从(SELECT TABLE_NAME <>'table1' n来自 INFORMATION_SCHEMA.TABLESWHERE TABLE_SCHEMA = SCHEMA()AND TABLE_NAME IN('table1', 'table2')) t 左连接 table1 t1ON t.n = 0 LEFT JOIN table2 t2ON t.n = 1

输出:

<前>|身份证 |姓名 ||----|--------||1 |姓名1 ||2 |姓名2 ||3 |姓名3 ||11 |姓名11 ||22 |姓名22 ||33 |名称33 |

这是SQLFiddle 演示

Is it possible without using union operator combining results of queries in sql (which works same as union/union all)

解决方案

Let's suppose you have two tables you want to UNION

CREATE TABLE Table1
(`id` int, `name` varchar(32));
CREATE TABLE Table2
(`id` int, `name` varchar(32));

And sample data

table1:

| ID |  NAME |
|----|-------|
|  1 | name1 |
|  2 | name2 |
|  3 | name3 |

table2:

| ID |  NAME  |
|----|--------|
| 11 | name11 |
| 22 | name22 |
| 33 | name33 |
|  1 |  name1 |

To emulate UNION ALL

SELECT COALESCE(t1.id, t2.id) id,
       COALESCE(t1.name, t2.name) name
  FROM 
(
  SELECT TABLE_NAME <> 'table1' n
    FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = SCHEMA()
     AND TABLE_NAME IN('table1', 'table2')
) t LEFT JOIN table1 t1 
    ON t.n = 0 LEFT JOIN table2 t2
    ON t.n = 1 

Output:

| ID |   NAME |
|----|--------|
|  1 |  name1 |
|  2 |  name2 |
|  3 |  name3 |
| 11 | name11 |
| 22 | name22 |
| 33 | name33 |
|  1 |  name1 |

To emulate UNION you need just to add DISTINCT

SELECT DISTINCT COALESCE(t1.id, t2.id) id,
       COALESCE(t1.name, t2.name) name
  FROM 
(
  SELECT TABLE_NAME <> 'table1' n
    FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_SCHEMA = SCHEMA()
     AND TABLE_NAME IN('table1', 'table2')
) t LEFT JOIN table1 t1 
    ON t.n = 0 LEFT JOIN table2 t2
    ON t.n = 1 

Output:

| ID |   NAME |
|----|--------|
|  1 |  name1 |
|  2 |  name2 |
|  3 |  name3 |
| 11 | name11 |
| 22 | name22 |
| 33 | name33 |

Here is SQLFiddle demo

这篇关于不使用集合运算符(UNION)组合选择查询的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！