Postgres -连接两个表的查询

Question

我有两个表，需要将它们连接到一个表中。

1: First Query

SELECT cu.user_name,
li.successdate
FROM logininfo li
JOIN user_mapping um ON um.user_key = li.username
JOIN cwd_user cu ON um.username = cu.user_name
ORDER BY successdate;

结果:

user_name      |       successdate
---------------------+-------------------------
K_Daniel           | 2018-09-02 13:38:22.331 

2: Second Query

WITH last_login_date AS
(SELECT user_id
, to_timestamp(CAST(cua.attribute_value AS double precision)/1000) AS last_login
FROM cwd_user_attribute cua
WHERE cua.attribute_name = 'lastAuthenticated'
AND to_timestamp(CAST(cua.attribute_value AS double precision)/1000) < (CURRENT_DATE))
SELECT c.user_name
, g.group_name
FROM cwd_user c
INNER JOIN last_login_date l ON (c.id = l.user_id)
INNER JOIN cwd_membership m  ON (c.id = m.child_user_id)
INNER JOIN cwd_group g       ON (m.parent_id = g.id)
WHERE g.group_name LIKE '%CEO-%' ;

结果:

user_name     |                     group_name
------------------+----------------------------------------------------
K_Daniel        | CEO-Building1 

以下是预期结果:

user_name      |       successdate       |        group_name
---------------------+-------------------------+-----------------------
K_Daniel        | 2018-09-02 13:38:22.331 |       CEO-Building1

连接这些表的合适查询是什么?

任何想法

谢谢,

Answer 1

经过几次努力，我找到了解决方案。

WITH last_login_date AS
(SELECT user_id
, to_timestamp(CAST(cua.attribute_value AS double precision)/1000) AS last_login
FROM cwd_user_attribute cua
WHERE cua.attribute_name = 'lastAuthenticated'
AND to_timestamp(CAST(cua.attribute_value AS double precision)/1000) < (CURRENT_DATE))
SELECT c.user_name
, li.successdate
, g.group_name
FROM cwd_user c
INNER JOIN last_login_date l ON (c.id = l.user_id)
INNER JOIN cwd_membership m  ON (c.id = m.child_user_id)
INNER JOIN cwd_group g       ON (m.parent_id = g.id)
INNER JOIN user_mapping um ON (c.user_name = um.username)
INNER JOIN logininfo li ON (um.user_key = li.username)
WHERE g.group_name LIKE 'CEO-%' ;