SQL Server:如何从每个组获得最大搜索次数

时间：2023-02-05 01:42:45

I have a below dataset and I am trying to get max occurrences of CID per each OID.

我有一个下面的数据集，我正在尝试获取每个OID最多的CID。

IF OBJECT_ID('TEMPDB..#SS',N'U') IS NOT NULL 
DROP TABLE #SS
GO
SELECT * INTO #SS FROM (
SELECT 1 AS OID,501 AS CID
UNION ALL
SELECT 1 AS OID,503 AS CID
UNION ALL
SELECT 1 AS OID,502 AS CID
UNION ALL
SELECT 1 AS OID,501 AS CID
UNION ALL
SELECT 1 AS OID,501 AS CID
UNION ALL
SELECT 2 AS OID,502 AS CID
UNION ALL
SELECT 2 AS OID,502 AS CID
UNION ALL
SELECT 2 AS OID,502 AS CID
UNION ALL
SELECT 2 AS OID,501 AS CID
UNION ALL
SELECT 1 AS OID,503 AS CID
)A
GO

In above sample dataset, I need to get 2 CID per each OID which occurred maximum times. The expected result could be:

在上面的示例数据集中，我需要获得每个发生最大次数的OID 2 CID。预期的结果可能是:

OID CID
1   501
1   503
2   501
2   502

This cannot be a duplicate to SQL Select top frequent records because I need this sub-queries and SQL-Server would not accept ORDER BY in sub-query and eventually I need a ranking function to solve my issue. Ranking function was not used in the link provided.

这不能是对SQL Select top频繁记录的重复，因为我需要这个子查询，而且SQL- server在子查询中不会接受ORDER，最终我需要一个排名函数来解决我的问题。在提供的链接中没有使用排名功能。

1 个解决方案

#1

3  

You can do this pretty easily utilizing ROW_NUMBER. Thanks for Tab Alleman for the clarification on your requirements.

使用ROW_NUMBER可以很容易地做到这一点。感谢Tab Alleman关于你的要求的澄清。

select *
from
(
    select *
        , RowNum = ROW_NUMBER() over (partition by OID order by count(*) desc)
    from #SS
    group by OID
        , CID
) x
where x.RowNum <= 2
order by x.OID
    , x.CID

#1

3  

You can do this pretty easily utilizing ROW_NUMBER. Thanks for Tab Alleman for the clarification on your requirements.

使用ROW_NUMBER可以很容易地做到这一点。感谢Tab Alleman关于你的要求的澄清。

select *
from
(
    select *
        , RowNum = ROW_NUMBER() over (partition by OID order by count(*) desc)
    from #SS
    group by OID
        , CID
) x
where x.RowNum <= 2
order by x.OID
    , x.CID
标签：

相关文章