SQL Server - 三列的不同组合 - 顺序无关紧要

Question

我想通过 SQL Server 中的每个组合获取 3 列值组合的计数。我如何获得这个？

例如

Row    P1   P2   P3
 1     3    10   20
 2     4    15   29
 3     8    10   16
 4     15    4   29
 5     10   20    3

Output Should be:
3  10  20  - 2
4  15  29  - 2
8  10  16  - 1

谢谢，

Answer 1

选项 1 -

cross apply, for xml path, group by.

select      vals,count (*) as cnt
from        t cross apply (select ' ' + cast (p as varchar(10)) from (values (p1),(p2),(p3)) as t(p) order by p for xml path('')) v (vals)
group by    vals

---

vals        cnt
 3 10 20    2
 4 15 29    2
 8 10 16    1

选项 2 -

xquery，分组依据

select      vals
           ,count (*) as cnt

from       (select      cast ((cast ('' as xml)).query('for $i in (sql:column("p1"),sql:column("p2"),sql:column("p3")) order by $i return $i') as varchar(max)) as vals

            from        t
            ) t

group by    vals
;

---

vals        cnt
-------     ---
3 10 20     2
4 15 29     2
8 10 16     1

---

选项 3 -

Unpivot, row_number, Pivot.

select      [1],[2],[3],count (*) as cnt
from       (select      row,p,row_number () over (partition by row order by p) as n
            from        t unpivot (p for col in (p1,p2,p3)) upv
            ) t pivot (max(p) for n in ([1],[2],[3])) pv
group by    [1],[2],[3]

---

1   2   3   cnt
--- --- --- ---
3   10  20  2
4   15  29  2
8   10  16  1

选项 4 -

偏移n行，分组依据。

select      [1],[2],[3],count(*) as cnt

from       (select      (select p from (values (p1),(p2),(p3))as t(p) order by p offset 0 rows fetch first 1 row only)  as [1]
                       ,(select p from (values (p1),(p2),(p3))as t(p) order by p offset 1 rows fetch first 1 row only)  as [2]
                       ,(select p from (values (p1),(p2),(p3))as t(p) order by p offset 2 rows fetch first 1 row only)  as [3]

            from        t
            ) t

group by    [1],[2],[3]

---

1   2   3   cnt
--- --- --- ---
3   10  20  2
4   15  29  2
8   10  16  1