04 SELECT 子查询/all_any关键字/多列子查询 = | in | and | union (all)

1. all和any的使用

1.1 all(满足条件的所有)/可以换成max

1.2 any((满足条件的任何一个就可以))/可以换成min

2.多列子查询 = | in | and | union (all)

2.1 多条件匹配,子查询条件返回1行,可使用=号

SELECT a.B01,a.B02,a.B109,b.NSRZT
from `工商库` a
where (B01, B02,b109)=(
	# 子查询条件返回1行,可使用=号,
		select b01,b02,b109
			from sheet2 b
			where b01 = **) 

2.2 多条件匹配,子查询条件返回多行,不能使用=号,需使用in

• 三者条件需同时满足 in

SELECT a.B01,a.B02,a.B109,b.NSRZT
from `工商库` a
where (B01, B02,b109) in (
		# 子查询条件返回多行,需使用in, 此时相当于多列and查询结果
		select b01,b02,b109
			from sheet2 b
			where b01 = **) 

• 三者条件需同时满足 and

with t as(select b01,b02,b109
			from sheet2 b
			where b01 = **) 

SELECT a.B01,a.B02,a.B109,b.NSRZT
from `工商库` a
where B01 = t.b01 and B02 = t.b02 and B109 = t.b109

• 三者条件满足任何一个or (不推荐特慢)

with t as(select b01,b02,b109
			from sheet2 b
			where b01 = **) 

SELECT a.B01,a.B02,a.B109,b.NSRZT
from `工商库` a
where B01 = t.b01 and B02 = t.b02 and B109 = t.b109

• 三者条件满足任何一个union all (推荐)

SELECT a.B01,a.B02,a.B109,b.NSRZT
from `工商库` a,sheet2 b
where a.B01=B.B01

UNION all 
# Union 包含去重的效果,  Union All 显示联合所有的结果
SELECT a.B01,a.B02,a.B109,b.NSRZT
from `工商库` a,sheet2 b
where a.B02=B.B02

UNION ALL

SELECT a.B01,a.B02,a.B109,b.NSRZT
from `工商库` a,sheet2 b
where a.B109=B.B109