mysql子语句查询 ,引用子查询结果(多参数,单参数)或with as

 多参数（都适用）

SELECT * FROM blade_user bu
inner join (

SELECT p.tenant_id,p.properties->>'$.contact' as real_name FROM gioet_instance AS p 
inner join gioet_instance AS s on   s.id = p.parent_id
where p.category_id = 2 AND s.category_id = 1 and s.id = 10 limit 1

) as pp on pp.tenant_id=bu.tenant_id and pp.real_name =bu.real_name;

单参数

SELECT * FROM blade_user bu
where bu.tenant_id=389767 and bu.real_name =(

SELECT p.properties->'$.contact' as real_name FROM gioet_instance AS p 
inner join gioet_instance AS s on   s.id = p.parent_id
where p.category_id = 2 AND s.category_id = 1 and s.id = 10 limit 1

);

如果某个子查询需重复使用，用with as

#mysql版本8及以上才支持，低版本报错
select version(); 

with PP as(
SELECT  p.tenant_id,p.properties->'$.contact' as real_name FROM gioet_instance AS p 
inner join gioet_instance AS s on   s.id = p.parent_id
where p.category_id = 2 AND s.category_id = 1 and s.id = 10 limit 1
)

SELECT * FROM blade_user bu
inner join pp on pp.tenant_id=bu.tenant_id and pp.real_name =bu.real_name;