我有 3 个表，其中包含如下示例数据。我正在尝试获取有关代理名称、代理的客户数量以及代理上次登录的报告。如果代理没有客户，他将不会有任何记录（但可能有最后一次登录日期）。相反，代理可能有客户，但从未登录。
table agents
| id | first | last |
----------------------------------------
| 1 | dave | schultz |
| 2 | bobby | clarke |
| 3 | ed | hospidar |
| 4 | derek | smith |
table agentclients
| id | agentid | clientid |
----------------------------------------
| 1 | 2 | 345 |
| 2 | 3 | 347 |
| 3 | 3 | 221 |
| 4 | 1 | 567 |
table loginhistory
| id | userid | usertype | ts
-------------------------------------------------------
| 1 | 2 | A | 2018-11-17 14:16:44 |
| 2 | 3 | A | 2018-11-24 20:46:16 |
| 3 | 4 | A | 2018-11-27 13:07:58 |
| 4 | 1 | A | 2019-01-05 13:45:01 |
| 5 | 4 | A | 2019-01-19 06:36:23 |
| 6 | 3 | A | 2019-01-24 02:13:44 |
Results:
agent id | agent name | clients | last login
-------------------------------------------------------
1 | dave schultz | 1 | 2019-01-05 13:45:01
2 | bobby clark | 1 | 2018-11-17 14:16:44
3 | ed hospidar | 2 | 2019-01-24 02:13:44
4 | derek smith | 0 | 2018-11-27 13:07:58
我似乎可以得到计数或最大登录次数，但是如果我尝试加入所有 3 个，则计数不正确。
SELECT a.id, a.first, a.last, count(ac.clientid) as 'client count'
FROM agents a
LEFT JOIN agentclients ac on a.id = ac.agentid
WHERE a.agentdeleted = 0
GROUP BY ac.agentid;
适用于计数客户
如果我尝试添加 max() 计数中断：
SELECT a.id, a.first, a.last, count(ac.clientid) as 'client count',
max(l.ts) AS 'lastlogin'
FROM agents a
LEFT JOIN agentclients ac on a.id = ac.agentid
LEFT JOIN loginhistory l on l.userid = a.aid and l.usertype = 'A'
WHERE a.agentdeleted = 0
GROUP BY ac.agentid;
您应该计算agentclients表中代理的唯一记录数量。您可以在列的帮助DISCTINCT下完成agentclients.id
SELECT a.id,
a.first,
a.last,
COUNT( DISTINCT ac.id) as 'client count',
max(l.ts) AS 'lastlogin'
FROM agents a
LEFT JOIN agentclients ac on a.id = ac.agentid
LEFT JOIN loginhistory l on l.userid = a.aid and l.usertype = 'A'
WHERE a.agentdeleted = 0
GROUP BY ac.agentid;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句