首页 新闻 赞助 找找看

关于group by 用法的 SQL问题

0
悬赏园豆:60 [待解决问题]
有数据表fruitTablt如下:
name       ProductPlace       price
苹果               中国                    1.1
苹果               日本                    1.0
苹果               美国                    2.0
橘子               中国                    3.0
香蕉               中国                    3.1
梨子               美国                    3.0
 

我想查一下每个国家最贵的水果是什么
SQL要怎么写?
一统的主页 一统 | 初学一级 | 园豆:97
提问于:2012-06-15 19:30
< >
分享
所有回答(7)
0

试下:

select a.name, a.productplace, a.price from fruitablt a inner join (select productplace, max(price) as price from fruitablt group by productplace) b on a.price = b.price and a.productplace = b.productplace
无之无 | 园豆:5095 (大侠五级) | 2012-06-15 19:46
0
select ft.name,ft.ProductPlace,ft.price from #fruitTablt ft where ft.price=(select MAX(price) from #fruitTablt ft2 where ft.ProductPlace=ft2.ProductPlace group by ProductPlace );
大 蜗 牛 | 园豆:167 (初学一级) | 2012-06-16 09:57
0

同意一楼。

tian_z | 园豆:158 (初学一级) | 2012-06-16 21:14
0

一楼已经很理想了, 还可以用 row_number(), partition by 

select * from(
    select *,  row_number = row_number() over (partition by productplace order by price desc)
    from fruitTable
    ) b 
where b.row_number = 1
gunsmoke | 园豆:3592 (老鸟四级) | 2012-06-18 07:20
0

select * from fruitTablt  where price in (

select a.price from

(select max(price) as price ,ProductPlace ,name  from fruitTablt   group by    ProductPlace ,name )a )

snjsunyan | 园豆:55 (初学一级) | 2012-06-18 10:55
0

感觉还是row_number 好用,对于排序的问题,建议使用row_number

Shannon | 园豆:611 (小虾三级) | 2012-07-02 17:21
0

WITH CTA AS (

SELECT '苹果' name ,'中国' ProductPlace,1.1 price UNION ALL
SELECT '苹果','日本',1.0 UNION ALL
SELECT '苹果','美国',2.0 UNION ALL
SELECT '橘子','中国',3.0 UNION ALL
SELECT '香蕉','中国',3.1 UNION ALL
SELECT '香','中国',3.1 UNION ALL
SELECT '梨子','美国',3.0
)
SELECT name,ProductPlace,price FROM (
SELECT * , MAX(price) OVER(PARTITION BY ProductPlace) expPri FROM CTA
) a WHERE price = exppri

这个可以查出并列最贵

snipersun | 园豆:204 (菜鸟二级) | 2012-07-05 11:39
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册