SQL Server分组，不丢失行

I have a table like this which needs to be grouped by Name column but need to retain Ids and Dates Is there a solution without XML PATH or STRING_AGG

我有一个这样的表，它需要按名称列分组，但需要保留id和日期是否有一个没有XML路径或STRING_AGG的解决方案

Id  Name    Date    
1   Apple   2018/20/04  
2   Apple   2018/20/03  
3   Orange  2018/20/01  
4   Apple   2018/20/02  
5   Orange  2018/20/03

Group by name should yield =>

按名称分组的结果应该等于>

Id  Name    Date        Group
1   Apple   2018/20/04  1
2   Apple   2018/20/03  1
3   Orange  2018/20/01  2
4   Apple   2018/20/02  1
5   Orange  2018/20/03  2

2 个解决方案

#1

You can try this.

你可以试试这个。

Using DENSE_RANK function,then ORDER BY ID to keep the order is asc

使用DENSE_RANK函数，然后按ID排序以保持顺序为asc

SELECT Id,Name,Dates,DENSE_RANK() OVER(ORDER BY NAME) 'GROUP'
FROM T2
ORDER BY ID

sqlfiddle:http://sqlfiddle.com/#!18/10652/1

sqlfiddle:http://sqlfiddle.com/ ! 18/10652/1

#2

Try something like this.

这样的尝试。

Select [ID], [Name], [Date], 
ROW_NUMBER() over (partition by [Name] order by [ID]) as [Group]
from TableName

Result

结果

+----+----------+--------------+-------+
| ID | Name     |    Date      | Group |
+----+----------+--------------+-------+
|  1 | Apple    | 2018/20/04   |     1 |
|  2 | Apple    | 2018/20/03   |     2 |
|  4 | Apple    | 2018/20/02   |     3 |
|  3 | Orange   | 2018/20/01   |     1 |
|  5 | Orange   | 2018/20/03   |     2 |
+----+----------+--------------+-------+

#1