多个查询如何加入它 [英] Multiple query how to join it

问题描述

我有2张桌子，但我想加入他们。怎么做



选择classSchedule。[classScheduleId]来自classSchedule



classScheduleId

------------

1000

1001

1002



选择classSchedule.classScheduleId，count（attendance.studentId）as attend where attendance.classScheduleId = classSchedule.classScheduleId group by（classSchedule.classScheduleId）



classScheduleId |参加

--------------------------

1001 | 2





但我期待的是



classScheduleId |参加

--------------------------

1000 | 0

1001 | 2

1002 | 0



我的尝试：



在这种情况下，我不知道如何做到这一点，我只是想找到一些例子来解决它，但直到现在我也不知道我要搜索的关键字

i have 2 table but I want join them together. how to do it

select classSchedule.[classScheduleId ] from classSchedule

classScheduleId
------------
1000
1001
1002

select classSchedule.classScheduleId ,count(attendance.studentId) as attend where attendance.classScheduleId=classSchedule.classScheduleId group by(classSchedule.classScheduleId )

classScheduleId | attend
--------------------------
1001 | 2


but what i expect is

classScheduleId | attend
--------------------------
1000 | 0
1001 | 2
1002 | 0

What I have tried:

In this case i dont know how to do so that i just try to find some example to solve it but until now i also dont know the keyword that i want to search

推荐答案

你需要一个GROUP BY和LEFT JOIN

尝试：

You need a GROUP BY and a LEFT JOIN
Try:

SELECT s.classScheduleID, COUNT(a.classScheduleId) FROM classSchedule s
LEFT JOIN Attendance a ON a.classScheduleId = s.classScheduleID
GROUP BY s.classScheduleId

您需要使用左连接或右连接，具体取决于sql语句中表的顺序，请查看： SQL LEFT JOI N关键字 [ ^ ]。如果没有匹配的记录，某些字段将为null。在你的情况下，它将是：

classScheduleId |参加

--------------------------

1000 | NULL

1001 | 2

1002 | NULL

您可以使用 ISNULL（）将NULL转换为0 [ ^ ]如果您愿意的话。

然而，这样做会给读者提供错误的信息。如果出勤率确实为0，则应将其明确插入考勤表。 NULL表示数据尚未可用，暗示这些类尚未进行，而0出勤意味着没有参与者参加这些课程。

You will need to use either left join or right join depending on the order of your tables in the sql statement, check this out: SQL LEFT JOIN Keyword[^]. Some fields will be null if there is no matching records. As in your case, it will be:
classScheduleId | attend
--------------------------
1000 | NULL
1001 | 2
1002 | NULL
You can convert the NULL to 0 using ISNULL()[^] if you want to.
However, doing so will give the readers wrong information. If the attendance is really 0, it should be inserted into the attendance table explicitly. NULL means the data is not available yet, implying that these classes have not been conducted whereas 0 attendance means no participants turned up for these classes.

这篇关于多个查询如何加入它的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！