SQL最小值和计数

Question

我试图实现的是显示类别中书籍的计数

其中计数(类别)>Count(Category)中的最小数量

示例；

如果类别是

A = 1
b = 2
c = 3
D = 1
E = 1

我正在尝试使用MIN.显示>1的类别

我得到的错误是：

ORA-00935：组函数嵌套太深

SELECT Count(Category),
Category 
From Books
Having Count((Category) > MIN(Count(Category)
Group BY Category  

Answer 1

正在寻找类似的东西：

Select Count(Category),
Category 
From Books 
Group BY Category 
Having Count(Category) > (Select Min(cnt)
from (Select Count(Category) AS cnt
From Books
Group By Category))

这将在所有类别中选择计数大于最小计数的所有类别。

Answer 2

另一种方法是从最低值开始计数rank(平局被分配相同的秩)，并且只选择秩大于1:的行

select * from (
select count(*) cnt, category,
rank() over (order by count(*)) rn
from books
group by category  
) t where rn > 1

Answer 3

这应该做到：

SELECT Category, CategoryCount from
(SELECT rownum as r, Category, Count(*) as CategoryCount
From Books 
Group BY Category
Order by CategoryCount asc)
Where r > 1;

Answer 4

Giorgos的答案是正确的。它可以使用子查询分解进行重新排列(并使效率略高)：

with ctg (category, categ_count) as (
select   category, count(*)
from     books
group by category
)
select category, categ_count
from   ctg
where  categ_count > (select min(categ_count) from ctg);