mysql建立多表视图

mysql的view不支持子查询

CREATE VIEW waits AS SELECT * FROM ( select
id,company_id,createtime,updatetime,status, 1 as name FROM
fa_company_test where status in (‘试工中’,‘邀请中’, ‘试工完成’)

UNION ALL SELECT id,company_id,createtime,updatetime,status, 2 as name
FROM fa_contract where status=0 ) a

View’s SELECT contains a subquery in the FROM clause

所以每个表建立一个视图，然后操作视图

DROP VIEW  IF EXISTS `wait_test`;
create view wait_test
as
select id as table_id,company_id,createtime,updatetime,status, 'fa_company_test' as table_name  FROM fa_company_test where status in ('试工中','邀请中', '试工完成');

DROP VIEW  IF EXISTS wait_contract;
create view wait_contract
as
SELECT id as table_id,company_id,createtime,updatetime,status, 'fa_contract' as table_name   FROM fa_contract where status=0 ;

DROP VIEW  IF EXISTS waits;
CREATE VIEW waits
AS
select * FROM wait_test UNION ALL SELECT * FROM wait_contract


SELECT (@rowNum:=@rowNum+1) AS rowNo, waits.* FROM waits, (SELECT (@rowNum :=0) ) b ORDER BY table_id