SQL Server中一个SQL语句问题
数据表MatchNumLog有字段：ID（自动编号），RuleID，MatchNum，Frequency（MatchNum的出现次数）
主键为RuleID和MatchNum,数据如下所示：
ID（自动编号） RuleID MatchNum Frequency
1 001 1 3
2 001 2 5
3 002 3 1
4 002 4 10
5 002 1 6
若要求各个RuleID出现次数最多（Frequency最大）的MatchNum，SQL语句该如何写？谢谢！
如上例要求的结果为：
2 001 2 5
4 002 4 10
[解决办法]
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[RuleID] nvarchar(3),[MatchNum] int,[Frequency] int)
Insert #T
select 1,N'001',1,3 union all
select 2,N'001',2,5 union all
select 3,N'002',3,1 union all
select 4,N'002',4,10 union all
select 5,N'002',1,6
Go
SELECT *
FROM
(Select *,COUNT(*)OVER(PARTITION BY [RuleID]) con,ROW_NUMBER()OVER(PARTITION BY [RuleID] ORDER BY [Frequency] desc) AS row from #T AS a )t
WHERE row=1
/*
IDRuleIDMatchNumFrequencyconrow
20012521
400241031
*/
select * from tb a
where id=(select top 1 id from tb where RuleID=a.RuleID order by MatchNum desc,Frequency desc)
select t.* from MatchNumLog t where not exists (select 1 from MatchNumLog where RuleID = t.RuleID and Frequency > t.Frequency)
[解决办法]
select * from MatchNumLog t where Frequency = (select max(Frequency) where RuleID = t.RuleID)
use Tempdb
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[RuleID] nvarchar(3),[MatchNum] int,[Frequency] int)
Insert #T
select 1,N'001',1,3 union all
select 2,N'001',2,5 union all
select 3,N'002',3,1 union all
select 4,N'002',4,10 union all
select 5,N'002',1,6 UNION ALL
select 6,N'002',1,10
--方法1:
Select * from #T a where not exists(select 1 from #T where [RuleID]=a.[RuleID] and [Frequency]>a.[Frequency] OR ([Frequency]=a.[Frequency] AND [ID]>a.[ID] ) )
--方法2:
select *
from (select *,row_number()over(partition by [RuleID] order by [Frequency] DESC,ID DESC ) as MaxID from #T a)T
where MaxID=1
/*
IDRuleIDMatchNumFrequency
200125
6002110
*/
SELECT *
FROM
(Select *,COUNT(*)OVER(PARTITION BY [RuleID]) con,ROW_NUMBER()OVER(PARTITION BY [RuleID] ORDER BY [Frequency] desc) AS row from #T AS a )t
WHERE row=1 AND con>1
