Блог

У меня есть таблица базы данных с именем logoninfo . Ниже приведена схема.

    Record ID|User ID | Computer ID |LOGON_TIME|LOGOFF_TIME
 

Если время выхода из системы равно 0, то пользователь в данный момент зарегистрирован на компьютере.
Имя пользователя находится в отдельной таблице с именем «user» со схемой

    user id  | user name 
 

Имя компьютера находится в отдельной таблице с именем «компьютер» со схемой

     computer id | computer name 
 

Мне нужно иметь отчет, в котором есть имя пользователя и соответствующий ему текущий зарегистрированный компьютер (разделенный запятой)

         user name | computer names
 

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

 select user.name as USER_NAME,
    userLogon."comps" as COMPUTERS
from user
    inner join (
        select user_id as "user_resource",
            string_agg(compRes.name, ' ,') as "comps"
        from logoninfo
            inner join computer compRes on logoninfo.computer_id = compRes.computer_id
        where logoninfo.logoff_time = 0
        group by logoninfo.user_id
    ) userLogon on userLogon."user_resource" = user.user_id
 

Итак, я попытался добавить distinct в string_agg функцию, но это невозможно в SQL Server.

Пожалуйста, помогите мне с запросом к требуемому отчету.

Заранее спасибо.

Я сделал это так. Вы можете удалить # знак, это означает, что я использовал временную таблицу.

 SELECT u.name as [USER_NAME]
     , l.comps as [COMPUTERS]
FROM #user u
OUTER APPLY (
  SELECT STRING_AGG(c.name, ', ') as [comps]
  FROM (
    SELECT c.name
    FROM #logoninfo l
    JOIN #computer c ON l.computer_id = c.computer_id
    WHERE l.user_id = u.user_id
      AND l.logoff_time = 0
    GROUP BY c.name
  ) c
) l
 

И результат:

Вот способ сделать это.

Ранняя фильтрация и агрегирование с использованием string_agg

 select userLogon.user_resource
         , string_agg(compRes.name, ' ,') as concat_computer
    from (
          select distinct 
                  user_id as "user_resource",
                  compRes.name as "comps"
            from logoninfo
            join computer compRes 
              on logoninfo.computer_id = compRes.computer_id
            join user us
              on logonginfo.user_id=us.user_id
            where logoninfo.logoff_time = 0
          ) userLogon
  group by userLogon.user_resource

      
 

Я бы использовал коррелированный подзапрос или боковое соединение:

 select u.*, c.*
from users u
cross apply (
    select string_agg(name) as all_computers
    from (
        select distinct c.name
        from computer c
        inner join logoninfo li on li.computer_id = c.computer_id
        where li.user_id = u.user_id and li.logoff_time = 0
    ) c
) c