sql server json多层 拼接 sql join多张表

目录

什么是联结

编写JOIN

表格与列

键

主键 (PK)

外键 (FK)

主键 - 外键关联性

连接多个表格

别名

JOIN 简介

INNER JOIN

OUTER JOIN

总结

主键和外键

JOIN

别名

---

什么是联结

简单的说，就是将其他表中的列添加过来，进行“添加列”的运算。该操作通常用于无法从一张表中获取期望数据（列）的操作。使用联结可以从多张表（3张以上都行）中选取数据。

JOIN 语句的整个目标是一次能够从多个表格中获取数据。JOIN 使我们能够从多个表格中获取数据。是既简单，又很强大的功能。

编写JOIN

以下是一个 JOIN 语句，你将发现，我们在普通查询中引入了两个新的部分：JOIN 和 ON。JOIN 指定了你要从中获取数据的第二个表格。ON 表示你想如何合并 FROM 和 JOIN 语句中的表格。

SELECT orders.*
FROM orders
JOIN accounts
ON orders.account_id = accounts.id;

练习问题

1. 尝试获取 accounts 表格中的所有数据，以及 orders 表格中的所有数据。
2. 尝试从 orders 表格中获取 standard_qty、gloss_qty 和 poster_qty，并从 accounts 表格中获取 website 和 primary_poc。

SELECT accounts.*,orders.*
FROM accounts 
JOIN orders
ON accounts.id = orders.id;

SELECT orders.standard_qty,orders.gloss_qty,orders.poster_qty,accounts.website,accounts.primary_poc
FROM orders JOIN accounts
ON accounts.id = orders.id;

= 两边的列顺序并不重要

实体关系图（ERD)

表格与列

在 如上数据库中，有 5 个表格

1. web_events
2. accounts
3. orders
4. sales_reps
5. region

你将发现，表格中某些列的列名称旁边具有 PK 或 FK，而其他列根本没有标签。如果你再仔细观察，可能会发现，PK 在每个表格中与第一列相关。PK 表示主键。每个表格都存在主键，它是每行的值都唯一的列。如果你查看我们的数据库中每个表格的前几行，你会发现这个首个 PK 列始终是唯一的。对于此数据库，它始终称为 id，但并非所有数据库都这样。

键

主键 (PK)

主键是特定表格的唯一列。在我们的每个表格中是第一列，并且这些列都叫做 id，但是并非必须都要这样。通常，在大多数数据库中，主键是表格的第一列。

外键 (FK)

外键是另一个表格中的主键。我们可以在上一个 ERD 中看到，外键如下所示：

1. region_id
2. account_id
3. sales_rep_id

每个都与另一个表格的主键相关。下图是一个示例：

主键 - 外键关联性

在上图中，可以看出：

1. region_id 是外键。
2. region_id 与 id 相连 - 它是将这两个表格相连的主外键关系。
3. Crow's foot（鱼尾纹） 表示 FK 实际上可以出现在 sales_reps 表格中的很多行中。
4. 我们 PK 显示在此表格中 id 每行仅出现一次。

注意，我们的 SQL 查询包含两个想要连接的表格：一个来自 FROM，另一个来自 JOIN。然后在 ON 中，我们始终让主键等于外键：

连接多个表格

可以利用同一逻辑连接多个表格。看看下面的三个表格。

如果我们想连接所有这三个表格，我们可以采用相同的逻辑。

FROM web_events
JOIN accounts
ON web_events.account_id = accounts.id
JOIN orders
ON accounts.id = orders.account_id

现在，我们的 SELECT 语句可以从所有三个表格中获取数据。同样，JOIN 存储的是表格，ON 是让主键等于外键。

SELECT 语句将需要指定你想从中获取列的表格：

SELECT web_events.channel, accounts.name, orders.total

我们可以继续按照这一流程操作，连接所有要连接的表格。为了提高效率，我们可能不希望这么做，除非需要从所有表格中获取信息。

别名

当我们连接表格时，最好能为每个表格设置一个别名。你实际上在算术运算符部分见过类似的列名称别名。

示例：

FROM tablename AS t1
JOIN tablename2 AS t2

之前，你见过如下所示的语句：

SELECT col1 + col2 AS total, col3

经常你可能还会见到没有 AS 部分的语句。上述每个语句都可以改写为以下形式，结果依然相同：

FROM tablename t1
JOIN tablename2 t2

以及

SELECT col1 + col2 total, col3

我们可以直接在列名称（在 SELECT 中）或表格名称（在 FROM 或 JOIN 中）后面写上别名，方法是在要设定别名的列或表格后面直接写上别名。不是必须要用as 。

练习

