SQL 中的内部连接和分组依据，没有聚合函数.

问题描述

我正在尝试从具有某些条件的 2 个表中检索数据.当我只对条件进行内连接时，我得到了一个巨大的价值(200000 个数据).但是当我分组时，我得到的值非常少，例如(8000 条数据).

I am trying to retrieve data from 2 table with some conditions. When I just do the inner join with the conditions , I get a huge value (200000 data). But when I group by I get a very less value like (8000 data).

SELECT Tcg.SK_tID, Tcg.SK_ServiceProviderID
INTO #CHDetails 
FROM #ClientGroup Tcg           
INNER JOIN dbo.Component AS chd ON  chd.SK_PID = Tcg.SK_PID 
                                AND chd.SK_ServiceProviderID = Tcg.SK_ServiceProviderID
                                AND chd.SK_CompID = @CHD
                                AND chd.ReportDate < @ReportDate
GROUP BY Tcg.SK_PID ,Tcg.SK_ServiceProviderID

你能告诉我这是什么原因吗?内连接总是采用公共数据.#ClientGroup 表中的数据在 70000 左右，而 dbo.Component 中的数据非常庞大.当我从逻辑上查询公共 PID 和服务提供者时，它应该给我等于或小于 #ClientGroup 的记录.它如何提供更多?

Can you please let me know the cause for this. Inner join always takes the common data. The data in the #ClientGroup table is around 70000 , while data in the dbo.Component is very huge. When I query for common PID and Service provider logically it shoul give me the records equal to or less than #ClientGroup. How is it giving more ?

当我分组时，我得到 8000.但是为什么我要在 2 个表的内部联接中分组.

When I do group by i get 8000. But why should I do group by in a inner join for 2 tables.

推荐答案

group by 本质上是对结果执行不同的操作.您必须这样做的原因可能是因为您在两个表中都有重复项.

The group by is essentially performing a distinct on the result. The reason you have to do this is likely because you have duplicates in both tables.

看到这个 sqlfiddle:http://sqlfiddle.com/#!3/cbdca/2

See this sqlfiddle: http://sqlfiddle.com/#!3/cbdca/2

其中，table1 有 3 行，table2 有 3 行.连接在一起时，它们返回 9 行.

In it, table1 has 3 rows and table2 has 3 rows. When joined together, they return 9 rows.

这篇关于SQL 中的内部连接和分组依据，没有聚合函数.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！