一.查询语句
记录一下学习mysql时碰到的一个很难懂的mysql语句;
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
二.数据源
表结构及其中的数据如下:
CREATE TABLE shop (
article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
dealer CHAR(20) DEFAULT '' NOT NULL,
price DOUBLE(16,2) DEFAULT '0.00' NOT NULL,
PRIMARY KEY(article, dealer)
);
INSERT INTO shop VALUES
(1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),
(3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
表数据如下:
三.查询需求
需求:查询价格最大的整条记录!
四.查询结果
- 子查询
SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);
-
分页最大值查询
SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
-
JOIN语法查询
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
五.用法解释
SELECT s1.article, s1.dealer, s1.price FROM shop s1 LEFT JOIN shop s2 ON s1.price < s2.price WHERE s2.article IS NULL;
经查询该语句为join语法的使用,意思是在left join语法基础上,当s1.price的最大值,在s2.price中没有比它更大的值的时候,s2中该行的值就是null.
个人英语能力有限,只能理解这么多,详细解释如下:
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 the s2
rows values will be NULL
.