查出普通价与会员价之间差价最大的商品

 mysql> desc product;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| name        | varchar(255) | YES  |     | NULL    |                |
| descr       | varchar(255) | YES  |     | NULL    |                |
| normalprice | double       | YES  |     | NULL    |                |
| memberprice | double       | YES  |     | NULL    |                |
| pdate       | datetime     | YES  |     | NULL    |                |
| categoryid  | int(11)      | YES  |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

 

//查出正常价与会员价之间的最大差价的商品
select id, name, normalprice-memberprice from product where normalprice-memberprice in (select min(normalprice-memberprice) from product);

 

再加一条:

//查出正常价与会员价之间差价按降序排列
select id, name, normalprice-memberprice from product order by normalprice-memberprice desc;