我正在执行一个查询，它给出了我想要的输出。 我被困在解决方案的最后一步。
查询
SELECT sum(`purchase`.`purchase_ammount`) as Purchase, `franchise`.`district_id`
FROM `purchase`
LEFT JOIN `franchise`
ON `purchase`.`f_id` = `franchise`.`id`
WHERE `franchise`.`district_id` in (
select `district`.`id`
from `district`
LEFT JOIN `region`
ON `district`.`region_id` = `region`.`id`
LEFT JOIN `regional_owner`
ON `regional_owner`.`r_id` = `region`.`id`
WHERE `regional_owner`.`email_id` = 'balajibarhate10@gmail.com'
)
AND purchase.purchase_date
BETWEEN Date_format(Curdate(), "%Y-%m-01") AND Curdate()
GROUP BY `franchise`.`district_id`
输出量
+----------+-------------+
| Purchase | district_id |
+----------+-------------+
| 18151 | 4 |
| 24603 | 6 |
+----------+-------------+
我想要的
如果district_id是4，我希望在购买第一行时增加5%，如果district_id不是4，我希望在购买第二行时增加2%。 我希望它在查询本身中。 我怎样才能做到这一点呢？
这应该行得通
SELECT (CASE WHEN district_id = 4 THEN Purchase * 1.05
ELSE Purchase * 1.02 END) PURCHASE_AMT, district_id
FROM
(
SELECT sum(`purchase`.`purchase_ammount`) as Purchase, `franchise`.`district_id`
FROM `purchase`
LEFT JOIN `franchise`
ON `purchase`.`f_id` = `franchise`.`id`
WHERE `franchise`.`district_id` in (
select `district`.`id`
from `district`
LEFT JOIN `region`
ON `district`.`region_id` = `region`.`id`
LEFT JOIN `regional_owner`
ON `regional_owner`.`r_id` = `region`.`id`
WHERE `regional_owner`.`email_id` = 'balajibarhate10@gmail.com'
)
AND purchase.purchase_date
BETWEEN Date_format(Curdate(), "%Y-%m-01") AND Curdate()
GROUP BY `franchise`.`district_id`
) A
可以使用case语句。
CASE
WHEN district_id = 4 THEN purchase_amount * 1.05
ELSE purchase_amount * 1.02
END AS AdjustedPrice
当然，您可以根据需要调整计算字段的指定名称。
