mysql - 显示登录名而不是 User_id

Question

从 xxcus.xxacl_pn_employee_v 表中，我得到如下 LOGIN_NAME

我想要显示数据的列是CREATED_BY，表名称是xxacl_pn_flat_status_his，User_ID是5681 >

我尝试了下面的查询，但我得到的是 User_id 而不是 LOGIN_NAME。

SELECT   *
FROM (SELECT   ROWNUM srno, pn.project_id AS project_id, pn.property_name,
               pn.building, pn.building_id AS building_id,
               cd.flat_id AS flat_id, ab.flat_status, ab.comments,
               ab.created_by,
               TO_CHAR (ab.creation_date, 'dd/MM/yyyy') AS creation_date
          FROM xxcus.xxacl_pn_projbuild_v pn INNER JOIN xxacl_pn_flat_status_his ab
               ON pn.project_id = ab.project_id
             AND pn.building_id = ab.building_id
               INNER JOIN xxacl_pn_flat_det_v cd ON cd.flat_id =
                                                                ab.flat_id
               INNER JOIN xxcus.xxacl_pn_employee_v emp
               ON ab.created_by = emp.user_id
         WHERE ab.delete_flag = 'N' AND cd.flat_id = '9987'
      ORDER BY ab.creation_date) pp ORDER BY 1 DESC

所以我的问题是

I am unable to get LOGIN_NAME

我在这里做错了什么？

我正在使用ORACLE

Answer 1

您的查询中未包含 LOGIN_NAME。试试这个:

SELECT *
FROM (
    SELECT ROWNUM srno
        ,pn.project_id AS project_id
        ,pn.property_name
        ,pn.building
        ,pn.building_id AS building_id
        ,cd.flat_id AS flat_id
        ,ab.flat_status
        ,ab.comments
        ,ab.created_by
        ,emp.LOGIN_NAME -- <== Query Login name here
        ,TO_CHAR(ab.creation_date, 'dd/MM/yyyy') AS creation_date
    FROM xxcus.xxacl_pn_projbuild_v pn
    INNER JOIN xxacl_pn_flat_status_his ab
        ON pn.project_id = ab.project_id
            AND pn.building_id = ab.building_id
    INNER JOIN xxacl_pn_flat_det_v cd
        ON cd.flat_id = ab.flat_id
    INNER JOIN xxcus.xxacl_pn_employee_v emp
        ON ab.created_by = emp.user_id
    WHERE ab.delete_flag = 'N'
        AND cd.flat_id = '9987'
    ORDER BY ab.creation_date
    ) pp
ORDER BY 1 DESC