В MSSQL есть таблица careerreserves с полями id, code, name, status, personid,…
- User
В MSSQL есть таблица career_reserves с полями id, code, name, status, person_id, person_fullname, person_position, start_date, plan_readiness_date, finish_date, readiness_percent, personnel_reserve_id, position_type, position_name, position_id, position_common_id, subdivision_id, user_access_role, user_group_id, modification_date, app_instance_id, role_id и таблица career_reserve с полями: id, data, которое представляет собой xml-структуру:
<career_reserve SPXML-FORM="x-local://wtv/wtv_career_reserve.xmd"> 0x6223C446EF297A49 tvm5amzsa2cz Практикум с наставником на рабочем месте task passed <parent_task_id>5b02evf4qmjv</parent_task_id> <start_date>2024-09-18T14:00:00+00:00</start_date> <plan_date>2024-09-18T18:00:00+00:00</plan_date> <typical_development_program_id>0x621329F5751E631C</typical_development_program_id> <typical_development_program_task_id>tvm5am</typical_development_program_task_id> <object_type>task_type</object_type> <object_id>0x61DD2CDA3EAB67C0</object_id> <type_document>portal_doc</type_document> <link_document>http://</link_document> <task_id>0x6223C4737BDFD554</task_id> <forbid_task_portal_edit>0</forbid_task_portal_edit> <custom_elems> <custom_elem> coll_file </custom_elem> </custom_elems> 26fucza082fo День 3. Ресурсы для обеспечения сельхозпроизводства stage passed <start_date>2024-09-18T00:00:00+00:00</start_date> <plan_date>2024-10-04T00:00:00+00:00</plan_date> <fact_date>2024-09-23T00:00:00+00:00</fact_date> <typical_development_program_id>0x621329F5751E631C</typical_development_program_id> <typical_development_program_task_id>26fucz</typical_development_program_task_id> <object_type>stage</object_type> <type_document>portal_doc</type_document> <link_document>http://</link_document> <forbid_task_portal_edit>0</forbid_task_portal_edit> <custom_elems> <custom_elem> coll_file </custom_elem> </custom_elems> <forbid_tasks_edit>0</forbid_tasks_edit>
помоги создать отчёт с полями уникальных person_id из всех <person_id> шаги должны быть следующие:
- отбираем из таблицы career_reserves все, у которых position_type = 'adaptation'
- сопоставляем таблицы career_reserves и career_reserve по career_reserves.id = career_reserve.id
- в полученных в результате career_reserve из xml-структуры в поле data ищем присутствие task и <object_type>task_type</object_type> и <object_id>7051841907397978048</object_id> и Практикум с наставником на рабочем месте
- выводим career_reserves.id или career_reserve.id - что проще
- Assistant
Чтобы создать требуемый отчёт, можно воспользоваться следующим SQL-запросом в MSSQL. Этот запрос выполняет все описанные шаги:
- Фильтрация записей по
position_type = 'adaptation'в таблицеcareer_reserves. - Сопоставление таблиц
career_reservesиcareer_reserveпо полюid. - Поиск в XML-структуре (
data) наличия определённых элементов и значений с использованием XQuery. - Вывод уникальных
person_idудовлетворяющих условиям.
Вот полный запрос:
SELECT DISTINCT crs.person_id FROM career_reserves crs JOIN career_reserve cr ON crs.id = cr.id WHERE crs.position_type = 'adaptation' AND cr.data.exist('/career_reserve/task/task[ type = "task" AND object_type = "task_type" AND object_id = "7051841907397978048" AND name = "Практикум с наставником на рабочем месте" ]') = 1;Пояснение к запросу:
-
SELECT DISTINCT crs.person_id:- Выбирает уникальные значения
person_idиз таблицыcareer_reserves.
- Выбирает уникальные значения
-
FROM career_reserves crs JOIN career_reserve cr ON crs.id = cr.id:- Выполняет внутреннее соединение (JOIN) между таблицами
career_reservesиcareer_reserveпо полюid.
- Выполняет внутреннее соединение (JOIN) между таблицами
-
WHERE crs.position_type = 'adaptation':- Фильтрует записи в таблице
career_reserves, оставляя только те, у которыхposition_typeравен'adaptation'.
- Фильтрует записи в таблице
-
AND cr.data.exist(...) = 1:- Использует метод
existдля проверки наличия в XML-структуре (data) нужных элементов и значений. - Внутри
existвыполняется XQuery, который ищет любой<task>внутри<career_reserve>-><task>, удовлетворяющий следующим условиям:<type>равно"task".<object_type>равно"task_type".<object_id>равно"7051841907397978048".<name>равно"Практикум с наставником на рабочем месте".
- Использует метод
Если требуется вывести вместо
person_idполеidизcareer_reservesилиcareer_reserve, можно изменить частьSELECTследующим образом:-
Для
career_reserves.id:SELECT DISTINCT crs.id AS CareerReserveID, crs.person_id -
Для
career_reserve.id:SELECT DISTINCT cr.id AS CareerReserve_XML_ID, crs.person_id
Примечания:
-
Убедитесь, что XML-структура в поле
dataсоответствует указанным путям. В приведённом примере<career_reserve>содержит вложенные элементы<task>. Если структура отличается, необходимо скорректировать путь в XQuery соответственно. -
Оптимизация производительности:
- При больших объёмах данных рассмотрите возможность создания индексов на поле
idв обеих таблицах. - Также можно использовать
CROSS APPLYс методами XML для более сложных запросов или улучшения читаемости.
- При больших объёмах данных рассмотрите возможность создания индексов на поле
Альтернативный вариант с использованием
CROSS APPLY:Если требуется более детальная работа с XML-данными, можно воспользоваться
CROSS APPLYдля разбора XML:SELECT DISTINCT crs.person_id FROM career_reserves crs JOIN career_reserve cr ON crs.id = cr.id CROSS APPLY cr.data.nodes('/career_reserve/task/task') AS T(Task) WHERE crs.position_type = 'adaptation' AND T.Task.value('(type)[1]', 'NVARCHAR(50)') = 'task' AND T.Task.value('(object_type)[1]', 'NVARCHAR(50)') = 'task_type' AND T.Task.value('(object_id)[1]', 'NVARCHAR(50)') = '7051841907397978048' AND T.Task.value('(name)[1]', 'NVARCHAR(255)') = 'Практикум с наставником на рабочем месте';Этот вариант может быть полезен, если требуется более гибкая обработка или извлечение дополнительных данных из XML.
Если у вас возникнут дополнительные вопросы или потребуется уточнение запроса под конкретные особенности вашей базы данных, не стесняйтесь обращаться!
- Фильтрация записей по