我正在寻找一种快速方法/方式,如果在上述产品上确实存在折扣,则可以使用辅助表中的折扣价,否则我想使用第一个表价。
我用##########标记的部分是我被卡住的地方...。该怎么办?
目前我所拥有的是:
SELECT
cat.`CategoryId` AS CategoryId
,prod.`ProductId` AS ProductId
,COUNT(prod.`ProductId`)AS product_count
,prod.`Price` AS price
,adj.`ProductPrice` AS Adj_Price
FROM ax_category cat
INNER JOIN xx_products prod USING(categoryid)
INNER JOIN xx_subcategory sc ON(prod.Subcategoryid = sc.SubCategoryId)
INNER JOIN xx_services s USING(productid)
LEFT JOIN xx_invoiceline il USING(serviceid)
LEFT JOIN xx_invoices i USING(invoiceid)
#LEFT JOIN xx_adjustments adj ON (s.`ProductId` = adj.`ProductId` AND s.`Quantity` = adj.`Quantity`)
LEFT JOIN xx_adjustments adj ON (s.`ProductId` = adj.`ProductId` AND s.`Quantity` = adj.`Quantity`)
WHERE DATE_FORMAT(s.DateEnd,'%Y-%m') = '2013-11'
############################################
IF EXISTS(
SELECT a.`ProductPrice` AS price
FROM xx_adjustments AS a
WHERE a.`ProductId` = '999'
#limit 1
)
############################################
GROUP BY prod.`ProductId`
您可以使用COALESCE()
:
SELECT
cat.`CategoryId` AS CategoryId
,prod.`ProductId` AS ProductId
,COUNT(prod.`ProductId`)AS product_count
,prod.`Price` AS price
,adj.`ProductPrice` AS Adj_Price
,COALESCE(adj.`ProductPrice`, prod.`Price`) AS discountedPrice
FROM ax_category cat
INNER JOIN xx_products prod USING(categoryid)
INNER JOIN xx_subcategory sc ON(prod.Subcategoryid = sc.SubCategoryId)
INNER JOIN xx_services s USING(productid)
LEFT JOIN xx_invoiceline il USING(serviceid)
LEFT JOIN xx_invoices i USING(invoiceid)
#LEFT JOIN xx_adjustments adj ON (s.`ProductId` = adj.`ProductId` AND s.`Quantity` = adj.`Quantity`)
LEFT JOIN xx_adjustments adj ON (s.`ProductId` = adj.`ProductId` AND s.`Quantity` = adj.`Quantity`)
WHERE DATE_FORMAT(s.DateEnd,'%Y-%m') = '2013-11'
GROUP BY prod.`ProductId`
在这里看到一个简单的例子。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句