具有MAX聚合函数的CASE表达式Oracle

本文介绍了具有MAX聚合函数的CASE表达式Oracle的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

使用Oracle，我选择了title_id及其相关的发布月份，

Using Oracle, I have selected the title_id with its the associated month of publication with:

SELECT title_id, CASE EXTRACT(month FROM pubdate) WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar' WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'Jun' WHEN 7 THEN 'Jul' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep' WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' ELSE 'Dec' END MONTH FROM TITLES;

使用以下语句:

SELECT MAX(Most_Titles) FROM (SELECT count(title_id) Most_Titles, month FROM (SELECT title_id, extract(month FROM pubdate) AS MONTH FROM titles) GROUP BY month);

我能够确定出版图书数量最多的月份.

I was able to determine the month with the maximum number of books published.

是否有一种方法可以将两个语句合并在一起，以便我可以将当​​月的文本与最大标题数相关联?

Is there a way to join the two statements so that I can associate the month's text equivalent with the maximum number of titles?

推荐答案

为了将月份转换为字符串，我不会使用CASE语句，而只会使用TO_CHAR.而且，您可以使用分析功能对结果进行排名，以获取出版次数最多的月份.

In order to convert a month to a string, I wouldn't use a CASE statement, I'd just use a TO_CHAR. And you can use analytic functions to rank the results to get the month with the most books published.

SELECT num_titles, to_char( publication_month, 'Mon' ) month_str FROM (SELECT count(title_id) num_titles, trunc(pubdate, 'MM') publication_month, rank() over (order by count(title_id) desc) rnk FROM titles GROUP BY trunc(pubdate, 'MM')) WHERE rnk = 1

一些其他警告

• 如果有两个月的出版物数量最多，则此查询将返回两行.如果您希望Oracle任意选择一个，则可以使用row_number分析函数而不是rank.
• 如果表中的PUBDATE列在该书出版的当月第一天只有午夜的日期，则可以消除PUBDATE列上的trunc.

• If there are two months that are tied with the most publications, this query will return both rows. If you want Oracle to arbitrarily pick one, you can use the row_number analytic function rather than rank.
• If the PUBDATE column in your table only has dates of midnight on the first of the month where the book is published, you can eliminate the trunc on the PUBDATE column.