select top 1 category_id from products order by product_price desc
select p.product_no from categories c,products p where c.category_id=p.category_id group by c.category_id having product_price=max(product_price)
你这语句过不了编译。group by c.category_id 就不能查询 select p.product_no 了吧,这是group 的语法。
我尝试了一会,sql语句估计难以实现,期待高手=。=
select products.* from products where product_price in (select max(product_price) from products group by products.category_id) mysql 测试通过,Orcale应该没问题
select category_id,max(product_price) from products group by category_id having category_id in (select category_id from products )
select max(product_price) maxprice from products where category_id in (select category_id from categories ) group by category_id;
select B.ProductID,B.CategoryID,B.UnitPrice,B.ReorderLevel from dbo.Products B inner join ( select MAX(C.UnitPrice) as UnitPrice, C.CategoryID as CategoryID from dbo.Products C group by C.CategoryID ) A on(A.CategoryID=B.CategoryID and A.UnitPrice=B.UnitPrice)没用你的数据库,用的是微软自带的。
--SQL SERVER 2005:select a.* from ( select ROW_NUMBER() OVER( PARTITION BY category_id ORDER BY product_price DESC ) AS row_num, * from test_products ) a where a.row_num = 1
貌似 orcale 也有像 SQL SERVER 2005 一样的 解析函数 可以取 row_num
最讨厌看到那种问 SQL 问题,不说明用的是什么数据库的帖子!
select * from products p,(select max(product_price),max(product_no) as psid from products where 1=1 group by category_id) ps where ps.psid=p.product_no看哈可以不!随便写的!应该没有问题的吧
这个是对的,高手噢,我修改后对了=。= select * from products p inner join ( select CATEGORY_ID,max(PRODUCT_PRICE) maxPrice from products group by CATEGORY_ID) a on p.category_id=a.CATEGORY_ID and p.product_price = a.maxPrice;
你这语句过不了编译。group by c.category_id 就不能查询 select p.product_no 了吧,这是group 的语法。
mysql 测试通过,Orcale应该没问题
select category_id,max(product_price) from products group by category_id having category_id in (select category_id from products )
(
select MAX(C.UnitPrice) as UnitPrice, C.CategoryID as CategoryID from dbo.Products C group by C.CategoryID
) A on(A.CategoryID=B.CategoryID and A.UnitPrice=B.UnitPrice)没用你的数据库,用的是微软自带的。
select ROW_NUMBER() OVER( PARTITION BY category_id ORDER BY product_price DESC ) AS row_num,
* from test_products
) a
where a.row_num = 1
select * from products p inner join ( select CATEGORY_ID,max(PRODUCT_PRICE) maxPrice from products group by CATEGORY_ID) a on p.category_id=a.CATEGORY_ID and p.product_price = a.maxPrice;