sql - 如何在sql中对同类行进行分组

Question

我有一个像这样的表:

    Table1
---------------------
Id |Kind1|Kind2|Kind3|
---------------------
1  |  2  |  2  |  3  | *
---------------------
2  |  2  |  2  |  2  | +
---------------------
3  |  2  |  2  |  1  |
---------------------
4  |  2  |  2  |  3  | *
---------------------
5  |  2  |  1  |  1  |
---------------------
6  |  1  |  1  |  1  |
---------------------
7  |  2  |  2  |  2  | +
----------------------

我想得到如下所示的结果 ID 必须自动生成我必须计算同一行组的数量

Result
------------------------------
Id |  Count |New1 |New2 |New3 |
------------------------------
1  |    2   |  2  |  2  |  2  | 
------------------------------
2  |    2   |  2  |  2  |  3  |
------------------------------
3  |    1   |  2  |  2  |  1  |
------------------------------
4  |    1   |  2  |  1  |  1  |
------------------------------
5  |    1   |  1  |  1  |  1  |
------------------------------

我如何在 sql server 中执行此操作？

Answer 1

select ROW_NUMBER() over (order by (select 0)) as ID,count(*) as count,kind1 as new1,kind2 as new2,kind3 as new3
from yourtable
group by kind1,kind2,kind3