Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите пожалуйста с запросом!  [new]
elena_78
Member

Откуда:
Сообщений: 21
Помогите с запросом, а то у меня мозг взорвался!
Есть 3 таблицы department, staffposition и employee. employee связана с department через staffposition. В таблице department есть поля recID, name, mainID, validStatus, ismain. Ну и еще несколько, они нас не интересуют. Нужно сделать запрос сколько employee-ров работают в каждом подразделении с определенным validStatus-ом и ismain-ом. НО! В department есть определенная структура. Т.е. например, есть факультеты, есть адмитнистративно хозяйственный отдел, есть научно-исследовательская часть и т.д. К факультетам относятся названия факультетов, дальше названия кафедр и т.д. У каждого есть свой recID. К научно-исследовальтельской части относиться Отделы разработки, институты исследований, лаболатории и т.д. Т.е. существует определенная иерархия вложенности. У вложенности есть уровни. Начинается все с нулевого уровня. Т.е. если нам нужны люди из научно-исследовательского отдела, то это будет нулевой уровень. Если люди из всех лаболаторий научно-исследовательской части, то это первый ровень и т.д. Т.е. структура как папки лежат в Windows. Эту вложенность можно отследить по mainID. Т.е. у нулевого уровня научно-исследовательской части recID будет например 100203, а mainID будет самой структуры предприятия, т.е. 100001, дальше институты, лаболатории и т.д. научно-исследовательской части(НИЧ) будут иметь свои recID, но mainID будет 100203, отделения раболаторий, на первом уровне будет иметь recID 100655, a mainID 100203, а все кафедры которые туда попадают будут именть разные recID, но mainID 100655 и т.д.
Так вот мне нужно посчитать людей в иерархии НИЧ до третьего уровня. Если считать людей, относящихся к каждой структуре вплоть до 5-го уровня, то ничего сложного нет, считает правильно, а вот как их сложить всех до третьего уровня - никак не могу придумать!
Мой код считает неверно!

select  sum(
case when e_g.ismain=1 and d.mainID=g.ID then 1 
when e_gn.ismain=1 and d.mainID=g_n.id then 1
when e_i.ismain=1 and d.mainID=g_i.id then 1
when e_l.ismain=1 and d.mainID=g_l.id then 1
when e_k.ismain=1 and d.mainID=g_k.id then 1
 
else 0 end ) as 'количество человек',

d.name 'имя подразделения', g.id, d.mainID, d.recID

from department d
left join TreeDepartment (100203) g ON d.mainID=g.id
left join TreeDepartment (100542) g_n ON d.mainID=g_n.id
left join TreeDepartment (100655) g_i ON d.mainID=g_i.id
left join TreeDepartment (100705) g_l ON d.mainID=g_l.id
left join TreeDepartment (100206) g_k ON d.mainID=g_k.id
left outer join staffPosition sp_g on sp_g.departmentID=g.ID
left outer join staffPosition sp_gn on sp_gn.departmentID=g_n.ID
left outer join staffPosition sp_gi on sp_gi.departmentID=g_i.ID
left outer join staffPosition sp_gl on sp_gl.departmentID=g_l.ID
left outer join staffPosition sp_gk on sp_gk.departmentID=g_k.ID
left outer join employee e_g on e_g.staffPositionId=sp_g.recid
left outer join employee e_gn on e_gn.staffPositionID=sp_gn.recID
left outer join employee e_i on e_i.staffPositionId=sp_gi.recid
left outer join employee e_l on e_l.staffPositionId=sp_gl.recid
left outer join employee e_k on e_k.staffPositionId=sp_gk.recid
where d.validStatusID in (1,3,6,7,8,100388,102301)
and d.recID in (100203,100656,100224,100357,100211,100204,100205,100225,
100598,100597,100443,100228,100673,100394,100213,100596,100442,100222,100544,
100565,100567,100532,100670,100734,100568,100570,100541,100562,100560,100566,
100563,100666,100665,100717,100718,100546,100719,100669,100540,100529,100527,
100564,100667,100720,100671,100668,100569,100539,100716,100533,100512,100215,
100217,100521,100220,100213,100725,100227,100534,100691,100417,100491,100221,
100218,100542,100207,100543,100705,100545,100726)  
group by d.name, d.recID, g.id, d.mainID
order by d.recID
15 окт 09, 15:39    [7791877]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
elena_78
Member

