問題描述

我有以下查詢，其結果是員工所說的語言及其相應級別:

I have the following query which gets as result the language spoken by an employee and his corresponding level :

SELECT E.EmployeeId
        ,ISNULL(L.ID ,0) AS LanguageId
        ,L.Label AS Language,
        ll.Label AS LanguageLevel
         FROM Employee e
LEFT JOIN AF_AdminFile aaf ON e.AdminFileId = aaf.AdminFileId
LEFT JOIN AF_Language al ON aaf.AdminFileId = al.AdminFileId
LEFT JOIN Language l ON al.LanguageId = l.ID
LEFT JOIN LanguageLevel ll ON al.LanguageLevelId = ll.LanguageLevelId
ORDER BY e.EmployeeId

結果如下:

對于 EmployeeId=6 的員工，他說英語/流利，西班牙語/好，法語/母語.
知道我的表 Language 中有 187 種不同的語言，我的表 LanguageLevel 中有 4 個語言級別(公平，流利，好，母語)

For the Employee with EmployeeId=6,he speaks English/Fluent, Spanish/Good,French/Mother Tongue.
Knowing that I have 187 different languages in my table Language and 4 language levels in my table LanguageLevel (Fair,Fluent,Good,Mother Tongue)

我只想獲得母語和流利的語言，如下所示:

I want to get only the MotherTongue and the Fluent language like below :

EmployeeId MotherTongue        Fluent
6          French              English

推薦答案

當前查詢的輸出遵循非規范化鍵值存儲的模式.在這種情況下，鍵是語言級別，值是語言.處理此問題的一種方法是按員工匯總，然后使用透視邏輯來獲取您想要的語言.

The output from your current query follows the pattern of an unnormalized key value store. In this case, the keys are the language levels, and the values the languages. One way to handle this is to aggregate by employee and then use pivoting logic to obtains the languages you want.

SELECT
    e.EmployeeId,
    MAX(CASE WHEN ll.label = 'Mother Tongue' THEN l.label END) AS MotherTongue,
    MAX(CASE WHEN ll.label = 'Fluent' THEN l.label END) AS Fluent
FROM Employee e
LEFT JOIN AF_AdminFile aaf
    ON e.AdminFileId = aaf.AdminFileId
LEFT JOIN AF_Language al
    ON aaf.AdminFileId = al.AdminFileId
LEFT JOIN Language l
   ON al.LanguageId = l.ID
LEFT JOIN LanguageLevel ll
   ON al.LanguageLevelId = ll.LanguageLevelId
GROUP BY
    e.EmployeeId
ORDER BY
    e.EmployeeId;

這篇關于獲得員工的母語和流利的語言的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！