创建表
列的最大值
或者
任务:每项物品的的最高价格是多少?
对每项物品,找出最贵价格的物品的经销商。
mysql> 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));
列的最大值
SELECT MAX(article) AS article FROM shop;
找出最贵物品的编号、销售商和价格。
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;
任务:每项物品的的最高价格是多少?
SELECT article, MAX(price) AS price
FROM shop
GROUP BY article
对每项物品,找出最贵价格的物品的经销商。
ELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);