如何在join查询后获得不同的用户名，ORACLE

Question

2个表user表和role_post表(state列定义在role_post表中)，一个用户可以有许多role_post行，即使处于一个状态。所以我想要相同状态的10个不同的用户，我可以修改我的查询吗?

SELECT
u.username,
u.full_name,
u.created_at,
r.role_id r_id,
rp.state,
FROM
user_account u
INNER JOIN ROLE r ON
u.user_id = r.user_id
LEFT JOIN role_post rp ON
r.role_id = rp.role_id
LEFT JOIN post p ON
p.post_id = rp.post_id
INNER JOIN (
SELECT
*
FROM
(
SELECT
u.user_id
FROM
user_account u
LEFT JOIN ROLE_POST rp2 ON
u.ROLE_ID = rp2.ROLE_ID
WHERE
rp2.STATE = '001'
ORDER BY
u.created_at DESC
WHERE
ROWNUM <= 10 ) uu ON
uu.user_id = u.user_id
ORDER BY
u.created_at DESC

Answer 1

你的查询提出了一些关于你的数据模型的问题，这使得很难给出一个自信的答案。

• 当你特别希望你的结果包括那些表中存在匹配的行时，为什么你在一些表上做LEFT JOIN?
• 为什么在POST没有被使用的情况下你要加入呢?
• 如果一个用户可以承担多个角色，为什么USER_ACCOUNT有ROLE_ID列?

因此，忽略所有这些，我建议您将CROSS APPLY功能与FETCH FIRST x ROWS ONLY结合使用。您可以从USER_ACCOUNT中获得用户列表，然后从CROSS APPLY中获得具有您正在查找的状态的ROLE_POST的第一条记录的用户列表。因为每个用户只连接到一行，所以最后每个用户仍然只有一行。然后，将结果限制为您想要的列表中的任意10个用户(例如，最近创建的10个用户)。

放在一起，它可能看起来像这样:

SELECT u.username,
u.full_name,
u.created_at,
x.role_id r_id,
x.state
FROM   user_account u
CROSS APPLY ( SELECT  r.role_id, rp.state
FROM    role r 
INNER JOIN role_post rp ON rp.role_id = r.role_id
WHERE   r.user_id = u.user_id 
AND     rp.state = '001'
-- Best to ORDER BY something here so results are predictable and consistent
ORDER BY ???
FETCH FIRST 1 ROW ONLY ) x
ORDER BY u.created_at DESC
FETCH FIRST 10 ROWS ONLY;