lavrinenkoe (lavrinenkoe) wrote,
lavrinenkoe
lavrinenkoe

Задачка для программиста-первокурсника

Простая задачка для студента-программиста, которую почему-то не могут решить некоторые вполне себе умные дядьки с опытом.

Дано:
1) Таблица клиентов declare @Client table(Id int not null, [Name] nvarchar(50))
2) Таблица менеджеров declare @Manager table(Id int not null, [Name] nvarchar(50))
3) Связующая таблица, которая фиксирует посещение клиентов и какого менеджера они посетили:
declare @Activity table(
Id int identity(1,1)
, ClientId int not null
, ManagerId int not null
, Created datetime default(getdate())
)

Тестовые данные:
insert into @Client(Id, [Name]) values (1, 'Вася')
insert into @Client(Id, [Name]) values (2, 'Коля')
insert into @Client(Id, [Name]) values (3, 'Митя')
insert into @Client(Id, [Name]) values (4, 'Петя')
insert into @Client(Id, [Name]) values (5, 'Максимка')

insert into @Manager(Id, [Name]) values (1, 'Эльвира')
insert into @Manager(Id, [Name]) values (2, 'Полуэкт')
insert into @Manager(Id, [Name]) values (3, 'Галлий')
insert into @Manager(Id, [Name]) values (4, 'Абрахам')
insert into @Manager(Id, [Name]) values (5, 'Спиноза')

insert into @Activity(ClientId, ManagerId) values (1, 3)
insert into @Activity(ClientId, ManagerId) values (1, 3)
insert into @Activity(ClientId, ManagerId) values (1, 5)
insert into @Activity(ClientId, ManagerId) values (2, 2)
insert into @Activity(ClientId, ManagerId) values (3, 1)
insert into @Activity(ClientId, ManagerId) values (3, 3)
insert into @Activity(ClientId, ManagerId) values (3, 1)

Задача
Получить список всех менеджеров и всех клиентов так, чтобы:
1) Если менеджер встречался с клиентом хоть раз, то это должно быть отображено в таблице.
2) Если менеджер не встречался с клиентом ни разу, то это тоже должно быть отображено в таблице.
3) Если клиент не встречался с менеджером ни разу, то и это тоже должно было быть отображено в таблице подобным образом:

Клиент Менеджер Активность
Вася Абрахам yes
Спиноза no
Коля no


(приведена не вся результирующая табличка)

* Запрос нужно сделать одним запросом безо всяких временных таблиц, подзапросов и union-ов.

Есть страшшшшшное решение с юнионами и подзапросами, которое выглядит так:
select distinct
 c.Name as Client
, m.Name as Manager
, 'yes' as Activity
from @Activity a inner join @Client c on a.ClientId = c.Id
             inner join @Manager m on a.ManagerId = m.Id
union all
select
'' as Client
, [Name] as Manager
, 'no'
from
@Manager where Id not in (select ManagerId from @Activity)
union all
select
[Name] as Client 
      , '' as Manager
, 'no'
from
      @Client where Id not in (select ClientId from @Activity)

Но это ведь не то, чего мы хотим, не правда-ли? ;-)

Решение под катом:

всё дело в хитром full outer join , про который почему-то все забывают и который нужно лишь грамотно использовать:

select
   c.Name as Client
   , m.Name as Manager
   , case when a.Id is null then 'no' else 'yes' end as Activity
from @Manager m
left outer join @Activity a on m.Id = a.ManagerId
full outer join @Client c on a.ClientId = c.Id

group by m.Name, c.Name, case when a.Id is null then 'no' else 'yes' end




Tags: t-sql, задача, программирование
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 0 comments