SQL-自连接和分组

一.介绍

这是一道面试题，看似简单，其实还是有一定技巧的，分析一下可以复习一下SQL查询的一些重要概念。

二.问题

给定一个包含四列的员工表

• ID
• Name
• Salary
• ManagerId

要求

获取经理姓名、每个经理的员工数量以及每个团队的总工资。

三.设置表格和初始数据

根据问题设置表格

USE [TestDB]
GO

    /****** Object:  Table [dbo].[Employees]    Script Date: 5/28/2021 7:42:51 PM ******/   SET ANSI_NULLS ON
GO
   SET QUOTED_IDENTIFIER ON
GO
   CREATE TABLE [dbo]
        .
[Employees](
    [Id] [int] IDENTITY(1, 1) NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [Salary] [int] NOT NULL,
    [ManagerId] [int] NOT NULL,
 CONSTRAINT [PK_dbo.Employees] PRIMARY KEY CLUSTERED    (
    [Id] ASC   )WITH (
                PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON
            [PRIMARY]   ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

插入初始数据

insert dbo.Employees
Values  ('Greg', 100000, 1),
        ('George', 150000, 1),
        ('Helen', 130000, 1),
        ('Tom', 120000, 2),
        ('Kevin', 110000, 2),
        ('David', 120000, 3),
        ('Geek', 110000,3),
        ('Tesla', 120000,3)

结果是

四.解决问题

1.SQL 中的自连接

这是一个自连接问题。自连接是一种常规连接，但表与自身连接。我们在下面回顾了连接类型；

不同类型的 SQL JOIN

以下是 SQL 中不同类型的 JOIN，

• (INNER)JOIN。 返回两个表中具有匹配值的记录
• LEFT(OUTER)JOIN。 返回左表中的所有记录以及右表中匹配的记录
• RIGHT(OUTER)JOIN。 返回右表中的所有记录以及左表中匹配的记录
• FULL(OUTER)JOIN。 当左表或右表有匹配项时，返回所有记录

我们进行自连接，

• 左边的表是员工表 - dbo.Employees e，
• 右边的表是经理表 - dbo.Employees m，
• 其中 Employee 表的 ManagerId == Manager 表的 Id (EmployeeId)

SELECT m.Name [Manager Name], e.Id [Employee ID], e.salary [Employee Salary]
FROM dbo.Employees e JOIN dbo.Employees m
ON e.ManagerId = m.Id

我们得到了结果，

其中经理姓名是我们需要的，员工 ID 和薪水是从员工表（左）中的 SQL SELECT 中进行计数和求和的。

交叉连接

它返回连接表中行的笛卡尔积。换句话说，它将生成将第一个表中的每一行与第二个表中的每一行组合在一起的行。

例如。 我有两张表，学生表和员工表。交叉连接结果将是 45(5*9)

2.SQL 中的 Group by

为了获得计数和总数，我们需要使用Group by子句，

SQL GROUP BY 语句
GROUP BY语句将具有相同值的行分组为汇总行，例如“查找每个国家/地区的客户数量”。

GROUP BY语句通常与聚合函数（ COUNT()、MAX()、MIN()、SUM()、AVG() ）一起使用，按一列或多列对结果集进行分组。

SELECT m.Name [Manager Name], count(m.Id) [team amount], sum(e.salary) [Total Salary]
FROM dbo.Employees e JOIN dbo.Employees m
ON e.ManagerId = m.Id
group by m.name

我们对 m.name 进行分组，并使用聚合函数 Count() 和 Sum()。

备注

当我们选择 m.name 时，我们必须按 m.nam 分组；否则，假设我们按 m.id 分组

SELECT m.Name [Manager Name], count(m.Id) [team amount], sum(e.salary) [Total Salary]
FROM dbo.Employees e JOIN dbo.Employees m
ON e.ManagerId = m.Id
group by m.id

然后我们收到所选 m.name 的错误消息，

消息 8120，级别 16，状态 1，第 1 行，

列“dbo.Employees.Name”在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

五.概括

本文讨论了 SQL 查询的主要特性 - JOI 和 Group By。