我想使用if语句更新表A中的价格，我已经尝试过了，但是不能只通过逐个调用id来进行计算，如何直接计算价格？
这是表：
表A
-------------------------
id | type | total | price
-------------------------
1 | a1 | 80 | [ ]
2 | a2 | 50 | [ ]
3 | a2 | 15 | [ ]
这是if语句
if($type=='a1')
{
$price1=1050;
$price2=1500;
$price3=2000;
if($total <= 10)
{
$price = $total*$price1;
}
elseif($total <= 20)
{
$a = 10;
$remtotal = $total-$a;
$price = ($a*$price1)+($remtotal*$price2);
}
elseif($total > 20)
{
$a = 10;
$b = 10;
$remtotal = ($total-$a)-$b;
$price = ($a*$price1)+($b*$price2)+($remtotal*$price3);
}
}
elseif($type=='a2')
{
$price1=2100;
$price2=3000;
$price3=4000;
if($total <= 10)
{
$price = $total*$price1;
}
elseif($total <= 20)
{
$a = 10;
$remtotal = $total-$a;
$price = ($a*$price1)+($remtotal*$price2);
}
elseif($total > 20)
{
$a = 10;
$b = 10;
$remtotal = ($total-$a)-$b;
$price = ($a*$price1)+($b*$price2)+($remtotal*$price3);
}
}
SELECT id,type,total,price FROM TABLE A WHERE id='id'
UPDATE TABLE A SET price='$price' WHERE id=id"
我可以打电话给id一一计算
我希望可以立即将其全部计算在内
预期结果
-------------------------
id | type | total | price
-------------------------
1 | a1 | 80 | [145500]
2 | a2 | 50 | [171000]
3 | a2 | 15 | [28500]
怎么做？
解决了
我创建2张桌子
TABLE A
-------------------------
id | type | total | price
-------------------------
1 | a1 | 80 | [ ? ]
2 | a2 | 50 | [ ? ]
3 | a2 | 15 | [ ? ]
TABLE B
| type | price1 | price2 | price3 |
| ---- | ------ | ------ | ------ |
| a1 | 1050 | 1500 | 2000 |
| a2 | 2100 | 3000 | 4000 |
| a2 | 2100 | 3000 | 4000 |
这是代码
UPDATE tabelA ta, tabelB tb
SET ta.price = ( CASE
WHEN ta.type = 'a1' AND ta.total <= 10
THEN ta.total * tb.price1
WHEN ta.type = 'a1' AND ta.total <= 20
THEN (10 * tb.price1) + ((ta.total - 10) * tb.price2)
WHEN ta.type = 'a1' AND ta.total > 20
THEN (10 * tb.price1) + (10 * tb.price2) + (((ta.total - 10) - 10) * tb.price3)
WHEN ta.type = 'a2' AND ta.total <= 10
THEN ta.total * tb.price1
WHEN ta.type = 'a2' AND ta.total <= 20
THEN (10 * tb.price1) + ((ta.total - 10) * tb.price2)
WHEN ta.type = 'a2' AND ta.total > 20
THEN (10 * tb.price1) + (10 * tb.price2) + (((ta.total - 10) - 10) * tb.price3)
END )
WHERE ta.type = tb.type
TABLE A
-------------------------
id | type | total | price
-------------------------
1 | a1 | 80 | [145500]
2 | a2 | 50 | [171000]
3 | a2 | 15 | [28500]
最佳答案
最简单的事情是制作价格表。
创建表/插入
CREATE TABLE prices (
`id` INTEGER,
`type` VARCHAR(2),
`price1` DOUBLE,
`price2` DOUBLE,
`price3` DOUBLE
);
INSERT INTo prices (id, type, price1, price2, price3) VALUES(1, 'a1', 1050, 1500, 2000);
然后使用
INNER JOIN与您的真实表并在CASE END子句中进行计算。询问
SELECT
*
, (
CASE
WHEN Table1.type = 'a1' AND Table1.total <= 10
THEN Table1.total * prices.price1
WHEN Table1.type = 'a1' AND Table1.total <= 20
THEN (10 * prices.price1) + ((Table1.total - 10) * prices.price2)
WHEN Table1.type = 'a1' AND Table1.total > 20
THEN (10 * prices.price1) + (10 * prices.price2) + (((Table1.total - 10) - 10) * prices.price3)
END
) AS price_total
FROM
Table1
INNER JOIN
prices
ON
Table1.type = prices.type
结果
| id | type | total | id | type | price1 | price2 | price3 | price_total |
| --- | ---- | ----- | --- | ---- | ------ | ------ | ------ | ----------- |
| 1 | a1 | 80 | 1 | a1 | 1050 | 1500 | 2000 | 145500 |
demo
对不起，我已经添加了我期望的输出
已编辑
我只是注意到类型
a1和a2的PHP计算代码相同，所以您在这里重复一遍。查询应该/可以没有类型。
询问
SELECT
*
, (
CASE
WHEN Table1.total <= 10
THEN Table1.total * prices.price1
WHEN Table1.total <= 20
THEN (10 * prices.price1) + ((Table1.total - 10) * prices.price2)
WHEN Table1.total > 20
THEN (10 * prices.price1) + (10 * prices.price2) + (((Table1.total - 10) - 10) * prices.price3)
END
) AS price_total
FROM
Table1
INNER JOIN
prices
ON
Table1.type = prices.type
结果
| id | type | total | id | type | price1 | price2 | price3 | price_total |
| --- | ---- | ----- | --- | ---- | ------ | ------ | ------ | ----------- |
| 1 | a1 | 80 | 1 | a1 | 1050 | 1500 | 2000 | 145500 |
| 2 | a2 | 50 | 1 | a2 | 2100 | 3000 | 4000 | 171000 |
| 3 | a2 | 15 | 1 | a2 | 2100 | 3000 | 4000 | 36000 |
demo
否则，您需要像这样在查询中重复它。
询问
SELECT
*
, (
CASE
WHEN Table1.type = 'a1' AND Table1.total <= 10
THEN Table1.total * prices.price1
WHEN Table1.type = 'a1' AND Table1.total <= 20
THEN (10 * prices.price1) + ((Table1.total - 10) * prices.price2)
WHEN Table1.type = 'a1' AND Table1.total > 20
THEN (10 * prices.price1) + (10 * prices.price2) + (((Table1.total - 10) - 10) * prices.price3)
WHEN Table1.type = 'a2' AND Table1.total <= 10
THEN Table1.total * prices.price1
WHEN Table1.type = 'a2' AND Table1.total <= 20
THEN (10 * prices.price1) + ((Table1.total - 10) * prices.price2)
WHEN Table1.type = 'a2' AND Table1.total > 20
THEN (10 * prices.price1) + (10 * prices.price2) + (((Table1.total - 10) - 10) * prices.price3)
END
) AS price_total
FROM
Table1
INNER JOIN
prices
ON
Table1.type = prices.type