1. 为与客户名称 Walmart 相关的所有 web_events 创建一个表格。表格应该包含三列：primary_poc、事件时间和每个事件的渠道。此外，你可以选择添加第四列，确保仅选中了 Walmart 事件。
2. 为每个 sales_rep（销售代表）对应的 region（区域）以及相关的 accounts（客户）创建一个表格，最终表格应该包含三列：区域 name（名称）、销售代表 name（名称），以及客户 name（名称）。根据客户名称按字母顺序 (A-Z) 排序。
3. 提供每个 order（订单）的每个区域 name（名称），以及客户 name（名称）和订单的 unit price（单价） (total_amt_usd/total)。最终表格应该包含三列：region name（区域名称）、account name（客户名称）和 unit price（单价）。少数几个客户的总订单数为 0，因此我除以的是 (total + 0.01) 以确保没有除以 0。

SELECT a.primary_poc, w.occurred_at, w.channel, a.name
FROM web_events w
JOIN accounts a
ON w.account_id = a.id
WHERE a.name = 'Walmart';

SELECT r.name region,s.name rep,a.name account,
FROM sales_reps s
JOIN region r
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
ORDER BY a.name;

SELECT r.name region, a.name account,
o.total_amt_usd/(o.total + 0.01) unit_price
FROM region r
JOIN sales_reps s
ON s.region_id = r.id
JOIN accounts a
ON a.sales_rep_id = s.id
JOIN orders o
ON o.account_id = a.id;

JOIN 简介

INNER JOIN

注意，到目前为止介绍的每个 JOIN 都是 INNER JOIN，即我们仅获取在两个表格中都匹配存在的行。

你可能见过以下 SQL 语法

LEFT OUTER JOIN

或

RIGHT OUTER JOIN

这些命令和 LEFT JOIN 和 RIGHT JOIN 完全一样。

OUTER JOIN

如果我们想要展示的数据并未同时存在于两张表格，而只存在于其中一张表格，此时可以用外联结。它将返回内连接的结果，以及被连接的表格中没有匹配的行。这种连接返回的是与两个表格中的某个表格不匹配的行，完整的外连接用例非常罕见。

LEFT JOIN 得到的结果包含了能匹配右表的所有结果，这部分与INNER JOIN 一样，同时它还会返回左表中没有匹配项的所有结果。左边的是主表

示例：

          

RIGHT JOIN得到的结果包含了能匹配左表的所有结果，这部分与INNER JOIN 一样，同时它还会返回右表中没有匹配项的所有结果。右边的是主表，即accounts是主表。

            

如果连接的表格没有匹配的信息，那么就会出现单元格为空的列。这些空的单元格就引出了新的数据类型，叫做 NULL。

示例：INNER JOIN

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
JOIN State s
ON c.countryid = s.countryid;

      

生成的表格中的列数。3   生成的表格中的行数。6 countryid 1 将在生成的表格中出现的次数。2    countryid 6 将在生成的表格中出现的次数。0

因为这是 JOIN（实际上是 INNER JOIN），我们只能获得在两个表格中都出现了的行。因此，生成的表格将看起来像右侧表格，并获取列 countryName。因为1、2、3 和 4 是两个表格中的 countryid，因此将同时获取这些信息。countryid 5 和 6 仅出现在了 Country 表格中，因此将忽略这两行。

示例：LEFT JOIN

SELECT c.countryid, c.countryName, s.stateName
FROM Country c
LEFT JOIN State s
ON c.countryid = s.countryid;

    

生成的表格中的列数。3     生成的表格中的行数。8 

countryid 1 将在生成的表格中出现的次数。2      countryid 6 将在生成的表格中出现的次数。1

总结

主键和外键

你学习了在连接数据库中的表格时与主键和外键相关的关键要素：

主键 - 对于表格中的每行都是唯一的。主键通常是数据库中的第一列（就像 Parch & Posey 数据库中每个表格的 id 列）。

外键 - 是出现在另一个表格中的主键，允许行不是唯一的行。

数据库的数据设置非常重要，但通常不是数据分析师的职责。这一过程称为数据库规范化。

JOIN

在这节课，你学习了如何使用 JOIN 组合多个表格的数据。你将最常用到的三个 JOIN 语句为：

1. JOIN - 一种 INNER JOIN，仅获取在两个表格中都存在的数据。
2. LEFT JOIN - 用于获取 FROM 中的表格中的所有行，即使它们不存在于 JOIN 语句中。
3. RIGHT JOIN - 用于获取 JOIN 中的表格中的所有行，即使它们不存在于 FROM 语句中。

还有高级 JOIN，它们仅适用于非常特定的情况。UNION 和 UNION ALL、CROSS JOIN 和比较难的 SELF JOIN。这些内容比较深奥，它们在特殊情况下比较实用。

别名

可以使用 AS 或直接对表格和列设定别名。这样可以减少要输入的字符数，同时确保列标题可以描述表格中的数据。