问题:查询对规格商品的价格最小的记录?
表结构:表商品表store_prd,商品价格表:store_prd_price,商品表与价格表示一对多关系
注意:分组查询的结果集作为多条件的关系
sql实现:
SELECT
prd.`door_id` doorId,prd.`name` prdName,
prd.`id` prdId,
prd.`prd_type` prdType,
prd.`prd_expire_date` prdExpireDate,
p1.`discount_price` discountPrice,
p1.`price` costPrice,
p1.`discount` isDiscount,
prdPrice.`saleStock` saleCount,
prd.`discount_type` discountType
FROM
`store_prd_price` p1,
`store_prd` prd,
(SELECT
prd.`id` prdId,
MIN(price.`discount_price`) AS discountPrice
FROM
`store_prd_price` price,
`store_prd` prd
WHERE prd.`id` = price.`prd_id`
AND prd.`is_delete` = 0
AND price.`is_delete` = 0
GROUP BY prdId) t3,
(SELECT
SUM(priceN.`sale_stock`) saleStock,
prdN.`id`
FROM
`store_prd` prdN,
`store_prd_price` priceN
WHERE prdN.`id` = priceN.`prd_id`
GROUP BY prdN.`id`) prdPrice
WHERE prd.`id` = p1.`prd_id`
and t3.prdId = p1.`prd_id`
AND p1.`discount_price` = t3.discountPrice
GROUP BY prdId
order by prd.`create_time` desc