sql 带序号

CREATE TABLE [dbo].[auto] (
  [id] int NULL,
  [name] char(10) COLLATE Chinese_PRC_CI_AS NULL,
  [age] int NULL
)
ON [PRIMARY]
GO

INSERT INTO [auto] ([id], [name], [age])
VALUES (11, 'a', 12)
GO

INSERT INTO [auto] ([id], [name], [age])
VALUES (33, 'b', 22)
GO

INSERT INTO [auto] ([id], [name], [age])
VALUES (5, '3', 3)
GO

方法一：使用内联接
数据量大了不好处理，几百行的可以用

select NO = count(*),a.id,a.name
from auto as a inner join auto as b on a.name >= b.name
group by a.name,a.id
order by count(*)

方法二： 使用  IDENTITY 函数
可处理大批理的数据

create proc show
as
 select IDENTITY(int, 1,1) as a ,* into  #tb  from auto
 select * from #tb