sql - T-SQL-获取具有相同B组的所有As的列表

Question

我正在尝试编写一个棘手的SQL查询。请看下表:

+---+---+
| 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。

因此，上面的输出集将是:

+---+----+
| A | ID |
+---+----+
| 1 |  1 |
| 3 |  1 |
| 2 |  2 |
| 4 |  2 |
+---+----+

谢谢。

编辑:如果有帮助，我将列出另一个表中可能存在的B的所有不同值。

编辑:非常感谢您的所有创新答案。确实能够学到很多东西。

Answer 1

这是解决您的棘手选择的数学技巧:

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

fiddle http://sqlfiddle.com/#!3/6b98d/11

当然，这将仅在有限的非重复计数上起作用，因为您会溢出。这是使用字符串的更通用的解决方案:

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

fiddle http://sqlfiddle.com/#!3/6b98d/29