Блог

У меня есть следующие таблицы:

 Projects (ID, Name, ManagerUser_ID)
Users(ID, Name, Active)
Delegates(ProjectID, UserID, OrderNo)
  

ManagerUser_ID является менеджером проекта для проекта и представляет собой ссылку на таблицу users. Пользователи, однако, могут быть Inactive . Итак, таблица делегатов — это таблица «многие ко многим», определяющая пользователей, которые могут получить доступ к данным проекта.

Что мне требуется, так это выбрать первого делегата для проекта, который Active предполагает, что менеджер Inactive . OrderNo Поле определяет порядок делегатов (1 является первым).

Некоторые примеры данных:

 Project
1, Project1, 2
2, Project2, 4
3, Project3, 1

Users
1, Joe, true
2, John, false
3, Dave, true
4, Bob, false

Delegates
1, 4, 1
1, 1, 2
1, 3, 3
2, 2, 1
2, 4, 2
2, 3, 3
  

Таким образом, выходные данные моего запроса должны были бы отображать:

 Project1, Joe
Project2, Dave
Project3, Joe
  

Показать проекты и пользователей для каждого проекта, где, если ManagerUser_ID неактивен, затем выберите User in Delegates с наименьшим OrderNo значением Active .

Кажется, это помогает (используя функцию window)

 SELECT  P.Name, ISNULL(U.Name, FirstDelegate.Name) AS ProjManager
FROM    Projects P LEFT OUTER JOIN
        Users U ON P.ManagerUser_ID = U.ID AND U.Active = 1 LEFT OUTER JOIN
        (
            SELECT * FROM
            (
                SELECT D.ProjectID, 
                       US.Name, 
                       ROW_NUMBER() OVER (PARTITION BY ProjectID ORDER BY OrderNo) AS SeqNo
                FROM  Delegates D INNER JOIN
                      Users US ON D.UserID = US.ID
                WHERE US.Active = 1
            ) AS Del
            WHERE Del.SeqNo = 1
        ) AS FirstDelegate ON P.ID = FirstDelegate.ProjectID
  

 Select projectName, userName
From

(
    Select projectName, userName, row_number() over (partition by projectName order by priority ASC) as rank
    From
    (
        SELECT p.name as projectName, u.name as userName, 1 as priority
        FROM projects p INNER JOIN users u ON u.active = true and u.id = p.ManagerUser_ID

        UNION

        SELECT TOP(1) p.name, u.name, 2
        FROM Delegates d INNER JOIN projects p  ON p.id = d.projectId
                         INNER JOIN users u ON u.id = d.userId
        Where u.active = true
        Order by u.OrderNo ASC
    )
)
where rank = 1
  

Я думаю, что это будет что-то вроде этого. Идея состоит в том, чтобы выполнить дополнительное объединение (используя левое объединение), чтобы найти более раннюю допустимую строку. Если вы можете найти эту строку, то, очевидно, текущая строка, которую вы создаете, не та, которая вам нужна:

 select p.Name,m.Name from Projects p inner join Users m on p.ManagerUser_ID = m.ID and m.Active = 1
union all
select
    p.Name,
    u.Name
from
    Projects p
        inner join
    Delegates d
        on
            p.ID = d.ProjectID
        inner join
    Users u
        on
            d.UserID = u.ID and
            u.Active = 1
        left join
    Delegates d_anti
        inner join
    Users u_anti
        on
            d_anti.UserID = u_anti.ID and
            u_anti.Active = 1
        on
            p.ID = d_anti.ProjectID and
            d_anti.OrderNo < d.OrderNo
where
    u_anti.ID is null
  

 select p.name, 
u.name, 
min(d.orderNo)
from projects p, 
users u, 
users manager, 
delegates d
where p.ManagerUser_ID = manager.id
and manager.active = false
and p.id = d.projectId
and d.userid = u.id
group by p.name, 
u.name