如何从不同的数据库统计和连接mySQL上的两个表？

问题描述：

我有两个数据库。这些是core和push。如何从不同的数据库统计和连接mySQL上的两个表？

这是core数据库subs表：

+----+---------+----+----+-----+ 
| id | service | ad | op | act | 
+----+---------+----+----+-----+ 
| 1 | CHO  | 96 | x | 1 | 
| 2 | CHO  | 98 | x | 1 | 
| 3 | DANG | 96 | x | 1 | 
| 4 | HOORAY | 96 | x | 1 | 
| 5 | CHO  | 98 | x | 1 | 
| 6 | DANG | 96 | x | 1 | 
+----+---------+----+----+-----+

，这是push数据库buff表：

+----+---------+-------+ 
| id | service | sub | 
+----+---------+-------+ 
| 1 | DANG | daily | 
| 2 | HOORAY | daily | 
| 3 | DANG | daily | 
+----+---------+-------+

我想数subs和buff。我做了这个查询潜艇：

select service, ad, op, count(1) as sub where act=1 group by service,ad,op; 

RESULT: 
+---------+----+----+-----+ 
| service | ad | op | sub | 
+---------+----+----+-----+ 
| CHOO | 96 | x | 1 | 
| CHOO | 98 | x | 2 | 
| HOORAY | 96 | x | 1 | 
| DANG | 96 | x | 2 | 
+---------+----+----+-----+

，这是buff查询：

select service, count(1) as pushed 
from buff a where sub = 'daily' 
group by service; 

RESULT: 
+---------+---------+ 
| service | pushed | 
+---------+---------+ 
| HOORAY | 1 | 
| DANG | 2 | 
+---------+---------+

所以，我的问题是如何加入从两个不同的数据库这些表？这里是我希望得到的结果：

+---------+----+----+-----+--------+ 
| service | ad | op | sub | pushed | 
+---------+----+----+-----+--------+ 
| CHOO | 96 | x | 1 | 0 | 
| CHOO | 98 | x | 2 | 0 | 
| HOORAY | 96 | x | 1 | 1 | 
| DANG | 96 | x | 2 | 2 | 
+---------+----+----+-----+--------+

答

一个选项是连接在一起的两个电流子查询。我在下面使用了一个完整的外连接，因为我们想要处理服务可能只出现在subs或buff表中的可能性，但不能同时处理两者。

SELECT t1.service, 
     t1.ad, 
     t1.op, 
     t1.sub, 
     COALESCE(t2.pushed, 0) AS pushed 
FROM 
(
    SELECT service, ad, op, COUNT(*) AS sub 
    FROM subs 
    WHERE act = 1 
    GROUP BY service,ad,op 
) t1 
LEFT JOIN 
(
    SELECT service, COUNT(1) AS pushed 
    FROM buff a 
    WHERE sub = 'daily' 
    GROUP BY service 
) t2 
    ON t1.service = t2.service 
UNION 
SELECT t2.service, 
     COALESCE(t1.ad, -1), 
     COALESCE(t1.op, -1), 
     COALESCE(t1.sub, -1), 
     t2.pushed 
FROM 
(
    SELECT service, ad, op, COUNT(*) AS sub 
    FROM subs 
    WHERE act = 1 
    GROUP BY service,ad,op 
) t1 
RIGHT JOIN 
(
    SELECT service, COUNT(1) AS pushed 
    FROM buff a 
    WHERE sub = 'daily' 
    GROUP BY service 
) t2 
    ON t1.service = t2.service

它的工作原理。非常感谢你的帮助。上帝保佑你:) – finarifina

@ArifinaIstighfariZahro对于真正丑陋的外连接的Aplogies。如果你确定两个表中的一个保证在另一个表中有所有的服务，那么你可以只用“UNION”的一半。 –

答

使用的数据库名称，带有圆点的表名前缀，我不检查下面的句子，它只能说明这个想法：

SELECT service, ad, op, count(1) AS sub FROM core.subs AS t1 
LEFT JOIN push.buff AS t2 ON t1.service=t2.service WHERE t1.act=1 
AND sub = 'daily' GROUP BY t1.service,t1.ad,t1.op

如何从不同的数据库统计和连接mySQL上的两个表？

相关推荐