ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Вопрос по сложной выборке
16 сообщений из 16, страница 1 из 1
Вопрос по сложной выборке
    #34717155
VoDA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VoDA
Участник
Всем хорошего настроения :)

Дано: Есть пользователи, они имеют различные сертификаты. Есть задачи, которые тоже имеют список необходимых для выполнения сертификатов. Нужно найти ВСЕХ пользователей которые могут выполнить задачу (имеют ВСЕ сертификаты, которые требуются в задаче)

Данные: Есть пользователи, есть сертификаты. они связаны многие-ко-многим.
Есть задачи. Задача связана с сертификатами многие-ко-многим.

Требуется: найти ВСЕХ пользователей у которых есть ВСЕ сертификаты связанные с задачей.
если у задачи нет сертификатов - ее может выполнить любой.

Как сделать пока не понял :(
Можно сделать на app сервере, но хочется на СУБД, чтобы не тянуть огромные пакеты данных.
...
Рейтинг: 0 / 0
09.08.2007, 14:28
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34717518
belugin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
belugin
Участник
А если перевормулировать так: выбрать всех ползователей для которых нет такого сертификата который есть у задачи но нет у пользователя
...
Рейтинг: 0 / 0
09.08.2007, 15:49
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34717558
Фотография Сергей Васкецов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сергей Васкецов
Участник
VoDA
DDL таблиц приведите - напишу решение.
...
Рейтинг: 0 / 0
09.08.2007, 15:58
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34717830
Фотография softwarer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
softwarer
Участник
VoDAКак сделать пока не понял :(
Если тупо в лоб: "количество сертификатов, связывающих пользователя с задачей, равняется количеству сертификатов в задаче".
...
Рейтинг: 0 / 0
09.08.2007, 17:05
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34717836
VoDA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VoDA
Участник
Сергей Васкецов
DDL таблиц приведите - напишу решение.

PostgreSQL

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
CREATE TABLE employer
(
  id int4 NOT NULL,
  employer_first_name varchar( 64 ),
  employer_last_name varchar( 64 ),
    CONSTRAINT employer_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE task
(
  id int4 NOT NULL,
  task_description varchar( 2048 ),
  CONSTRAINT task_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;

CREATE TABLE certificates
(
  id int4 NOT NULL,
  name varchar( 128 ),
  CONSTRAINT certificates_pkey PRIMARY KEY (id)
)
WITHOUT OIDS;


CREATE TABLE task_certificates
(
  id int4 NOT NULL,
  certificate_id int4,
  task_id int4,
--level int4,        
  CONSTRAINT task_certificates_pkey PRIMARY KEY (id)
) 
WITHOUT OIDS;

CREATE TABLE employer_certificates
(
  id int4 NOT NULL,
  certificate_id int4,
  employer_id int4,
--level int4,        
  CONSTRAINT employer_certificates_pkey PRIMARY KEY (id)
) 
WITHOUT OIDS;

Задача немного усложняется тем, что сертификаты имеют уровни. Уровень у человека должен быть больше, чем у задачи. НО можно решить и без этого... дальше сам дойду :)
...
Рейтинг: 0 / 0
09.08.2007, 17:06
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34717852
VoDA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VoDA
Участник
softwarer VoDAКак сделать пока не понял :(
Если тупо в лоб: "количество сертификатов, связывающих пользователя с задачей, равняется количеству сертификатов в задаче".
Молодец :)

Я сам так же сделал... но сам запрос получается уж очень не эстетичным что-ли, потому спрашиваю помощи всемогущего All ;)
...
Рейтинг: 0 / 0
09.08.2007, 17:09
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34717922
Фотография softwarer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
softwarer
Участник
VoDAМолодец :)
Давай пирожок.

VoDAЯ сам так же сделал... но сам запрос получается уж очень не эстетичным что-ли
Задумался, как написать его так, чтобы получилось неэстетично.
...
Рейтинг: 0 / 0
09.08.2007, 17:29
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34718086
belugin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
belugin
Участник
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
select * from employee where
    not exists (
            select * from task_certificates where 
                  task_certificates.task_id == ? 
                  and
                  not exists (
                        select * from employee_certeficates where
                            employee_certeficates.employee_id = employee.employee_id                                            
                            and
                            employee_certeficates.certificate_id = task_certificates.certifivcate_id
                               
                  )                                                                                  
    )

employer это работодатель, а не работник

А на количество их нельзя сравнивать - т.к. они не взаимозаменяемые
...
Рейтинг: 0 / 0
09.08.2007, 18:03
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34718094
Фотография softwarer
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
softwarer
Участник
beluginА на количество их нельзя сравнивать - т.к. они не взаимозаменяемые
Поподробнее, пожалуйста.

Если сильно хотите, согласен уточнить формулировку до "количество уникальных сертификатов".
...
Рейтинг: 0 / 0
09.08.2007, 18:05
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34718612
belugin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
belugin
Участник
eЕсли сильно хотите, согласен уточнить формулировку до "количество уникальных сертификатов".

допустим для рещения задачи нужен человек, имеющий хотя бы 1 сертификат по MS SQL Server
в наличии имеется Иванов у которого 1 сертификат по макраме - подойдет ли он для этой задачи?
...
Рейтинг: 0 / 0
10.08.2007, 00:33
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34719092
Фотография Сергей Васкецов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сергей Васкецов
Участник
VoDAPostgreSQL
С этим не работал.

Во-первых, в Вашем случае стоило бы наложить уникальность по паре идентификатров в таблицах связи.

Во-вторых, случай, что у задачи нет сертификатов, сделаете сами.

В-третьих:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
declare @task_id int4 -- task identifier

вариант  1 .

select distinct ec.employer_id
  from employer_certificates ec
 where exists(select  1 
                from task_certificates tc
               where tc.task_id=@task_id
                 and tc.certificate_id=ec.certificate_id)
   and not exists(select  1 
                    from task_certificates tc
                   where tc.task_id=@task_id
                     and not exists(select  1 
                                      from employer_certificates ec2
                                     where ec2.employer_id=ec.employer_id
                                       and ec2.certificate_id=tc.certificate_id))

вариант  2 .

select e.id
  from employer e
 where (select count( 1 )
          from employer_certificates ec,task_certificates tc
         where ec.employer_id=e.id
           and tc.certificate_id=ec.certificate_id
           and tc.task_id=@task_id)
       =
       (select count(distinct tc.certificate_id)
          from task_certificates tc
         where tc.task_id=@task_id)

Сильно ошибиться в синтаксисе не должен был.

Вариант 1 по ряду причин мне кажется предпочительнее, работать он должен быстрее.
Но в варианте 2 можно предрассчитать последний агрегат.
Там же под count специально сделал 2 разных аргумента, чтобы Вы обратили на это внимание, что стоит сделать "во-первых".
В варианте 1 exists нужен для облегчения выполнения запроса, чтобы куда более сложный второй подзапрос лишний раз не гонять, такой же exists можно добавить и во 2-й вариант.
В варианте 1 можно избавиться от distinct и заменить employer_certificates ec на employer e, здесь просто показана логика, что если у одного человека в среднем не сильно много сертификатов и людей много, то получится выигрыш в исключении employer e. Тем не менее, вариант 3 без distinct:

Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
вариант  3 .

select e.id
  from employer e
 where exists(select  1 
                from task_certificates tc,employer_certificates ec
               where tc.task_id=@task_id
                 and tc.certificate_id=ec.certificate_id
                 and ec.employer_id=e.id)
   and not exists(select  1 
                    from task_certificates tc
                   where tc.task_id=@task_id
                     and not exists(select  1 
                                      from employer_certificates ec
                                     where ec.employer_id=e.id
                                       and ec.certificate_id=tc.certificate_id))

3-й вариант вроде бы можно переписать без самого внутреннего not exists через join, попробуйте сами это сделать.
...
Рейтинг: 0 / 0
10.08.2007, 10:52
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34719130
VoDA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VoDA
Участник
beluginemployer это работодатель, а не работник

А на количество их нельзя сравнивать - т.к. они не взаимозаменяемые

а employee - это работник, а не работодатель )))
...
Рейтинг: 0 / 0
10.08.2007, 11:01
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34719134
belugin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
belugin
Участник
Сергей Васкецов
where exists(select 1


авторесли у задачи нет сертификатов - ее может выполнить любой
...
Рейтинг: 0 / 0
10.08.2007, 11:02
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34719136
Фотография Сергей Васкецов
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Сергей Васкецов
Участник
belugin
Читаем еще раз 1-й пост и мое сообщение целиком и внимательно.
...
Рейтинг: 0 / 0
10.08.2007, 11:03
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34719192
belugin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
belugin
Участник
Сергей Васкецов belugin
Читаем еще раз 1-й пост и мое сообщение целиком и внимательно.

Извиняюсь, пропустил...
...
Рейтинг: 0 / 0
10.08.2007, 11:17
| Ответить | Цитировать | Написать  
Вопрос по сложной выборке
    #34731937
VoDA
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
VoDA
Участник
Спасибо всем, кто принял участие.

Было решено сделать на уровне апп-сервера - через работу со списками и их пересечением.
Критериев почему именно так - много :)

