从同一个表中加入数据 [英] JOIN data from same table

问题描述

我有一个表，包含学生的姓名，电子邮件，职位等，以及他们的状态（可以是Y或N之一）。我想写一个查询，计算每个位置类型，以及每个类型中使用JOIN的Y的数量和N的数量。 （也就是说，它是一个包含三个列的表：Position，StatusIsYes和StatusIsNo。）

I have a table containing the names, emails, positions, etc of a students, as well as their "status" (which can be one of Y or N.) I want to write a query that counts the number of each type of position, as well as the number of Y AND the number of N within each type using JOIN. (That is, it would be a table with three columns: Position, StatusIsYes, and StatusIsNo.)

我已经使用CASE子句通过以下方式我不能弄清楚如何使用JOIN子句。

I have already done this using the CASE clause the following way, but I can't figure out how to do it using the JOIN clause.

SELECT position,
COUNT(CASE WHEN status = 'Y' THEN 1 ELSE NULL END) AS StatusIsYes,
COUNT(CASE WHEN status = 'N' THEN 1 ELSE NULL END) AS StatusIsNo
 FROM 
students GROUP BY crd

我感谢任何建议！

知道可以使用JOIN来完成，但是我想知道如何使用 a JOIN

I know it can be done without using JOIN, but I want to know how it is possible to do it with a JOIN.

推荐答案

您可以在要从同一个表中提取记录的情况下使用SELF JOIN。

例如：

表名：employee

字段：EmpId，EmpName，ManagerId

现在，如果您想获取Empolyees的详细信息在Manager Position中，我们需要这样编写查询：

SELECT e1.EmpId，e1.EmpName FROM EmployeeDetails e1，EmployeeDetails e2其中e1.EmpId = e2.ManagerId;

You can use SELF JOIN in the case when you want to fetch records from same table.
For ex:
Table Name: employee
Fields : EmpId,EmpName,ManagerId
Now if you want to get the details of Empolyees who are in Manager Position for that we need to write query like this:
SELECT e1.EmpId, e1.EmpName FROM EmployeeDetails e1, EmployeeDetails e2 where e1.EmpId=e2.ManagerId;

希望它会帮助你。

要了解更多信息，请检查 this 链接。

Fro more information please check this link.

这篇关于从同一个表中加入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！