此子查询是否可以写入连接并仍然获得相同的结果集/行数?
问题描述:
如何将以下内容写入连接并获取相同数量的行?此子查询是否可以写入连接并仍然获得相同的结果集/行数?
SELECT
s.subjectid,
s.subjectname,
(SELECT
COUNT(*)
FROM dbo.Classes AS c
WHERE c.SubjectID = s.SubjectID
AND c.MondaySchedule = 1)
AS numofclasses
FROM dbo.subjects AS s
ORDER BY numofclasses DESC
我试图把它写像下面,但得到了不同的答案:
SELECT
s.subjectid,
COUNT(ClassID) AS numberofclasses
FROM dbo.subjects AS s
LEFT JOIN dbo.classes AS c
ON s.SubjectID = c.SubjectID
WHERE c.MondaySchedule = 1
GROUP BY s.Subjectid
ORDER BY numberofclasses DESC
答
移动where
条件的on
条件。据转换外部联接于内连接:
select s.subjectid, count(ClassID) as numberofclasses
from dbo.subjects s left join
dbo.classes c
on s.SubjectID = c.SubjectID and c.MondaySchedule = 1
group by s.Subjectid
order by numberofclasses desc ;
这并假定subjects(subjectid)
是唯一的(或主键)。否则,这两者可能会返回不同的结果。
+0
哇,它确实有帮助。我正在获取更多的SQL专业知识,并因此感到好奇 - 如何将条件解决条件解决呢?它背后的概念/理论是什么? –
+0
@NikhilaBakurupanda。 。 。 'where'条件过滤掉'c.MondaySchedule = 1'的行。这些是由“左连接”专门创建的行。结果与内部连接相同。 –
我试图把它写像下面,但得到了不同的答案: –
选择s.subjectid,计数(的ClassID)从dbo.subjects numberofclasses 为S 留在s.SubjectID =加入dbo.classes为c c.SubjectID 其中c.MondaySchedule = 1 group by s.Subjectid order by numberofclasses desc –
首先找出您正在使用哪个RDBMS – Strawberry