select tel from info group by tel order by count(1) desc
order by count(Tel) over (partition by Tel) sql2005
DECLARE @TB TABLE([Id] INT, [Tel] INT, [Name] VARCHAR(2)) INSERT @TB SELECT 1, 123, 'XX' UNION ALL SELECT 2, 555, 'XX' UNION ALL SELECT 3, 555, 'XX' UNION ALL SELECT 4, 999, 'XX' UNION ALL SELECT 5, 2356, 'XX' UNION ALL SELECT 6, 123, 'XX' UNION ALL SELECT 7, 2356, 'XX' UNION ALL SELECT 8, 123, 'XX'SELECT Id,Tel,Name FROM (SELECT *,GRP=COUNT(*) OVER (PARTITION BY Tel) FROM @TB) T ORDER BY GRP DESC,Id /* Id Tel Name ----------- ----------- ---- 1 123 XX 6 123 XX 8 123 XX 2 555 XX 3 555 XX 5 2356 XX 7 2356 XX 4 999 XX */
SELECT TEL 个数=COUNT(TEL) FROM INFO GROUP BY TEL ORDER BY 个数 DESC 2356应该排第二!
--2000 DECLARE @TB TABLE([Id] INT, [Tel] INT, [Name] VARCHAR(2)) INSERT @TB SELECT 1, 123, 'XX' UNION ALL SELECT 2, 555, 'XX' UNION ALL SELECT 3, 555, 'XX' UNION ALL SELECT 4, 999, 'XX' UNION ALL SELECT 5, 2356, 'XX' UNION ALL SELECT 6, 123, 'XX' UNION ALL SELECT 7, 2356, 'XX' UNION ALL SELECT 8, 123, 'XX'SELECT Id,Tel,Name FROM ( SELECT *,CNT=(SELECT COUNT(*) FROM @TB WHERE Tel=T.Tel) FROM @TB AS T ) TB ORDER BY CNT DESC,Id
DECLARE @TB TABLE([Id] INT, [Tel] INT, [Name] VARCHAR(2)) INSERT @TB SELECT 1, 123, 'XX' UNION ALL SELECT 2, 555, 'XX' UNION ALL SELECT 3, 555, 'XX' UNION ALL SELECT 4, 999, 'XX' UNION ALL SELECT 5, 2356, 'XX' UNION ALL SELECT 6, 123, 'XX' UNION ALL SELECT 7, 2356, 'XX' UNION ALL SELECT 8, 123, 'XX' select *,次数=(select count(*) from @tb A where a.tel=b.tel) from @tb B order by 次数 desc,tel desc
----------------这个更简单 DECLARE @TB TABLE([Id] INT, [Tel] INT, [Name] VARCHAR(2)) INSERT @TB SELECT 1, 123, 'XX' UNION ALL SELECT 2, 555, 'XX' UNION ALL SELECT 3, 555, 'XX' UNION ALL SELECT 4, 999, 'XX' UNION ALL SELECT 5, 2356, 'XX' UNION ALL SELECT 6, 123, 'XX' UNION ALL SELECT 7, 2356, 'XX' UNION ALL SELECT 8, 123, 'XX' select * from @TB T order by (select count(1) from @TB where tel = T.tel) desc =========================================================================== /* Id Tel Name ----------- ----------- ---- 1 123 XX 6 123 XX 8 123 XX 2 555 XX 3 555 XX 5 2356 XX 7 2356 XX 4 999 XX */
Id Tel Name 1 123 XX 6 123 XX 8 123 XX 5 2356 XX 7 2356 XX 2 555 XX 3 555 XX 4 999 XX
"select * from info order by count(tel) desc" 你试试啊......学习中.....
DECLARE @TB TABLE([Id] INT, [Tel] INT, [Name] VARCHAR(2)) INSERT @TB SELECT 1, 123, 'XX' UNION ALL SELECT 2, 555, 'XX' UNION ALL SELECT 3, 555, 'XX' UNION ALL SELECT 4, 999, 'XX' UNION ALL SELECT 5, 2356, 'XX' UNION ALL SELECT 6, 123, 'XX' UNION ALL SELECT 7, 2356, 'XX' UNION ALL SELECT 8, 123, 'XX'select * from @tb order by COUNT(*) OVER (PARTITION BY Tel) desc(8 行受影响) Id Tel Name ----------- ----------- ---- 1 123 XX 6 123 XX 8 123 XX 2 555 XX 3 555 XX 5 2356 XX 7 2356 XX 4 999 XX(8 行受影响)
from info
group by tel
order by count(1) desc
INSERT @TB
SELECT 1, 123, 'XX' UNION ALL
SELECT 2, 555, 'XX' UNION ALL
SELECT 3, 555, 'XX' UNION ALL
SELECT 4, 999, 'XX' UNION ALL
SELECT 5, 2356, 'XX' UNION ALL
SELECT 6, 123, 'XX' UNION ALL
SELECT 7, 2356, 'XX' UNION ALL
SELECT 8, 123, 'XX'SELECT Id,Tel,Name
FROM (SELECT *,GRP=COUNT(*) OVER (PARTITION BY Tel) FROM @TB) T
ORDER BY GRP DESC,Id
/*
Id Tel Name
----------- ----------- ----
1 123 XX
6 123 XX
8 123 XX
2 555 XX
3 555 XX
5 2356 XX
7 2356 XX
4 999 XX
*/
2356应该排第二!
DECLARE @TB TABLE([Id] INT, [Tel] INT, [Name] VARCHAR(2))
INSERT @TB
SELECT 1, 123, 'XX' UNION ALL
SELECT 2, 555, 'XX' UNION ALL
SELECT 3, 555, 'XX' UNION ALL
SELECT 4, 999, 'XX' UNION ALL
SELECT 5, 2356, 'XX' UNION ALL
SELECT 6, 123, 'XX' UNION ALL
SELECT 7, 2356, 'XX' UNION ALL
SELECT 8, 123, 'XX'SELECT Id,Tel,Name
FROM (
SELECT *,CNT=(SELECT COUNT(*) FROM @TB WHERE Tel=T.Tel)
FROM @TB AS T
) TB
ORDER BY CNT DESC,Id
INSERT @TB
SELECT 1, 123, 'XX' UNION ALL
SELECT 2, 555, 'XX' UNION ALL
SELECT 3, 555, 'XX' UNION ALL
SELECT 4, 999, 'XX' UNION ALL
SELECT 5, 2356, 'XX' UNION ALL
SELECT 6, 123, 'XX' UNION ALL
SELECT 7, 2356, 'XX' UNION ALL
SELECT 8, 123, 'XX'
select *,次数=(select count(*) from @tb A where a.tel=b.tel) from @tb B order by 次数 desc,tel desc
DECLARE @TB TABLE([Id] INT, [Tel] INT, [Name] VARCHAR(2))
INSERT @TB
SELECT 1, 123, 'XX' UNION ALL
SELECT 2, 555, 'XX' UNION ALL
SELECT 3, 555, 'XX' UNION ALL
SELECT 4, 999, 'XX' UNION ALL
SELECT 5, 2356, 'XX' UNION ALL
SELECT 6, 123, 'XX' UNION ALL
SELECT 7, 2356, 'XX' UNION ALL
SELECT 8, 123, 'XX'
select * from @TB T order by (select count(1) from @TB where tel = T.tel) desc
===========================================================================
/*
Id Tel Name
----------- ----------- ----
1 123 XX
6 123 XX
8 123 XX
2 555 XX
3 555 XX
5 2356 XX
7 2356 XX
4 999 XX
*/
Id Tel Name
1 123 XX
6 123 XX
8 123 XX
5 2356 XX
7 2356 XX
2 555 XX
3 555 XX
4 999 XX
你试试啊......学习中.....
INSERT @TB
SELECT 1, 123, 'XX' UNION ALL
SELECT 2, 555, 'XX' UNION ALL
SELECT 3, 555, 'XX' UNION ALL
SELECT 4, 999, 'XX' UNION ALL
SELECT 5, 2356, 'XX' UNION ALL
SELECT 6, 123, 'XX' UNION ALL
SELECT 7, 2356, 'XX' UNION ALL
SELECT 8, 123, 'XX'select * from @tb order by COUNT(*) OVER (PARTITION BY Tel) desc(8 行受影响)
Id Tel Name
----------- ----------- ----
1 123 XX
6 123 XX
8 123 XX
2 555 XX
3 555 XX
5 2356 XX
7 2356 XX
4 999 XX(8 行受影响)