Chinaunix首页 | 论坛 | 博客
  • 博客访问: 1148243
  • 博文数量: 286
  • 博客积分: 11086
  • 博客等级: 上将
  • 技术积分: 3245
  • 用 户 组: 普通用户
  • 注册时间: 2006-06-22 17:06
个人简介

闻鸡起舞

文章分类

全部博文(286)

文章存档

2020年(9)

2019年(9)

2018年(6)

2017年(10)

2016年(10)

2014年(3)

2013年(4)

2011年(12)

2010年(16)

2009年(14)

2008年(125)

2007年(48)

2006年(20)

我的朋友

分类: Mysql/postgreSQL

2020-10-30 16:18:15

SELECT article, dealer, price 

FROM shop s1
WHERE price=(SELECT MAX(s2.price)
                        
FROM shop s2 WHERE s1.article = s2.article)
ORDER BY article;The preceding example uses a correlated subquery, which can be inefficient . Other possibilities for solving the problem are to use an uncorrelated subquery in the FROM clause, a LEFT JOIN, or a common table expression with a window function.

Uncorrelated subquery:
SELECT s1.article, dealer, s1.price FROM shop s1 JOIN ( SELECT article, MAX(price) AS price FROM shop GROUP BY article) AS s2 ON s1.article = s2.article AND s1.price = s2.price ORDER BY article;
LEFT JOIN
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.article = s2.article AND s1.price < s2.price WHERE s2.article IS NULL ORDER BY s1.article;

The LEFT JOIN works on the basis that when s1.price is at its maximum value, there is no s2.price with a greater value and thus the corresponding s2.articlevalue is NULL. 

Common table expression with window fonction

WITH s1 AS
( SELECT article, dealer, price,
RANK() OVER (PARTITION BY article
ORDER BY price DESC
) AS `Rank`
FROM shop ) SELECT article, dealer, price FROM s1 WHERE `Rank` = 1 ORDER BY article;








阅读(111) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~