在 MySql 查询中加入 3 个表

Erjon 发表于 Dev

埃尔容

我在 MySql 数据库中有这 3 个表。

users
+----+------+--------+------+
| Id | Name | Status | Role |
+----+------+--------+------+
|  1 | A    | Aktiv  | Op   |
|  2 | B    | Aktiv  | Op   |
|  3 | C    | Aktiv  | Op   |
|  4 | D    | Aktiv  | Op   |
+----+------+--------+------+

cnt
+----+------+------------+------+
| Id | Name | Date       | Type |
+----+------+------------+------+
|  1 | A    | 2017-11-09 | Web  |
|  2 | B    | 2017-11-09 | Web  |
|  3 | C    | 2017-11-09 | Web  |
|  4 | C    | 2017-11-09 | Inb  |
|  5 | A    | 2017-11-09 | Web  |
+----+------+------------+------+

Lead
+----+------+------------------+------------+
| Id | Name | Date             | Type       |
+----+------+------------------+------------+
|  1 | A    | 2017-11-09 00:24 | Imported   |
|  2 | B    | 2017-11-09 09:32 | Activation |
|  3 | B    | 2017-11-09 10:56 | Activation |
|  4 | D    | 2017-11-09 12:21 | Activation |
|  5 | D    | 2017-11-10 12:22 | Activation |
+----+------+------------------+------------+

我试图将它们加入一个主表中，但没有成功，我使用的查询是：

SELECT IFNULL(u.Name,'Total') as "Name",
Sum(IF(c.Type = 'Web',1,0)) as "Cnt Web",
Sum(IF(l.Type = 'Activation',1,0)) as "Lead Web"
FROM users u
LEFT JOIN cnt c ON u.Name = c.Name and c.Date = '2017-11-09'
LEFT JOIN lead l ON u.Name = l.Name and l.Date>= '2017-11-09' AND l.Date< '2017-11-10'
WHERE u.Status = 'Aktiv' AND u.Role = 'Op'
GROUP BY u.Name WITH ROLLUP

我需要的结果是这样的表：

+----+------+--------+---------+
| Id | Name | Cnt Web| Lead Web|
+----+------+------------------+
|  1 | A    | 2      | 0       |
|  2 | B    | 1      | 2       |
|  3 | C    | 1      | 0       | 
|  4 | D    | 0      | 1       |   
+----+------+------------------+

当我尝试将第一张桌子与第二张桌子或第一张桌子与第三张桌子连接时，我得到了正确的结果，但是当我将它们全部加入时，我无法得到所需的结果。

任何答案都是最受欢迎的。先感谢您。

SEarle1986

这是使用相关子查询的解决方案

SELECT  u.Id,
        u.Name,
        (SELECT COUNT(Name) FROM cnt WHERE Name = u.name AND type = 'Web' AND Date = '2017-11-09') AS cnt_web,
        (SELECT COUNT(Name) FROM lead WHERE Name = u.name AND type = 'Activation' AND Date>= '2017-11-09' AND Date< '2017-11-10') AS cnt_lead
FROM    users u
WHERE   u.Status = 'Aktiv' AND u.Role = 'Op'

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。