сам SQL (который был получен, но не используется сейчас):
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
select employee_query.employee_id, employee_query.task_id, res_cert_count
from
(
select employee_id, task_id, count(certificate_id) as res_cert_count
from
(select distinct r.id as employee_id, rc.certificate_id, sc.task_id from employees r
left join employer_certificates rc on r.id=rc.employer_id
left join task_certificates sc on rc.certificate_id = sc.certificate_id
left join task s on s.id = sc.task_id
) as a
group by employee_id, task_id
) as employee_query

inner join
(
select task_id, count(certificate_id) as sub_cert_count
from
(select distinct s.id as task_id, sc.certificate_id from task s
left join task_certificates sc on s.id = sc.task_id) as asd
group by task_id
) as task_query

on employee_query.task_id = task_query.task_id and res_cert_count = sub_cert_count;
...
Рейтинг: 0 / 0
16.08.2007, 12:14
| Ответить | Цитировать | Написать  
16 сообщений из 16, страница 1 из 1
Форумы / Проектирование БД [игнор отключен] [закрыт для гостей] / Вопрос по сложной выборке
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=32&msg=34717558&tid=1544350]:
 0ms
get settings:
 8ms
get forum list:
 15ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 223ms
get topic data:
 8ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 206ms
total:  519ms
созд. / загр.: 519ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]