Oracle Sql获取对于另一列的每个不同值出现的值最多的值

So lets say that i have this table

所以,让我说我有这张桌子

language | offer
chinese  |   1
chinese  |   1
english  |   1
spanish  |   2
spanish  |   2
italian  |   2
french   |   3

and I want the languange that appears most times for each different offer,like this

而且我希望每个不同的报价出现的languange就像这样

language | offer
chinese  |   1
spanish  |   2
french   |   3

How do I do this in oracle sql?

我如何在oracle sql中执行此操作?

3 个解决方案

#1

This is one way to do it using common table expressions.

这是使用公用表表达式执行此操作的一种方法。

SQL Fiddle

In the first cte, you calculate the counts grouped by offer and language. In the next cte, use rank or row_number to assign 1 to the offer with the highest language count. Finally, select from the 1st ranked rows.

在第一个cte中,您可以计算按要约和语言分组的计数。在下一个cte中,使用rank或row_number为具有最高语言计数的商品分配1。最后,从排名第一的行中选择。

with counts as(
select offer, language, count(*) cnt
from tablename
group by offer, language)
,ranking as
(select rank() over(partition by offer order by cnt desc) rnk
, c.*
from counts c)
select language, offer
from ranking 
where rnk = 1

Alternate approach without window functions:

没有窗口函数的替代方法:

with counts as (
select offer, language, count(*) cnt
from tablename
group by offer, language)
,maxcount as (select offer, max(cnt) mxcnt from counts group by offer)
select c.language, m.offer
from counts c
join maxcount m on m.offer = c.offer and m.mxcnt = c.cnt

#2

You can use row_number in oracle for your output.

您可以在oracle中使用row_number作为输出。

 SELECT LANGUAGE
        ,offer
    FROM (
        SELECT LANGUAGE
            ,offer
            ,row_number() OVER (
                PARTITION BY offer ORDER BY count(offer) DESC
                ) AS rno
        FROM table1
        GROUP BY LANGUAGE
            ,offer
        )
    WHERE rno = 1

#3

This one is a standard way to solve the problem:

这是解决问题的标准方法:

select offer, language
from tablename t
group by offer, language
having not exists
(
  select 1
  from tablename
  where offer = t.offer
  group by language
  having count(language) > count(t.language)
)

In the main query you group each language per offer and check in the subquery that no other language group from the same offer contains more occurrences.

在主查询中,您将每个商品的每种语言分组,并在子查询中检查来自同一商品的其他语言组是否包含更多实例。

#1