mysql_study_5

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));
# 插入数据
mysql> 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);

mysql> SELECT MAX(article) as article FROM shop;

# 显示price这一列最大值对应的行
mysql> SELECT article, dealer, price
    -> FROM   shop
    -> WHERE  price=(SELECT MAX(price) FROM shop);

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