SQL使用子查询而不是一对多加入 [英] SQL using subqueries instead of join for one to many

问题描述

我正在尝试创建一个选择，这将允许我将两个表连接在一起，而不使用任何使用JOIN的操作。抓住这两个表包含一对多的关系。

I am trying to create a select that will allow me to join two tables together without using any operation that uses JOIN. The catch is that these two tables contain a one to many relationship.

我们假设有两个表，部门和课程。

Let's say there are two tables, Department and Course.

部门
D_CODE | SCHOOL

01             | 1111


02              | 1111

03              | 2222


课程


CRS_CODE | D_CODE

MATH1            | 01

MATH2            | 02

ACCT1            | 03号

Department
D_CODE | SCHOOL
01             | 1111
02             | 1111
03             | 2222

Course
CRS_CODE | D_CODE
MATH1           | 01
MATH2           | 02
ACCT1           | 03

如果没有使用任何类型的加入，我如何找到所有学校及其提供的课程总数？


结果是，1111学校有2门课程，2222课程有1门课程。
我尝试使用WITH和多个子查询，但结果值总是重复。所以会说1111学校有16门课程，2222学校有16门课程（例如）。

How do I find all schools and the total number of courses it provides without using any type of join?
The result is that School 1111 has 2 courses and School 2222 has 1 course.
I've tried using WITH and multiple subqueries, but the resulting values always duplicates. So it will say School 1111 has 16 courses and School 2222 has 16 courses (as an example).

引导将不胜感激。谢谢！

Guidance would be much appreciated. Thank you!

推荐答案

尝试这个。没有在中使用子查询，其中条件也可以得到相同的结果。

Try this. Without using sub-query in Where condition also you will get the same result.

SELECT Count(D_CODE) No_OF_Course,
       SCHOOL
FROM   Department
WHERE  D_CODE IN(SELECT D_CODE
                 FROM   course)
GROUP  BY SCHOOL 

这篇关于SQL使用子查询而不是一对多加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！