加入可能的GROUP子句 [英] JOIN with probable GROUP clause

问题描述

我有一个ID列加入的两个表（P和R）。表P还有一个TYPE列，表示由ID指示的条目的TYPE（约100个中的1个）。

表R还有一个名为REF的列，表示加载了ID的文件来自。



我需要知道的是如何加入P和R以便为我提供多个类型的ID所在的文件（REF）文件。

请注意，表R中可能有多个相同的ID，但表P中只有一个。



这是一个星期五的下午，我的大脑被炒了....帮助!!!!



我需要这个尽可能高效。

记住表R有超过35,000,000行，表P有大约30,000



例如

表P

ID类型

1 a

2 a

3 b

4 b

5 b



表R

ID REF

1 aa

1 aa

2 aa

2 aa

3 aa

4 bb

5 bb

1 cc

1 cc

1 dd



在上面的示例中，我将返回REF''aa''，因为它包含ID 1,2和3是TYPE''a''和'b''。而REF''bb''只包含带有TYPE''b'的ID，而我想要的是一个文件（REF）中有多个TYPE的那些。



这个SQL是否给了我想要的东西？

SELECT r.REF FROM R作为r JOIN P作为p ON p.ID = r.ID group by r.REF HAVING count（p。 TYPE）> 1

Hi,

I''ve got two tables (P and R) joined by an ID column. Table P also has a TYPE column which indicates the TYPE (1 of about 100) of the entry indicated by the ID.
Table R also have a column called REF which indicates the files which the ID was loaded from.

What I need to know is how I can join P and R in order to give me the files (REFs) where more than one TYPE of ID was in the file.
Note that there can be more than one of the same ID in Table R but there will only be one of it in Table P.

Its a friday afternoon and my brain is fried....HELP!!!!

I need this to be as efficient as possible.
Bear in mind Table R has over 35,000,000 rows and Table P has about 30,000

E.g.
Table P
ID TYPE
1 a
2 a
3 b
4 b
5 b

Table R
ID REF
1 aa
1 aa
2 aa
2 aa
3 aa
4 bb
5 bb
1 cc
1 cc
1 dd

In the above example I would be returned REF ''aa'' because it contained IDs 1, 2 and 3 which are of TYPEs ''a'' and ''b''. Whereas REF ''bb'' only contained IDs with a TYPE ''b'' and I want the ones where there is more than one TYPE in a a file(REF).

Does this SQL give me what I want?
SELECT r.REF FROM R as r JOIN P as p ON p.ID=r.ID group by r.REF HAVING count(p.TYPE) > 1

推荐答案

如果我正确理解它...我想你只需要添加关键字 DISTINCT 到你的计数功能。所以它将是：

If I''m understanding it correctly...I think you just need to add the keyword DISTINCT to your count function. So it would be:

SELECT r.REF FROM R as r JOIN P as p ON p.ID=r.ID group by r.REF HAVING count(distinct p.TYPE) > 1 

参见 google [ ^ ]了解更多信息。



------ ------更新------------



对于性能问题，也许使用临时表会h ELP？由于R是具有最多记录的表，或许首先使用不同的值拉动它并使用THAT表加入P会更好吗？这样的事情：

See google[^] for more info.

------------ Update ------------

For performance issues, perhaps using temporary tables would help? Since R is the table with the most records, perhaps first pulling it with distinct values and using THAT table to join to P would be better? Something like this:

SELECT r.Ref FROM (SELECT DISTINCT ID, REF FROM R as r) INNER JOIN P as p ON p.ID=r.ID GROUP BY r.Ref HAVING COUNT(DISTINCT p.Type) > 1

希望这有帮助。

Hope this helps.

这篇关于加入可能的GROUP子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！