T-SQL - 获取具有相同 B 集的所有 As 的列表 [英] T-SQL - Get a list of all As which have the same set of Bs
问题描述
我正在尝试编写一个棘手的 SQL 查询.看看下表:
I'm struggling with a tricky SQL query that I'm trying to write. Have a look at the following table:
+---+---+
| A | B |
+---+---+
| 1 | 2 |
| 1 | 3 |
| 2 | 2 |
| 2 | 3 |
| 2 | 4 |
| 3 | 2 |
| 3 | 3 |
| 4 | 2 |
| 4 | 3 |
| 4 | 4 |
+---+---+
现在,从这个表中,我基本上想要一个所有具有完全相同 B 集的 As 的列表,并为每个集提供一个递增的 ID.
Now, from this table, I essentially want a list of all As which have the exact same set of Bs and give each set an incrementing ID.
因此,为上述设置的输出将是:
Hence, the output set for the above would be:
+---+----+
| A | ID |
+---+----+
| 1 | 1 |
| 3 | 1 |
| 2 | 2 |
| 4 | 2 |
+---+----+
谢谢.
如果有帮助,我会列出另一个表中可能存在的 B 的所有不同值.
If it helps, I have a list of all distinct values of B that are possible in another table.
非常感谢您提供的所有创新答案.确实能学到很多东西.
Thank you so much for all the innovative answers. Was able to learn a lot indeed.
推荐答案
这是解决棘手选择问题的数学技巧:
Here is mathematical trick to solve your tricky select:
with pow as(select *, b * power(10, row_number()
over(partition by a order by b)) as rn from t)
select a, dense_rank() over( order by sum(rn)) as rn
from pow
group by a
order by rn, a
小提琴 http://sqlfiddle.com/#!3/6b98d/11
这当然仅适用于有限的不同计数,因为您会溢出.以下是更通用的字符串解决方案:
This of course will work only for limited distinct count as you will get overflow. Here is more general solution with strings:
select a,
dense_rank() over(order by (select '.' + cast(b as varchar(max))
from t t2 where t1.a = t2.a
order by b
for xml path(''))) rn
from t t1
group by a
order by rn, a
小提琴 http://sqlfiddle.com/#!3/6b98d/29
这篇关于T-SQL - 获取具有相同 B 集的所有 As 的列表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!