求教：数据查询重复，并显示重复内容跟重复的次数

[laogendede]

求教一个问题，怎么把数据重复次数显示在右边（效果如图三），谢谢

这是表设计

这是数据

这是我想要的最后结果

[小野马1209]

declare @t1 table (ID int,uname varchar(100))
insert into @t1
select 1,'A' UNION ALL
select 2,'A' UNION ALL
select 3,'B' UNION ALL
select 4,'A' 
---以上为测试数据
SELECT *,COUNT(1) OVER(PARTITION  BY uname)  AS '重复次数' 
FROM @t1
order by ID

[小野马1209]

结果： /* ID uname 重复次数 1 A 3 2 A 3 3 B 1 4 A 3 */

[heart_xitao]

SELECT uname,count(uname) as num FROM r_name GROUP BY uname

[qq_33052575]

select a.id,a.uname,(select count(1) from tablename b where b.uname = a.uname) as 出现次数 from tablename a

[ZXYperseverance]

SELECT S.ID , S.UNAME,A.CS FROM S LEFT JOIN (SELECT S.UNAME,COUNT(1) AS CS FROM S ) A ON S.UNAME=A.UNAME ORDER BY A.CS DESC,S.ID ASC;