在一次MySQL练习中,我通过如下sql语句创建数据库表shop及插入相关语句:
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),(5,'xiaowy',20.4);
然后执行下列语句:
SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;
出现如下结果:
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0005 | xiaowy | 20.40 |
+---------+--------+-------+
发现会出现s2中的article,dealer,price有NULL值
执行如下语句:
select s1.article,s1.dealer,s1.price from shop s1 right join shop s2 on s1.price < s2.price where s1.article is null;
出现如下结果:
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| NULL | NULL | NULL |
+---------+--------+-------+
总有一行空值
执行如下语句:
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);
有一下结果:
+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
| 0001 | B | 3.99 |
| 0002 | A | 10.99 |
| 0003 | C | 1.69 |
| 0004 | D | 19.95 |
| 0005 | xiaowy | 20.40 |
+---------+--------+-------+
这里不知如何解释
MySQL帮助上说以上语句等价于:
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;