子查询设为临时表技巧的使用
练习:
- 先查询得到每个类别中的最高价格,并把它设为一张临时表。
SELECT
cat_id ,
MAX(shop_price) AS `max`
FROM ecs_goods
GROUP BY cat_id
- 作多表的笛卡尔积,并从中利用where语句筛选出符合条件的行记录
SELECT temp.cat_id,goods_id,goods_name FROM(SELECT
cat_id ,
MAX(shop_price) AS `max`
FROM ecs_goods
GROUP BY cat_id)temp,ecs_goods
WHERE
ecs_goods.cat_id=temp.cat_id AND ecs_goods.shop_price = temp.max