Откуда:
Сообщений: 21
Может я написала непонятно? 29 просмотров и ни одного ответа :(
15 окт 09, 17:43    [7793066]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
ТАРАКАН
Member

Откуда:
Сообщений: 439
elena_78
Может я написала непонятно? 29 просмотров и ни одного ответа :(

я думаю из-за того что нет скриптов создания таблиц и заполнение тестовыми данными...
15 окт 09, 17:46    [7793093]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
elena_78,
Вы читали рекомендации к оформлению сообщений?

Привидите DDL таблиц, тестовыпе данные(скрипт, заполняющий эти таблицы)
и требуемый результат.
15 окт 09, 17:50    [7793125]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
aleks2
Guest
1. Вам станет проще, если вы отделите мух от котлет.
2. Сначала разберитесь с иерархией и выведите список ID подразделений - хде считать.
3. А потом - все просто.
15 окт 09, 17:57    [7793201]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
aleks2
Guest
Кстати, вот такую хрень
d.validStatusID in (1,3,6,7,8,100388,102301)
лучше заменить INNER JOIN с таблицей
1,
3,
6,
7,
...
Быстрее будет.
15 окт 09, 18:04    [7793257]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
elena_78
Member

Откуда:
Сообщений: 21
aleks2, ID ВСЕХ подразделенийвот здесь

d.recID in (100203,100656,100224,100357,100211,100204,100205,100225,
100598,100597,100443,100228,100673,100394,100213,100596,100442,100222,100544,
100565,100567,100532,100670,100734,100568,100570,100541,100562,100560,100566,
100563,100666,100665,100717,100718,100546,100719,100669,100540,100529,100527,
100564,100667,100720,100671,100668,100569,100539,100716,100533,100512,100215,
100217,100521,100220,100213,100725,100227,100534,100691,100417,100491,100221,
100218,100542,100207,100543,100705,100545,100726)  

но из этого спика не понятно иерархия, она ясна только из mainID.
Поэтому я и не понимаю как вложенные объекты посчитать.
15 окт 09, 18:55    [7793483]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
iljy
Member

Откуда:
Сообщений: 8711
elena_78,

иерархия из mainID тоже не ясна, поскольку нет структуры таблиц. Приведите таблицы и связи. Ну и неплохо бы скрипт с небольшим количеством тестовых данных
15 окт 09, 19:00    [7793500]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
elena_78
Member

Откуда:
Сообщений: 21
iljy, Структура понятно только при команде

select * from TreeDepartment (100001)

Завтра выложу текстовые данные, сейчас нет коннекта к базе. Спасибо за внимание к моему вопросу!
15 окт 09, 19:09    [7793524]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
elena_78
Member

Откуда:
Сообщений: 21
Скрипт на заполнение таблицы department данными

INSERT INTO [Department]([recID], [name], [abbreviation], [description], [mainID], [validstatusid])
VALUES(<recID,int,>, <name,varchar(400),>, <abbreviation,varchar(30),>, <description,varchar(5000),>, <mainID,int,>, <validstatusid,int,>)

текстовые данные из department с указанием зависимостей. Частично.

select * from  TreeDepartment (100001)

id parent_id level name
100001 NULL 0
100100 100001 1 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100
100177 100001 1 ::АДМИНИСТРАТИВНО-УПРАВЛЕНЧЕСКИЕ ПОДРАЗДЕЛЕНИЯ100177
100203 100001 1 ::НАУЧНО-ИССЛЕДОВАТЕЛЬСКАЯ ЧАСТЬ100203
100231 100001 1 ::ПОДРАЗДЕЛЕНИЯ ОБСЛУЖИВАНИЯ100231
100243 100001 1 ::АДМИНИСТРАТИВНО-ХОЗЯЙСТВЕННОЕ УПРАВЛЕНИЕ100243
100253 100001 1 ::ПОДРАЗДЕЛЕНИЯ СОЦИАЛЬНОЙ СФЕРЫ100253
100265 100001 1 ::ФИЛИАЛ УНИВЕРСИТЕТА В Г. СЫЗРАНИ100265
100307 100001 1 ::ПРЕДСТАВИТЕЛЬСТВА УНИВЕРСИТЕТА100307
100317 100001 1 ::ОТДЕЛЕНИЯ КАФЕДР УНИВЕРСИТЕТА и УЧЕБНЫЕ ЦЕНТРЫ УНИВЕРСИТЕТА100317
100336 100001 1 ::БАЗОВЫЕ КАФЕДРЫ УНИВЕРСИТЕТА100336
100378 100001 1 ::ДРУГИЕ ПОДРАЗДЕЛЕНИЯ100378
100618 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::факультет дополнительного профессионального образования 100618
100115 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::факультет довузовской подготовки (ФДП)100115
100116 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::факультет повышения квалификации преподавателей (ФПКП)100116
100120 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::факультет гуманитарного образования (ФГО)100120
100102 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::факультет машиностроения и автомобильного транспорта (ФМиАТ)100102
100103 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Физико-технологический факультет (ФТФ)100103
100104 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::факультет пищевых производств (ФПП)100104
100105 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Нефтетехнологический факультет (НТФ)100105
100106 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Химико-технологический факультет (ХТФ)100106
100107 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::факультет автоматики и информационных технологий (ФАИТ)100107
100108 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Инженерно-технологический факультет (ИТФ)100108
100109 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Инженерно-экономический факультет (ИЭФ)100109
100110 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Теплоэнергетический факультет (ТЭФ)100110
100111 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Электротехнический факультет (ЭТФ)100111
100112 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Заочный политехнический институт (ЗПИ)100112
100128 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Военная кафедра100128
100703 100100 2 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Центр довузовской подготовки100703
100432 100177 2 ::АДМИНИСТРАТИВНО-УПРАВЛЕНЧЕСКИЕ ПОДРАЗДЕЛЕНИЯ100177::Отдел промышленной безопасности и производственного контроля100432
100434 100177 2 ::АДМИНИСТРАТИВНО-УПРАВЛЕНЧЕСКИЕ ПОДРАЗДЕЛЕНИЯ100177::Отдел по организации закупок100434
100178 100177 2 ::АДМИНИСТРАТИВНО-УПРАВЛЕНЧЕСКИЕ ПОДРАЗДЕЛЕНИЯ100177::Ректорат100178
100179 100177 2 ::АДМИНИСТРАТИВНО-УПРАВЛЕНЧЕСКИЕ ПОДРАЗДЕЛЕНИЯ100177::Учебное управление100179
100186 100177 2 ::АДМИНИСТРАТИВНО-УПРАВЛЕНЧЕСКИЕ ПОДРАЗДЕЛЕНИЯ100177::Планово-производственный отдел100186
100503 100103 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Физико-технологический факультет (ФТФ)100103::Информационный центр100503
100733 100103 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Физико-технологический факультет (ФТФ)100103::кафедра"Литейные и высокоэффективные технологии"100733
100118 100103 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Физико-технологический факультет (ФТФ)100103::кафедра "Технологии литейных процессов"100118
100143 100103 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Физико-технологический факультет (ФТФ)100103::кафедра "Металловедение, порошковая металлургия, наноматериалы"100143
100130 100103 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Физико-технологический факультет (ФТФ)100103::кафедра "Безопасность жизнедеятельности"100130
100362 100103 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Физико-технологический факультет (ФТФ)100103::Деканат ФТФ100362
100398 100103 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Физико-технологический факультет (ФТФ)100103::кафедра "Материаловедение и технология материалов"100398
100363 100104 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::факультет пищевых производств (ФПП)100104::Деканат ФПП100363
100315 100104 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::факультет пищевых производств (ФПП)100104::кафедра "Технология пищевых производств и парфюмерно-косметических продуктов"100315
100306 100105 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Нефтетехнологический факультет (НТФ)100105::кафедра "Химическая технология и промышленная экология"100306
100364 100105 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Нефтетехнологический факультет (НТФ)100105::Деканат НТФ100364
100349 100105 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Нефтетехнологический факультет (НТФ)100105::базовая кафедра "Переработки нефти и нефтепродуктов" (при ОАО "Сызранский нефтеперера¬батывающий завод", г. Сызрань)100349
100413 100105 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Нефтетехнологический факультет (НТФ)100105::Геолого-минералогический музей НТФ100413
100119 100105 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Нефтетехнологический факультет (НТФ)100105::кафедра "Геология и геофизика"100119
100162 100105 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Нефтетехнологический факультет (НТФ)100105::кафедра "Бурения нефтяных и газовых скважин"100162
100163 100105 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Нефтетехнологический факультет (НТФ)100105::кафедра "Разработка и эксплуатация нефтяных и газовых месторождений"100163
100164 100105 3 ::ИНСТИТУТЫ И ФАКУЛЬТЕТЫ УНИВЕРСИТЕТА100100::Нефтетехнологический факультет (НТФ)100105::кафедра "Машины и оборудование нефтяных и газовых промыслов"100164

нас интересует НИЧ, т.е.

select * from TreeDepartment (100001)
id parent_id level name
100203 NULL 0
100656 100203 1 ::Отдел метрологии100656
100655 100203 1 ::Управление научных исследований100655
100542 100203 1 ::Управление послевузовского профессионального образования и студенческой науки СамГТУ100542
100705 100203 1 ::Управление программ и проектов100705
100224 100203 1 ::Отдел финансового сопровождения научно-исследовательских работ100224
100543 100542 2 ::Управление послевузовского профессионального образования и студенческой науки СамГТУ100542::Сектор студенческой науки100543
100207 100542 2 ::Управление послевузовского профессионального образования и студенческой науки СамГТУ100542::Отдел аспирантуры и докторантуры100207
100357 100655 2 ::Управление научных исследований100655::Институты100357
100359 100655 2 ::Управление научных исследований100655::Лаборатории100359
100360 100655 2 ::Управление научных исследований100655::Центры100360
100206 100655 2 ::Управление научных исследований100655::Научно-исследовательские секторы кафедр100206
100726 100705 2 ::Управление программ и проектов100705::Отдел научно-технической информации100726
100545 100705 2 ::Управление программ и проектов100705::Инновационный центр трансфера технологий100545
100546 100206 3 ::Управление научных исследований100655::Научно-исследовательские секторы кафедр100206::НИС кафедры информационных технологий100546
100560 100206 3 ::Управление научных исследований100655::Научно-исследовательские секторы кафедр100206::НИС кафедры Прикладной математики и информатики100560
100665 100206 3 ::Управление научных исследований100655::Научно-исследовательские секторы кафедр100206::НИС кафедры Электоснабжение Промпредприятий100665
100666 100206 3 ::Управление научных исследований100655::Научно-исследовательские секторы кафедр100206::НИС кафедры Технология твёрдых химических веществ100666
16 окт 09, 14:56    [7797373]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
iljy
Member

Откуда:
Сообщений: 8711
elena_78,

вы скрипты сами выполнить пробовали? Особенно первый.
16 окт 09, 14:59    [7797399]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
elena_78
Member

Откуда:
Сообщений: 21
iljy, нет, не пробовала. У меня уже имеется рабочая таблица с данными. Может быть я не совсем понимаю в чем подвох, но это играет какую-то особенную роль?
16 окт 09, 15:42    [7797784]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
iljy
Member

Откуда:
Сообщений: 8711
elena_78,

играет. они синтаксически неверны и не несут никакой смысловой нагрузки.
16 окт 09, 15:45    [7797809]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
elena_78
Member

Откуда:
Сообщений: 21
iljy, вы скажите что нужно показать, чтобы было более монятна задача, я это сделаю.
16 окт 09, 15:58    [7797906]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
iljy
Member

Откуда:
Сообщений: 8711
elena_78,

Рекомендации по оформлению сообщений в форуме, пункт 6, "пример хорошего стиля".
16 окт 09, 16:02    [7797934]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
elena_78
Скрипт на заполнение таблицы department данными

INSERT INTO [Department]([recID], [name], [abbreviation], [description], [mainID], [validstatusid])
VALUES(<recID,int,>, <name,varchar(400),>, <abbreviation,varchar(30),>, <description,varchar(5000),>, <mainID,int,>, <validstatusid,int,>)
Это не скрипт, это шаблон (template) скрипта.
В SQL Management Studio выделите его в редакторе, нажмите <Ctrl>+<Shift>+<M>, заполните поля, вот после этого полýчите скрипт.
Но это будет скрипт на вставку только одной записи в таблицу [Department]
16 окт 09, 16:03    [7797943]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
elena_78
Member

Откуда:
Сообщений: 21
iap, да это я понимаю. Я просто не понимаю зачем он нужен в данном случае. Учитывая что задача совсем в другом стоит.
16 окт 09, 16:50    [7798323]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
Konst_One
Member

Откуда:
Сообщений: 11513
вам намекают на то, что тут люди занятые и им самим ваши данные в ваших табличках эммулировать некогда
16 окт 09, 16:51    [7798337]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
Glory
Member

Откуда:
Сообщений: 104760
elena_78
iap, да это я понимаю. Я просто не понимаю зачем он нужен в данном случае. Учитывая что задача совсем в другом стоит.

Т.е. вы предлагаете каждому у себя на сервере
- создать ваши таблицы, по описанию из первого столбца
- вбить в них вручную данные и последующих постов
- придумать, какой результат для них вам нужен
- написать запрос
- и опубликовать его
16 окт 09, 16:56    [7798380]     Ответить | Цитировать Сообщить модератору
 Re: Помогите пожалуйста с запросом!  [new]
elena_78
Member

Откуда:
Сообщений: 21
Glory, спасибо за объяснение! Теперь понятно! Теперь обязательно сделаю все рекомендации!
16 окт 09, 17:39    [7798646]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить