首先给叙述一下,有一个表,这个表命名为book,假设这个表只有三列 title(书名)、种类(category)、每本书的价格(retail),请问一下,那么销售种类最高的是什么种类,怎么求?
除了这种方法
select category from book
group by category
having max(retail)=
(select max(max(retail)) from book
group by category);
之外,为什么下面这种方法运行不出结果
select category from book
group by category
having max(retail)>=all(select max(retail) from book group by category);
求解决!
除了这种方法
select category from book
group by category
having max(retail)=
(select max(max(retail)) from book
group by category);
之外,为什么下面这种方法运行不出结果
select category from book
group by category
having max(retail)>=all(select max(retail) from book group by category);
求解决!
select book.category from book,
(select category,max(retail) retail from book group by category) t
where book.category=t.category;
group by category
having max(retail)>=all(select max(retail) from book group by category);
这种方法应该可以达到LZ要的结果吧。不过感觉是不是太麻烦了。
直接
select title,category,retail from (
select title,category,retail,row_number() over(order by retail desc) rn
from book) where rn='1'
或者
select * from (select title,category,retail from book order by retail desc)
where rownum=1
(select category,sum(retail) retail from book group by category)
group by category
having max(retail)=(select max(sum(retail)) from book group by category);至于楼主的第二个方法其实是没有问题的
select category from
(select category,sum(retail) retail from book group by category)
group by category
having max(retail)>=all(select max(sum(retail)) from book group by category);
(select category, sum(retail) retail from book group by category)
group by category
having max(retail)=
(select max(sum(retail)) from book group by category);楼主的方法二的思路没错,我修改了一下
select category from
(select category, sum(retail) retail from book group by category)
group by category
having max(retail)>=
all(select max(sum(retail)) from book group by category);