如何對此查詢進行選擇案例？ (How can I do select case to this query?)

問題描述

如何對此查詢進行選擇案例？ (How can I do select case to this query?)

SQL FIDDLE DEMO HERE

我有這種表結構：

    CREATE TABLE Users

            ([UserId] int, 
            [IdDepartment] int);

    INSERT INTO Users
        ([UserId], [IdDepartment])
    VALUES
        (1, 5),
        (2, 0),
        (3, ‑1),
        (4, 0),
        (5, ‑1),
        (6, 0);

    CREATE TABLE Department
        ([IdDepartment] int, [Name] varchar(23), [IdUser] int);

    INSERT INTO Department
        ([IdDepartment], [Name], [IdUser])
    VALUES
        (1, 'Sales', 3),
        (2, 'Finance', null ),
        (3, 'Accounting' , 5),
        (4, 'IT' ,3),
        (5, 'Secretary',null),
        (6, 'Sport',3);

我想得到一個這樣的結果：在用戶表中，如果IdDepartment為0，則表示用戶是管理員，因此他可以查看所有部門。如果用戶在 idpartment 中有 ‑1，則意味著用戶可以訪問有限的部門，所以在這種情況下，我對 Department 表進行內部連接以獲取該部門的列表。最後一種情況是，如果用戶在用戶表中的 idDepartament 編號與 0 不同且與 ‑1 不同，則表示該用戶只能訪問該部門。

我嘗試做類似的事情那個，但結構不好：

select
    case idDepartment
       when  0 then (select Name from Department)
       when ‑1 then (select Name from Department where IdUser = 3)
       else         (select Name from Department 
                      inner join Users on Department.idDepartment = Users.Department         
                      where Users.UserId = 3)
    end
from 
    Department
where 
    IdUser = 3

我怎樣才能做到這一點？謝謝。

我添加一個我想要得到的示例：

 ‑For the user that has the userid (1) ‑‑>

    Department Name
    ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
    Secretary




‑For the user that has the userid (2) ‑‑>

    Department Name
    ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
    Sales
    Finance
    Accounting
    IT
    Secretary
    Sport




‑For the user that has the userid (3) ‑‑>

    Department Name
    ‑‑‑‑‑‑‑‑‑‑‑‑‑‑‑
    Sales
    IT
    Sports

參考解法

方法 1:

You cant do something like that in a SELECT CASE, the best option is to just introduce some logic

DECLARE @IdUser INT = 3
DECLARE @userDepartment INT
SELECT @userDepartment = IdDepartment
FROM Users
WHERE UserId = @IdUser

IF @userDepartment = 0
BEGIN
    SELECT Name FROM Department
END
ELSE IF @userDepartment = ‑1
BEGIN
   SELECT Name FROM Department WHERE IdUser = @IdUser
END
ELSE
BEGIN
    SELECT Name FROM Department 
    INNER JOIN Users 
       ON Department.idDepartment = Users.IdDepartment 
    WHERE Users.UserId = @IdUser
END

By the way, You've hit upon why your structure is not ideal. If you had a junction table between Users & Departments, you could model any combination of what you have already with a much simpler query (At the cost of lots of rows in your junction table)

方法 2:

Your sample code is a bit confusing but feels like you are looking for something like this:

declare @id_user int = 3

select d.IdDepartment, d.Name
from Department d
where exists
  (
    select 1
    from Users u
    where u.[UserId] = @id_user
      and u.IdDepartment in (0, d.IdDepartment)
  )
  or d.[IdUser] = @id_user

which implements:

• if IdUser in Department table is the same as @id_user given ‑ he has access to this department for sure
• otherwise this user has access to department if his IdDep value is 0 or equal to corresponding department ID

But your permissions/security model smells not good and it's absolutely not scalable. You'd better invent another entity (table) to store permitted tuples: (IdUser, IdDepartment). Select statements would look much more clear in this case.

方法 3:

  declare @IdUser int = 3;
  SELECT u.[UserId], d.Name 
  from Users u
  join Department d
        on   u.[IdDepartment] =  0 
        or ( u.[IdDepartment] = ‑1 and d.[IdUser]       = u.[UserId] )
        or ( u.[IdDepartment] >  0 and d.[IdDepartment] = u.[IdDepartment] )
   where u.[UserId] = @IdUser 
   order by u.[UserId], d.Name

userID 3 should include sport

(by Esraa_92、Jamiec、Ivan Starostin、paparazzo)

參考文件

1. How can I do select case to this query? (CC BY‑SA 2.5/3.0/4.0)