MySQL连接表

Question

我对连接表格还不熟悉。

ClassList
========================
ID      Level
1       2
2       2
3       4
4       1
5       3
6       7

BoysList
========================
ID      Name        Age
1       Alex        13
2       Arnold      16
3       Barny       15

GirlsList
========================
ID      Name        Age
4       Arnie       12
5       Martha      17
6       Beth        15

我能够连接两个表格。但是，在这种情况下，如果您必须先合并两个表（BoysList和GirlsList），然后再将其连接到ClassList，该怎么办？

结果集将是：

Result
================================================
ID      Name
4       Arnie           
6       Beth        
5       Martha      
1       Alex    
2       Arnold  
3       Barny       

结果的顺序应该是男孩女孩列表，名字按升序排序。如何实现这个？

- Marlon Gracey

Answer 1

你需要做的第一件事是将 BoysList 和 GirlsList 进行 UNION，然后再与 ClassList 进行 INNER JOIN。

SELECT  iStudentList.`ID`,
    iStudentList.`Name`,
    iStudentList.`Age`,
    ClassList.`iOrder`
FROM
ClassList INNER JOIN 
    (SELECT `ID`, `Name`, `Age`, "A" as iOrder FROM BoyssList
        UNION
     SELECT `ID`, `Name`, `Age`, "B" as iOrder FROM GirlsList) as iStudentList
ON ClassList.`ID` = iStudentList.`ID`
ORDER BY ClassList.`iOrder` DESC, iStudentList.`Name` ASC

- John Woo

Answer 2

您可以使用 union all（希望此查询没有错误）

select cl.id, bl.name
from ClassList cl, BoysList bl
where cl.id = bl.id
order by bl.name
union all
select cl.id, gl.name
from ClassList cl, GirlsList gl
where cl.id = gl.id
order by cl.name

修改后的查询：

select cl.id, bl.name, 'A' as ket
from boyList bl, classList cl
where bl.id = cl.id
union all
select cl.id, gl.name, 'B' as ket
from girlList gl, classList cl
where gl.id = cl.id
order by ket desc, name

- Amy Widjaja

---

很抱歉，我尝试了以上的代码并得到了错误代码... 我进行了修正如下：选择 cl.id, bl.name, 'A' as ket 来自 boyList bl, classList cl 在 bl.id = cl.id 联合全部 选择 cl.id, gl.name, 'B' as ket 来自 girlList gl, classList cl 在 gl.id = cl.id 按照 ket 降序排序，然后按照 name 现在它可以正常工作了... 感谢 johntotetwoo。 - Amy Widjaja

Answer 3

除了使用 UNION，你还可以使用 OUTER JOIN：

SELECT 
    cl.ID                         AS ID
  , COALESCE(b.`Name`, g.`Name`)  AS Name
  , COALESCE(b.Age, g.Age)        AS Age
  , cl.`Level`                    AS Level
FROM
      ClassList AS cl
  LEFT OUTER JOIN 
      BoysList AS b
          ON b.ID = cl.ID
  LEFT OUTER JOIN 
      GirlsList AS g
          ON g.ID = cl.ID

- ypercubeᵀᴹ