|
今天工作中遇到的需求，要查出几个表中的数据，但是本着方便客户的思想，想将这几条查询语句变成一条，只需要一次就可以查询到所有的结果，妹滴狠。
1. 使用子查询以及union all，拼接查询结果
1.1 union all 解析
union all，用于合并两个或多个select语句的结果集。需注意，列的数量必须相同，列名不必相同，列的类型也不必相同，如下：
--建表
Create table Table1
(
id int not null,
name varchar(20) not null
)
Create table Table2
(
id int not null,
name varchar(20) not null
)
--插入数据
Insert into Table1 values (1,'姚羽')
Insert into Table1 values (2,'边兵兵')
Insert into Table1 values (3,'袁磊')
Insert into Table2 values (1,'姚羽')
Insert into Table2 values (2,'柳春平')
Insert into Table2 values (3,'张永超')
Insert into Table2 values (4,'刘华健')
--执行sql
--select1
select id,name from Table1
union all select id,name from Table2;
--select 2
select id id1,name from Table1
union all select id,name from Table2;
--select3
select id,name from Table1
union all select name,id from Table2;
以下分别为执行结果： select1: select2: select3:
补充：形象的展示以下union 和 union all 的区别吧：
--select4
select id,name from Table1
union select id,name from Table2;
运行结果如下：  大概形象看看出来，union会去除重复行。
2.分次查询
--要将没有关联的表的结果集合在一起 就可以执行以下代码
--表名以及字段名保密
--select1
select count(distinct column1) AS total
from table1 t join table2 f
on t.ZJLX = f.GLOBAL_TYPE and t.ZJHM=f.GLOBAL_ID
WHERE f.column2='000' and f.column3='B0000' and t.column4='000';
--select2
SELECT COUNT(distinct column1) AS total1
FROM table2
where column2='TAX' and column2='01';
--select3
SELECT sum(distinct column1) AS total2
FROM table2
where column2='TAX' and column2='01';
--直接union all 需要将他们的字段区分开来
--select4
select count(distinct column1) AS total ,0 AS total1, 0 AS total2
from table1 t join table2 f
on t.ZJLX = f.GLOBAL_TYPE and t.ZJHM=f.GLOBAL_ID
WHERE f.column2='000' and f.column3='B0000' and t.column4='000';
--select5
SELECT 0 AS total,COUNT(distinct column1) AS total1 ,0 AS totak2
FROM table2
where column2='TAX' and column2='01';
--select6
SELECT 0 AS total,0 AS total1,sum(distinct column1) AS total2
FROM table2
where column2='TAX' and column2='01';
--这样保证每个想查的结果都可以显示出来，并且列数量相同，可以拼接
--select7
select total,total1,total2
from (select count(distinct column1) AS total ,0 AS total1, 0 AS total2
from table1 t join table2 f
on t.ZJLX = f.GLOBAL_TYPE and t.ZJHM=f.GLOBAL_ID
WHERE f.column2='000' and f.column3='B0000' and t.column4='000'
union all SELECT 0 AS total,COUNT(distinct column1) AS total1 ,0 AS totak2
FROM table2
where column2='TAX' and column2='01'
union all SELECT 0 AS total,0 AS total1,sum(distinct column1) AS total2
FROM table2
where column2='TAX' and column2='01')
--但是这样下来发现三行三列，不美观，这时候将其余两行赋值为0的作用就显现出来了
--select8
select sum(total),sum(total1),sum(total2)
from (select count(distinct column1) AS total ,0 AS total1, 0 AS total2
from table1 t join table2 f
on t.ZJLX = f.GLOBAL_TYPE and t.ZJHM=f.GLOBAL_ID
WHERE f.column2='000' and f.column3='B0000' and t.column4='000'
union all SELECT 0 AS total,COUNT(distinct column1) AS total1 ,0 AS totak2
FROM table2
where column2='TAX' and column2='01'
union all SELECT 0 AS total,0 AS total1,sum(distinct column1) AS total2
FROM table2
where column2='TAX' and column2='01')
运行结果如下： select1: select2:  select3: select7: select8:
如上即将两个没有关联的表的查询出的结果集合并为一个结果集。
|