哪个查询在正确或哪个错误？ [英] Which Query in Right or Which in Wrong?

问题描述

Hello Everyone。

我在两个查询中有很大的混淆。我想计算一个表中的总记录，其中主键位于另一个表中。我使用两种类型的查询，但两个查询给我不同的结果。所以请帮助我

Hello Everyone.
I have big confuse in two query. I want to count total records in a table which primary key is in another table. i user two types of query but both query giving me different result. so please help me

select COUNT(*) from first_tbl where isApprExam =1 and Code in (select schlcode from journals_tbl)

select COUNT(*) from regmaster inner join journals_tbl j on regmaster.Code=j.schlcode where regmaster.isApprExam =1

请告诉我他们为什么给出不同的答案，哪些是正确的查询。

Please tell me why they giving different answer and which is right query.

推荐答案

如果第一个表中的一行可以连接到第二个表中的多个行，则查询将计算每次出现的次数。
对于 IN 子句，每个对应的行只计算一次。



因此，如果您希望first_tbl / regmaster中的行数在journals_tbl中具有相应的行，则应使用 IN 或 EXISTS

If a row in the first table can be joined to several rows in the second table your query will count every occurrence.
In the case of the IN clause every corresponding row will be counted only once.

So if you want the number of rows in first_tbl/regmaster that has a corresponding row in journals_tbl you should use IN or EXISTS

解决方案2很好地回答了你的问题。



但是这里有额外的SQL计数例子可能会让它更清晰：

Solution 2 answers your question quite nicely.

But here are additional SQL count examples which may make it clearer:

--taking regmaster and first_tbl to be the same table

select COUNT(distinct regmaster.Code) 
from regmaster 
inner join journals_tbl j 
	on regmaster.Code = j.schlcode 
where regmaster.isApprExam = 1
;
--should give the same count as
select COUNT(*) from first_tbl where isApprExam =1 and Code in (select schlcode from journals_tbl)



select COUNT(*) 
from journals_tbl 
where Code in (select schlcode from regmaster where isApprExam = 1)
;
--should give the same count as
select COUNT(*) from regmaster inner join journals_tbl j on regmaster.Code=j.schlcode where regmaster.isApprExam =1

希望我能做到这一点，如果是这样，希望它可以帮助你理解。

Hopefully I got those right, and if so hope it helps you out in understanding.

Hi,

Don't use cont(*) its decrease the performance.

select COUNT(1) from regmaster inner join journals_tbl j on regmaster.Code=j.schlcode where regmaster.isApprExam =1

这篇关于哪个查询在正确或哪个错误？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！