Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
angelius Member Откуда: Сообщений: 37 |
Задача состоит в том чтобы проверить отвечает ли рабочий необходимым требованиям. Чтобы было проще объяснить, вот DDL таблиц:CREATE TABLE [WorkerSkills]( [WorkerId] [bigint] NOT NULL, [SkillName] [varchar](100) NOT NULL ) GO CREATE TABLE [SkillCombinator]( [SetId] [int] NOT NULL, [SkillCombinator] [varchar](5) NOT NULL ) GO CREATE TABLE [RequiredSkills]( [SetId] [int] NOT NULL, [SkillName] [varchar](100) NOT NULL ) GO вот тестовые данные: INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'A') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'B') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (1, 'C') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (2, 'D') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (2, 'X') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (3, 'E') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'A') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'B') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'H') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (4, 'I') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'A') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'B') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'C') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'E') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'G') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'H') INSERT [WorkerSkills] ([WorkerId], [SkillName]) VALUES (5, 'I') INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (1, 'AND') INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (2, 'OR') INSERT [SkillCombinator] ([SetId], [SkillCombinator]) VALUES (3, 'AND') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'A') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'B') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (1, 'C') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'D') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'E') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (2, 'F') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'G') INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'H') Это значит что есть 3 набора (set), в каждом из которых по три навыка (skill). set 1: A and B and C set 2: D or E or F set 3: G and H and I И рабочие с навыками: worker 1: A, B, C worker 2: D, X worker 3: E worker 4: A, B, H, I worker 5: A, B, C, E, G, H, I Нужно написать функцию в Sql Server 2008, которая принимает параметры WorkerId and SetCombinator и определяет, отвечает ли рабочий необходимым навыкам. Пример 1:
WorkerId: 1
SetCombinator: OR
Это значит что все наборы должны сравниваться с использованием OR, т.е.: set 1: A and B and C OR set 2: D or E or F OR set 3: G and H and I Результат должен быть true (1), т.к. рабочий отвечает критериям 1-го набора. Пример 2:
WorkerId: 4
SetCombinator: OR
Результат: false (0). Пример 3:
WorkerId: 1
SetCombinator: AND
Это значит что все наборы должны сравниваться с использованием AND, т.е.: set 1: A and B and C AND set 2: D or E or F AND set 3: G and H and I Результат: false, т.к. рабочий отвечает только навыкам 1-го набора, но не 2-го и 3-го. Пример 4:
WorkerId: 5
SetCombinator: AND
Результат: true, т.к. рабочий соответствует критериям всех наборов. Задачка сложная, по-крайней мере для меня, поэтому буду очень благодарен, если кто-нибудь сможет помочь! |
26 янв 14, 12:34 [15470427] Ответить | Цитировать Сообщить модератору |
angelius Member Откуда: Сообщений: 37 |
в тестовых данных не добавилась последняя строка, не нашел как можно редактировать свое сообщение, поэтому пишу здесь:INSERT [RequiredSkills] ([SetId], [SkillName]) VALUES (3, 'I') |
26 янв 14, 12:43 [15470449] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9632 |
Как-то так:create function fnMatchedToSkillsSet ( @WorkerId int, @Condition varchar(3) ) returns table as return ( with x as ( select sc.SetId, count(distinct ws.SkillName) as SkillCount from dbo.SkillCombinator sc join dbo.RequiredSkills rs on rs.SetId = sc.SetId left join dbo.WorkerSkills ws on ws.WorkerId = @WorkerId and ws.SkillName = rs.SkillName group by sc.SetId, sc.SkillCombinator having (sc.SkillCombinator = 'AND' and count(distinct ws.SkillName) = count(*)) or (sc.SkillCombinator = 'OR' /*and count(ws.SkillName) > 0*/) ) select cast(case when count(*) > 0 then 1 else 0 end as bit) as Result from x where SkillCount > 0 having (@Condition = 'AND' and count(*) = (select count(*) from dbo.SkillCombinator)) or (@Condition = 'OR') ); go |
26 янв 14, 14:10 [15470620] Ответить | Цитировать Сообщить модератору |
angelius Member Откуда: Сообщений: 37 |
Супер! Красивое решение! Вроде все работает правильно, единственное при @Condition='AND', когда рабочий не соответствует требованиям, ничего не возвращается. Я не силен в CTE, но попробую разобраться и найти эту небольшую проблемку. Спасибо большое! :) |
26 янв 14, 15:04 [15470759] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9632 |
create function fnMatchedToSkillsSet ( @WorkerId int, @Condition varchar(3) ) returns table as return ( with x as ( select sc.SetId, case when sc.SkillCombinator = 'AND' and count(distinct ws.SkillName) = count(*) then 1 when sc.SkillCombinator = 'OR' then 1 else 0 end as MatchedToSkillsSet from dbo.SkillCombinator sc join dbo.RequiredSkills rs on rs.SetId = sc.SetId left join dbo.WorkerSkills ws on ws.WorkerId = @WorkerId and ws.SkillName = rs.SkillName group by sc.SetId, sc.SkillCombinator ) select case when @Condition = 'AND' and sum(MatchedToSkillsSet) = (select count(*) from dbo.SkillCombinator) then 1 when @Condition = 'OR' then 1 else 0 end as Result from x ); count(distinct ws.SkillName) можно заменить на count(ws.SkillName) если для таблицы WorkerSkills (WorkerId, SkillName) уникально. |
||
26 янв 14, 15:49 [15470851] Ответить | Цитировать Сообщить модератору |
angelius Member Откуда: Сообщений: 37 |
Теперь стало возвращать "1" для параметров: 4, 'OR'. |
26 янв 14, 16:12 [15470906] Ответить | Цитировать Сообщить модератору |
invm Member Откуда: Москва Сообщений: 9632 |
create function fnMatchedToSkillsSet ( @WorkerId int, @Condition varchar(3) ) returns table as return ( with x as ( select sc.SetId, nullif(case when sc.SkillCombinator = 'AND' and count(distinct ws.SkillName) = count(*) then count(distinct ws.SkillName) when sc.SkillCombinator = 'OR' then count(distinct ws.SkillName) end, 0) as SkillsCount from dbo.SkillCombinator sc join dbo.RequiredSkills rs on rs.SetId = sc.SetId left join dbo.WorkerSkills ws on ws.WorkerId = @WorkerId and ws.SkillName = rs.SkillName group by sc.SetId, sc.SkillCombinator ) select case when @Condition = 'AND' and count(SkillsCount) = (select count(*) from dbo.SkillCombinator) then 1 when @Condition = 'OR' and count(SkillsCount) > 0 then 1 else 0 end as Result from x ); |
26 янв 14, 16:22 [15470926] Ответить | Цитировать Сообщить модератору |
angelius Member Откуда: Сообщений: 37 |
Отлично! Теперь все работает! Спасибо большое! |
26 янв 14, 16:36 [15470953] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |