create table a(id int ,
a_type varchar(30),
a_name varchar(20)
)
select * from a
insert into a values(1,'小说','rensheng小说1');
insert into a values(3,'科学','renshe科学');
insert into a values(2,'人文','rensh人文');
insert into a values(1,'小说','rensh地理');
insert into a values(4,'历史','rensh历史');
insert into a values(3,'人文','rensheng');效果:
姓名(a_name) 条数 编号(id)rensheng小说1 2 1rensh人文 1 2rensh科学 2 3rensh历史 1 4
a_type varchar(30),
a_name varchar(20)
)
select * from a
insert into a values(1,'小说','rensheng小说1');
insert into a values(3,'科学','renshe科学');
insert into a values(2,'人文','rensh人文');
insert into a values(1,'小说','rensh地理');
insert into a values(4,'历史','rensh历史');
insert into a values(3,'人文','rensheng');效果:
姓名(a_name) 条数 编号(id)rensheng小说1 2 1rensh人文 1 2rensh科学 2 3rensh历史 1 4
select 姓名=a_name, 条数=count(1), 编号=min(id)
group by a_name
你把数据放到数据库中就知道了 按name去分类显示效果为:
姓名(a_name) 条数 编号(id)rensheng小说1 2 1rensh人文 1 2rensh科学 2 3rensh历史 1 4
SELECT A_NAME,ROWS,ID FROM (
select A_NAME,COUNT(*) OVER(PARTITION BY ID) AS ROWS,RN=ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY GETDATE()),ID
FROM A) A
WHERE RN=1A_NAME ROWS ID
-------------------- ----------- -----------
rensheng小说1 2 1
rensh人文 1 2
rensheng 2 3
rensh历史 1 4(4 行受影响)