Sql выберите только самые распространенные результаты

У меня есть таблица с идентификаторами и элементами, где иногда связанный элемент отличается от других элементов, связанных с тем же идентификатором. Мне нужен запрос, который выбирает наиболее распространенный элемент и присваивает его этому идентификатору.

Приведенный ниже запрос работает, но я надеюсь оптимизировать его, чтобы избежать объединения двух отдельных CTE в конце, и вместо этого иметь один гладкий оператор SELECT:

IF OBJECT_ID('tempdb..#Test') IS NOT NULL
    DROP TABLE #Test

CREATE TABLE #Test
(
    [ID] INT
    ,[Item] VARCHAR(20)
)

INSERT #Test
VALUES
(100, 'Apple'),
(100, 'Apple'),
(100, 'Apples'),
(200, 'Orange'),
(200, 'Orange'),
(200, 'Orange'),
(200, 'Oranges'),
(300, 'Grape');

WITH cteOne AS (SELECT
[ID]
,[Item]
,COUNT(*) [Count]
FROM #Test
GROUP BY [ID]
,[Item]
),
cteTwo AS (SELECT
[ID]
,MAX([Count]) [Max]
FROM cteOne
GROUP BY [ID])

SELECT
C1.[ID]
,C1.[Item]
FROM cteOne C1
INNER JOIN cteTwo C2 ON C2.[ID] = C1.[ID]
AND C2.[Max] = C1.[Count]
ORDER BY [ID]

Любая помощь приветствуется!

0
0
38
2
Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Ответ принят как подходящий

Вы можете попробовать top 1 with ties с row_number

select
    top 1 with ties [ID], [Item]
from (
    SELECT
        [ID], [Item], COUNT(*) [Count]
    FROM #Test
    GROUP BY [ID], [Item]
) t
order by row_number() over (partition by [ID] order by [Count] desc)

Это даже лучше:

;WITH 
cteOne AS (
    SELECT [ID],[Item] ,COUNT(*) [Count]
    FROM #Test
    GROUP BY [ID],[Item]
),
cteTwoo as (
    select *, ROW_NUMBER() over (partition by id order by count) idx
    from cteOne
)
select ID, Item
from cteTwoo
where idx = 1

CTE_Three не создается.

SQL_M 11.04.2018 12:48

Другие вопросы по теме