select * from (
with
t_tree as
(
select CountryRegionCode from person.CountryRegion
)
select * from person.StateProvince where CountryRegionCode in (select * from t_tree)
) as chaxuuu
很简单哦,你把WITH AS 这段放到最前面去就可以了,下面的语句可以直接调用的,当然中间不要有分号断开。
with表连接查询比用in查询效率高?是吗?
请优化下面这个查询
with
t_tree1 as (select C from a ),
t_tree2 as (select Cfrom b )
select * from p
where C in (select C from t_tree1) or C in (select C from t_tree2)
以上写法意义不大
优化结果如下:
采用exists
select p.* from a,b,p where a.c=p.c or c.c=p.c