sql - where 子句中的子查询

Question

下面的代码现在在基于Windows 身份验证的VIEW中运行，用户应该能够看到他们拥有的所有数据以及直接或间接向他们报告的数据。现在需要另一个 WHERE 子句来处理在 Authorize 列中提供给用户的数据的附加结果。

样本数据：表 TORGANIZATION_HIERARCHY

    ManagerID | ManagerEmail    | Email             | EmployeeID | Authorize |  Level
    ---------------------------------------------------------------------------------
    NULL      | NULL            | user0@@abc.com    | 1     | NULL           |  0
    1         | user0@@abc.com  | user1@@abc.com    | 273   | NULL           |  1
    273       | user1@@abc.com  | user2@@abc.com    | 16    | NULL           |  2
    273       | user1@@abc.com  | SJiang@@abc.com   | 274   | NULL           |  2
    273       | user1@@abc.com  | [e-mail]    | 285   | user2@@abc.com; [e-mail] |  2
    285       | [e-mail]  | [e-mail] | 286   | NULL           |  3
    274       | SJiang@@abc.com | [e-mail]   | 275   | NULL           |  3
    274       | SJiang@@abc.com | [e-mail] | 276   | NULL           |  3
    16        | [e-mail]  | [e-mail]     | 23    | NULL           |  3

样本数据：表 TRANS

    Email             | Destination_account      | Customer_service_rep_code   
    -----------------------------------------------------------
    [e-mail]    |   Philippines            | 12646
    [e-mail]    |   Canada                 | 95862
    [e-mail] |   Italy                  | 98524
    user2@@abc.com    |   Italy                  | 29185
    user3@@abc.com    |   Brazil                 | 58722

当用户 [e-mail] (285) 登录时，底部查询正在工作。它可以看到 EmployeeID 285 和 286 的所有数据。我需要添加另一个用户 ( [e-mail] ) 授权查看的where 语句在授权列中。所以结果用户 [e-mail] 应该看到 EmployeeID 285、286、16、23。

WITH CTE 
 AS (SELECT OH.employeeid, 
            OH.managerid, 
            OH.email AS EMPEMAIL, 
            1        AS level 
     FROM   TORGANIZATION_HIERARCHY OH 
     WHERE  OH.[email] = (SELECT SYSTEM_USER)  --Example [e-mail]
     UNION ALL 
     SELECT CHIL.employeeid, 
            CHIL.managerid, 
            CHIL.email, 
            level + 1 
     FROM   TORGANIZATION_HIERARCHY CHIL 
            JOIN CTE PARENT 
              ON CHIL.[managerid] = PARENT.[employeeid]), 
 ANOTHERCTE 
 AS (SELECT   
    T.[email], 
    T.[destination_account], 
    T.[customer_service_rep_code] 
     FROM   [KGFGJK].[DBO].[TRANS] AS T) 

   SELECT * 
   FROM   ANOTHERCTE 
   INNER JOIN CTE 
           ON CTE.empemail = ANOTHERCTE.[email]; 

标签： sqlsql-server

Answer 1

这将根据列授权为您提供所需的内容。结果应该是 16 和 23

Select b.employeeid from TORGANIZATION_HIERARCHY a inner join TORGANIZATION_HIERARCHY b
on a.Authorize like  '%' + b.Email + '%'
where a.Email = '[e-mail]'

让我知道

完整的解决方案：

为了让您能够看到 [e-mail]，我必须更正表 6#TRANS 中的电子邮件。你在那里写的是 user3@@abc.com 而不是 [e-mail]。@ 并不是 @@。下面的代码用于您的测试。在你可以用你的表名替换之后

IF OBJECT_ID('tempdb..#TORGANIZATION_HIERARCHY') IS NOT NULL DROP TABLE #TORGANIZATION_HIERARCHY;   


    select  NULL  as ManagerID    ,NULL as ManagerEmail           ,'user0@@abc.com'  as Email  ,1 as EmployeeID    ,NULL    as Authorize       , 0 as Level into #TORGANIZATION_HIERARCHY
    union select    1         ,'user0@@abc.com',  'user1@@abc.com'    ,273   ,NULL           , 1
    union select    273       ,'user1@@abc.com',  'user2@@abc.com'    ,16    ,NULL           , 2
    union select    273       ,'user1@@abc.com',  'SJiang@@abc.com'   ,274   ,NULL           , 2
    union select    273       ,'user1@@abc.com',  '[e-mail]'    ,285   ,'user2@@abc.com; [e-mail]' , 2
    union select    285       ,'[e-mail]',  '[e-mail]' ,286   ,NULL           , 3
    union select    274       ,'SJiang@@abc.com', '[e-mail]'   ,275   ,NULL           , 3
    union select    274       ,'SJiang@@abc.com', '[e-mail]' ,276   ,NULL           , 3
    union select    16        ,'[e-mail]',  '[e-mail]'  ,23    ,NULL           , 3

    --select * from #TORGANIZATION_HIERARCHY

IF OBJECT_ID('tempdb..#TRANS') IS NOT NULL DROP TABLE #TRANS;   

    select '[e-mail]'  as Email ,    'Philippines'  as Destination_account         ,  12646 as Customer_service_rep_code into #TRANS
    union select '[e-mail]'   ,    'Canada'                ,  95862
    union select '[e-mail]',    'Italy'                 ,  98524
    union select 'user2@@abc.com'   ,    'Italy'                 ,  29185
    union select '[e-mail]'   ,    'Brazil'                ,  58722

    ;WITH CTE 
     AS (SELECT OH.employeeid, 
                OH.managerid, 
                OH.Authorize, 
                OH.email AS EMPEMAIL, 
                1        AS [level] 
         FROM   #TORGANIZATION_HIERARCHY OH 
         WHERE  OH.[email] = (SELECT '[e-mail]')  --Example
         UNION ALL 
         SELECT CHIL.employeeid, 
                CHIL.managerid, 
                CHIL.Authorize, 
                CHIL.email, 
                CHIL.[level] + 1 
         FROM   #TORGANIZATION_HIERARCHY CHIL 
                JOIN CTE PARENT 
                  ON CHIL.[managerid] = PARENT.[employeeid]), 
     ANOTHERCTE 
     AS (SELECT   
        T.[email], 
        T.[destination_account], 
        T.[customer_service_rep_code] 
         FROM   #TRANS AS T) 

       SELECT *
       FROM   ANOTHERCTE 
       RIGHT JOIN 
       (
            select a.EmployeeID, a.ManagerID, a.Authorize, a.Email as empemail, a.[level] From CTE INNER JOIN #TORGANIZATION_HIERARCHY a on lower(CTE.Authorize) like  '%' + lower(a.Email) + '%'
            union
            select * From CTE 
       ) CTE
               ON CTE.empemail = ANOTHERCTE.[email]
    order by [level]

输出：