mysql - 2 来自同一张表的嵌套查询？

Question

所以我有3张桌子

用户表

UserId User

语言表

LanguageId Language Fluency

用户语言表

UserLanguageId UserId LanguageId

基本上我需要的是一个查询，其中 PriLang 是 Fl​​uency='Primary' 而 SecLang 是 Fl​​uency='Secondary' 并且看起来像这样

+------------+-----------------+-----------+
| User       | PriLang         | SecLang   |
+------------+-----------------+-----------+
| Jimbo      | English         | Spanish   |
+------------+-----------------+-----------+
| Norm       | French          | Spanish   |
+------------+-----------------+-----------+
| Kathy      | Japanese        | Italian   |
+------------+-----------------+-----------+

标签： mysqlsqljoinpivot

Answer 1

在我看来，最直接的方法涉及到 user_languages 表的两个连接，以及每个连接到 langauges 表的连接。

select  usr.User, lg1.Language PriLang, lg2.Language SecLang 
from users usr
 left join user_languages ul1
  on ul1.userId = usr.userId
   and ul1.Fluency='Primary'
 left join user_languages ul2
  on ul2.userId = usr.userId
   and ul2.Fluency='Secondary'
 left join languages lg1
  on lg1.languageId = ul1.languageId
 left join languages lg2
  on lg2.languageId = ul2.languageId