34,576
社区成员
发帖
与我相关
我的任务
分享
SELECT eName
FROM (SELECT eName, COUNT(1) AS ct
FROM (SELECT eName, eIDx
FROM pEdu
WHERE (eName LIKE '%1%')
GROUP BY eIDx, eName) AS t
GROUP BY eName) AS y
ORDER BY ct DESC
直接复制过来报错,自己改了下好用了,谢谢这位朋友。也感谢大家!
可以准点下班了create table #tb(eidx int,ename varchar(20))
insert into #tb
select 1,'j'
union all select 1,'2'
union all select 0,'123'
union all select 34,'123'
union all select 34,'123'
union all select 34,'123'
union all select 34,'111'
union all select 34,'111'
union all select 45,'123'
union all select 46,'132'
union all select 46,'123'
union all select 47,'123'
union all select 47,'123'
union all select 50,'update'
union all select 54,'2'
union all select 55,'123'
union all select 55,'132'
union all select 56,'1323'
union all select 56,'123'
select *
from (SELECT eName,eIDx,count(*) as count FROM #tb WHERE eName LIKE '%1%' GROUP BY eIDx, eName
)t
order by case when eName='123' then 0 when eName='111' or eName='1323' then 9 else 1 end,count
/*eName eIDx Count
123 0 1
123 45 1
123 46 1
123 55 1
123 56 1
123 47 2
123 34 3
132 46 1
132 55 1
1323 56 1
111 34 2
*/
SELECT eName,eIDx
FROM pEdu
WHERE eName LIKE '%1%'
GROUP BY eIDx, eName
order by COUNT(eName)
SELECT eName,CNT=COUNT(1)
FROM
(
SELECT DISTINCT eName,eIDx FROM #temp
WHERE eName LIKE '%1%'
) T
GROUP BY eName
ORDER BY COUNT(1) DESC, eName --如果个数相同,可以再按eName排序
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-08-13 16:11:53
-- Version:
-- Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64)
-- Jun 10 2013 20:09:10
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([eidx] int,[ename] varchar(6))
insert [huang]
select 1,'j' union all
select 1,'2' union all
select 0,'123' union all
select 34,'123' union all
select 34,'123' union all
select 34,'123' union all
select 34,'111' union all
select 34,'111' union all
select 45,'123' union all
select 46,'132' union all
select 46,'123' union all
select 47,'123' union all
select 47,'123' union all
select 50,'update' union all
select 54,'2' union all
select 55,'123' union all
select 55,'132' union all
select 56,'1323' union all
select 56,'123'
--------------开始查询--------------------------
SELECT a.*
FROM huang a INNER JOIN (
select ename,COUNT(1) [count]
from [huang]
WHERE ename IN ('123','111','132','1323')
GROUP BY ename) b ON a.ename=b.ename
ORDER BY b.count DESC
------------------结果----------------------------
/*
eidx ename
----------- ------
0 123
34 123
34 123
34 123
45 123
46 123
47 123
47 123
55 123
56 123
46 132
55 132
34 111
34 111
56 1323
*/
select y.eName
from
(select t.eName,count(1) 'ct'
from (SELECT eName,eIDx
FROM pEdu
WHERE eName LIKE '%1%'
GROUP BY eIDx,eName) t
group by t.eName
) y
order by ct desc
if OBJECT_ID('tempdb..#temp', 'u') is not null drop table #temp;
CREATE TABLE #temp(eid INT, eidx INT, ename VARCHAR(100), elevel INT, eflag INT)
insert #temp
select '2','1','j','1','1' union all
select '5','1','2','1','1' union ALL
select '9','0','123','0','0' union all
select '19','34','123','0','0' union all
select '20','34','123','0','0' union all
select '21','34','123','0','0' union all
select '22','34','111','0','0' union all
select '23','34','111','0','0' union all
select '24','45','123','0','0' union all
select '25','46','132','0','0' union all
select '26','46','123','0','0' union all
select '27','47','123','0','0' union all
select '28','47','123','0','0' union all
select '38','50','update','0','0' union all
select '48','54','2','0','0' union all
select '50','55','123','0','0' union all
select '51','55','132','0','0' union all
select '55','56','1323','0','0' union all
select '56','56','123','0','0'
SELECT eName,CNT=COUNT(1)
FROM
(
SELECT DISTINCT eName,eIDx FROM #temp
WHERE eName LIKE '%1%'
) T
GROUP BY eName
ORDER BY COUNT(1) DESC
/*
eName CNT
123 7
132 2
1323 1
111 1
*/