問題描述

我正在嘗試從具有某些條件的 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模板網！