SELECT uname,count(uname) as num FROM r_name GROUP BY uname
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
结果: /* ID uname 重复次数 1 A 3 2 A 3 3 B 1 4 A 3*/
select a.id,a.uname,(select count(1) from tablename b where b.uname = a.uname) as 出现次数 from tablename a
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;
FROM r_name
GROUP BY uname
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
/*
ID uname 重复次数
1 A 3
2 A 3
3 B 1
4 A 3*/
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;