mysql连接表

发布于 2024-12-25 06:33:07 字数 770 浏览 0 评论 0原文

我是连接表的新手。

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

结果的顺序将是男孩女孩在前的列表，名称为 ASC。如何实现这一点？

原文

I am new with joining tables.

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

I was able to join two tables. But, what if, in this situation, you have to combine first the two tables (BoysList and GirlsList) and then join it with ClassList.

The Resultset would be:

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

order of the result would be list of boys girls first, name ASC. How do accomplish this?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

醉梦枕江山 2025-01-01 06:33:07

您要做的第一件事就是UNION BoysList 和 GirlsList，然后INNER JOIN 将其与 ClassList

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

the first thing you will do is to UNION the BoysList and GirlsList then INNER JOIN it with ClassList

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

回复收藏 0 原文

扛刀软妹 2025-01-01 06:33:07

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

选择 cl.id、bl.name 来自 ClassList cl、BoysList bl 其中 cl.id = bl.id 按 bl.name 排序联合所有选择 cl.id、gl.name 来自 ClassList cl、GirlsList gl 其中 cl.id = gl.id 按 cl.name 排序

修改后的查询：

select cl.id, bl.name, 'A' as ket 来自 boyList bl、classList cl 其中 bl.id = cl.id 联合所有选择 cl.id、gl.name、'B' 作为 ket 来自girlList gl、classList cl 其中 gl.id = cl.id 按 ket desc、名称排序

回复收藏 0 原文

沧笙踏歌 2025-01-01 06:33:07

除了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

Besides UNION, you can use 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

回复收藏 0 原文

~没有更多了~