mysql创建视图不能包涵子查询的解决办法。View's SELECT contains a subquery in the FROM clause

如下查询是没问题，但要创建成视图就报View's SELECT contains a subquery in the FROM clause错误。

CREATE or REPLACE VIEW `v_user_adunion_reg` AS

select a.name as user_name,b.* from t_users a,t_adunion_result b where a.id = b.userid and orderid is null and czid is null and txid is null and bkid IS null and hbid is null
and b.id in (select id from (select c.userid,min(c.id) as id from t_adunion_result c group by c.userid ) as d);

执行这句会报错。

解决办法：通过创建中间视图作关联查询解决。

CREATE or REPLACE VIEW `v_user_adunion_regsub` AS

select c.userid,min(c.id) as id from t_adunion_result c group by c.userid;

CREATE or REPLACE VIEW `v_user_adunion_reg` AS
select a.name as user_name,b.* from t_users a,t_adunion_result b,v_user_adunion_regsub c where a.id = b.userid and b.id =c.id and orderid is null and czid is null and txid is null and bkid IS null and hbid is null ;