mysql - 与其他表交叉不同的列值

Question

根据 PERMISSIONS 表 (query_1) 中“USER”的不同值，我打算将信息与 ACCESS_CONTROL 表 (query_2) 中的查询值进行交叉) 了解每个具有访问权限的“用户”多久没有登录。

我打算通过“USER”键字段将 query_1 与 query_1 交叉。怎么可能做到？

查询_1:

SELECT DISTINCT(`USER`) FROM `PERMISSIONS`;

查询_2:

SELECT 

`USER`, 
MAX(`REGISTRY_DATE`) AS MAX_REGISTRY_DATE, 
DATEDIFF(CURDATE(),MAX(`REGISTRY_DATE`)) AS DIFFERENCE_IN_DAYS

FROM `ACCESS_CONTROL` 

WHERE STATUS = 'Access Allowed' 

GROUP BY `USER` ORDER BY DIFFERENCE_IN_DAYS DESC;

预期结果:https://imgur.com/a/f5KQXWC

Answer 1

第一个查询与第二个查询的左连接:

select
  u.user, 
  coalesce(a.registry_date, 'never') max_registry_date,
  coalesce(a.difference_in_days, 'never') difference_in_days
from (
  select distinct user
  from permissions
) u left join (
  select user, max(registry_date) registry_date, 
  datediff(curdate(), max(registry_date)) difference_in_days
  from access_control 
  where `status` = 'Access Allowed' 
  group by user 
) a on a.user = u.user 

请参阅demo .
结果:

| user     | max_registry_date | difference_in_days |
| -------- | ----------------- | ------------------ |
| john     | 2019-09-06        | 0                  |
| susan    | 2019-09-01        | 5                  |
| mike     | 2019-08-06        | 31                 |
| anderson | never             | never              |