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.
相关文章