mysql - MYSQL 中多个表的 UNION 问题

Question

我尝试过，但没有运气，我有两个表“crm_rentals”和“crm_sales”。

两者具有相同的结构。

Id | portals_name
1  | {dubizzle}{JustRentals}{JustProperty}{propertyfinder}{bayut}
2  | {dubizzle}{JustRentals}{JustProperty}{propertyfinder}{bayut}
3  | {JustRentals}{JustProperty}{propertyfinder}
4  | {dubizzle}{JustProperty}{bayut} 

我想获取两个表中每个门户的编号，这是我尝试过的

select sum(dubizzle) dubizzle,sum(JustRentals) JustRentals,
sum(JustProperty) JustProperty,sum(propertyfinder) propertyfinder 
from ( (select count(id) as dubizzle from crm_rentals where 
portals_name like '%dubizzle%'
UNION
select count(id) as dubizzle from crm_sales where portals_name 
like  '%dubizzle%'
) a ,
(select count(id) as JustRentals from crm_rentals where 
 portals_name like '%JustRentals%'
UNION
select count(id) as JustRentals from crm_sales where 
portals_name like  '%JustRentals%') b,
(select count(id)  as JustProperty from crm_rentals where 
portals_name like '%JustProperty%'
UNION
select count(id)  as JustProperty from crm_sales where portals_name 
like '%JustProperty%') c ,
(select count(id) as propertyfinder from crm_rentals where 
portals_name like '%propertyfinder%'
UNION
select count(id) as propertyfinder from crm_rentals where 
portals_name like '%propertyfinder%'
) d )

我想要得到像这样的结果

Dubizzle    JustRentals  JustProperty Propertyfinder Others
100           100         100          100            100

问题:我无法得到此结果，我的查询给出语法错误。

更新我尝试了这个，但语法错误

select * from (select @table1:=(select count(id) as dubizzle 
from  crm_rentals where portals_name like '%dubizzle%') a,
@table2:=(select count(id) as dubizzle from crm_sales 
where portals_name   like '%dubizzle%') b, (@table1 +@table2) 
as dubizzle) f,
((select @table1:=(select count(id) as JustRentals from 
crm_rentals where portals_name like '%JustRentals%') c,
@table2:=(select count(id) as JustRentals from crm_sales 
where portals_name like '%JustRentals%') d, (@table1 +@table2)
 as JustRentals)  ff) AS f

Answer 1

你可以试试这个方法

SELECT * FROM ( 
(select count(id) as dubizzle from crm_rentals where portals_name like '%dubizzle%') AS a,
(select count(id) as JustRentals from crm_rentals where portals_name like '%JustRentals%') b,
(select count(id)  as JustProperty from crm_rentals where portals_name like '%JustProperty%') AS c
UNION
(select count(id) as dubizzle from crm_sales where portals_name like '%dubizzle%') AS a,
(select count(id) as JustRentals from crm_sales where portals_name like  '%JustRentals%') AS b,
(select count(id)  as JustProperty from crm_sales where portals_name like '%JustProperty%') AS c
)