Блог

У меня есть база данных SQL Server, которую я не проектировал. У сотрудников есть степени, лицензии и учетные данные, хранящиеся в нескольких разных таблицах. Я написал запрос, чтобы объединить всю эту информацию вместе, чтобы я мог видеть общий результат того, как выглядят данные. Меня попросили создать представление для этих данных, которое возвращает только самую высокую степень, которую они получили, и два самых высоких сертификата. Проблема в том, что, поскольку это уже существующие данные, в данные не встроена иерархия. Все степени и сертификаты просто хранятся в виде строки, связанной с их номером сотрудника. Первым логическим шагом было создание списка смежности (я считаю, что это правильный термин). Например, ‘MD’ — это наивысшая степень, которую вы можете получить в нашем списке. Итак, я присвоил этому «ранжированию» значение 1. Следующая более низкая степень «оценивается» как 2. и так далее. Я могу присоединиться к текстовому полю, которое содержит эти данные, и вернуть их соответствующий ранг. Проблема, с которой я сталкиваюсь, заключается в возврате только двух самых высоких значений на основе этого рейтинга. Если сотрудник имеет несколько степеней или сертификатов, они перечислены во второй или третьей строке. С логической точки зрения мне нужно сгруппировать идентификатор сотрудника, имя и фамилию. Затем, как объединить степени, сертификаты и лицензии на основе «рейтинга», который я создал для них. Это не настоящая иерархия в том смысле, в каком я об этом думаю, потому что мне нужно знать только два самых высоких и не обязательно взаимосвязь между результатами.

Другим потенциальным предостережением является то, что база данных должна оставаться в режиме совместимости с SQL Server 2000.

Любая помощь, которая может быть предоставлена, была бы высоко оценена. Спасибо.

 select a.EduRank as 'Licensure Rank',
   b.EduRank as 'Degree Rank',
   EmpComp.EecEmpNo, 
  EmpPers.EepNameFirst, 
   EmpPers.EepNameLast, 
   RTRIM(EmpEduc.EfeLevel),
   RTRIM(EmpLicns.ElcLicenseID),
   a.EduType,
   b.EduType
       from empcomp 
      join EmpPers on empcomp.eeceeid = EmpPers.eepEEID
      join EmpEduc on empcomp.Eeceeid = EmpEduc.EfeEEID
      join EmpLicns on empcomp.eeceeid = EmpLicns.ElcEEID
      join yvDegreeRanks a on a.EduCode = EmpLicns.ElcLicenseID
      join yvDegreeRanks b on b.EduCode = EmpEduc.EfeLevel
  

Думаю, я понимаю, в чем ваша проблема, однако я не уверен. Объединение таблиц вместе дало вам «двойные строки». «Быстрым и грязным» способом решения этого запроса было бы использовать подзапросы, отличные от Joins. При этом вы можете выбрать только TOP 1 степень и TOP 2 сертификаты.

РЕДАКТИРОВАТЬ : Можете ли вы попробовать этот запрос?

 SELECT *
FROM employSELECT tblLicensures.EduRank as 'Licensure Rank',
    tblDegrees.EduRank as 'Degree Rank',
    EmpComp.EecEmpNo, 
    EmpPers.EepNameFirst, 
    EmpPers.EepNameLast, 
    RTRIM(tblDegrees.EfeLevel),
    RTRIM(tblLicensures.ElcLicenseID),
    tblLicensures.EduType,
    tblDegrees.EduType
FROM EmpComp
    LEFT OUTER JOIN EmpPers ON empcom.eeceeid = EmpPers.eepEEID
    LEFT OUTER JOIN
        -- Select TOP 2 Licensure Ranks
        (
            SELECT TOP 2 a.EduType, a.EduRank, EmpLicns.ElcEEID
            FROM yvDegreeRanks a 
                INNER JOIN EmpLicns on a.EduCode = EmpLicns.ElcLicenseID
            WHERE EmpLincs.ElcEEID = empcomp.eeceeid
            ORDER BY a.EduRank ASC
        ) AS tblLicensures ON tblLicensures.ElcEEID = empcomp.Eeceeid
    LEFT OUTER JOIN 
        -- SELECT TOP 1 Degree
        (
            SELECT TOP 1 b.EduType, b.EduRank, EmpEduc.EfeEEID, EmpEduc.EfeLevel
            FROM yvDegreeRanks b 
                INNER JOIN EmpEduc on b.EduCode = EmpEduc.EfeLevel
            WHERE EmpEduc.EfeEEID = empcomp.Eeceeid
            ORDER BY b.EduRank ASC
        ) AS tblDegrees ON tblDegrees.EfeEEID = empcomp.Eeceeid  

Это не самое элегантное решение, но, надеюсь, оно хотя бы каким-то образом поможет вам.

 create table #dataset (
licensurerank [datatype],
degreerank [datatype],
employeeid [datatype],
firstname varchar,
lastname varchar,
efeLevel  [datatype],
elclicenseid [datatype],
edutype1 [datatype],
edutype2 [datatype]
)

select distinct identity(int,1,1) [ID], EecEmpNo into #employeeList from EmpComp

declare
    @count int,
    @rows int,
    @employeeNo int

select * from #employeeList
set @rows = @@rowcount
set @count = 1

while @count <= @ROWS
    begin

select @employeeNo = EecEmpNo from #employeeList where id = @count

        insert into #dataset
        select top 2 a.EduRank as 'Licensure Rank',
           b.EduRank as 'Degree Rank',
           EmpComp.EecEmpNo, 
          EmpPers.EepNameFirst, 
           EmpPers.EepNameLast, 
           RTRIM(EmpEduc.EfeLevel),
           RTRIM(EmpLicns.ElcLicenseID),
           a.EduType,
           b.EduType
               from empcomp 
              join EmpPers on empcomp.eeceeid = EmpPers.eepEEID
              join EmpEduc on empcomp.Eeceeid = EmpEduc.EfeEEID
              join EmpLicns on empcomp.eeceeid = EmpLicns.ElcEEID
              join yvDegreeRanks a on a.EduCode = EmpLicns.ElcLicenseID
              join yvDegreeRanks b on b.EduCode = EmpEduc.EfeLevel
            where EmpComp.EecEmpNo = @employeeNo

    set @count = @count   1
end
  

Создайте таблицы для сотрудников, типов степеней (включая ранг), типов сертификатов (включая ранг) и объедините таблицы employees_degrees и employees_certs. [Возможно, было бы лучше поместить степени и сертификаты в одну таблицу с флагом is_degree, если все остальные их поля одинаковы.] Вы можете извлечь существующие строковые значения и заменить их идентификаторами FK в таблицах degree и cert.

Сам запрос сложнее, потому что PARTITION BY недоступен в SQL Server 2000 (согласно Google). В ответе UW есть по крайней мере две проблемы: вам нужны LEFT JOIN s, потому что не у всех сотрудников есть степени и сертификаты, и нет ORDER BY того, что вы хотите использовать наилучшим образом из. TOP 2 подзапросы особенно сложны в использовании в этом контексте. Итак, на этот вопрос я пока не могу дать ответ.