T-SQL将一组行与其他行组进行比较

So I'm trying to filter one table by the values of multiple rows grouped by one column which match multiple rows of another table which are grouped by a column. For Exmaple:

所以我试图通过按一列分组的多行的值来过滤一个表,这些行匹配另一个表的多行,这些行按列分组。对于Exmaple:

###Table1###
+--------+-------+
| Symbol | Value |
+--------+-------+
| A      |     1 |
| A      |     2 |
| A      |     3 |
| B      |     9 |
| B      |     8 |
+--------+-------+

###Table2###
+--------+-------+
| Symbol | Value |
+--------+-------+
| C      |     9 |
| C      |     8 |
| D      |     1 |
| D      |     2 |
| D      |     4 |
| E      |     9 |
| E      |     8 |
| F      |     1 |
| F      |     2 |
| F      |     3 |
+--------+-------+

The query needs to return C, E, and F but not D because the values for A match the values of F, and the values of B match the values of C and E.

查询需要返回C,E和F但不返回D,因为A的值与F的值匹配,B的值与C和E的值匹配。

I hope this makes sense.

我希望这是有道理的。

1 个解决方案

#1

You can get the match by joining the tables on the value and then counting the symbols. For your data, this should work:

您可以通过连接值的表格然后计算符号来获得匹配。对于您的数据,这应该工作:

select t2.symbol, t1.symbol
from (select t1.*, count(*) over (partition by symbol) as cnt
      from table1 t1
     ) t1 join
     table2 t2
     on t1.value = t2.value
group by t1.symbol, t2.symbol, t1.cnt;
having count(*) = t1.cnt

This assumes:

No duplicates in either table.

两个表中都没有重复项。

You are looking for rows in table2 that match table1, but table2 could have additional values not in table1.

您正在寻找table2中与table1匹配的行,但table2可能具有不在table1中的其他值。

#1