mysql查询案例

一.查询语句

记录一下学习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);

表数据如下:

三.查询需求

需求:查询价格最大的整条记录!

四.查询结果

1. 子查询

SELECT article, dealer, price FROM shop WHERE price=(SELECT MAX(price) FROM shop);

2. 分页最大值查询

   SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1;
   

3. 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.

详细解释点这里!

JOIN用法详解