我有一个表,包含学生的姓名,电子邮件,职位等,以及他们的状态(可以是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.
更多推荐
从同一个表中加入数据
发布评论