SELECT id, amount FROM report
我需要amount
为amount
如果report.type='P'
和-amount
如果report.type='N'
。 如何将其添加到上述查询中?
#1楼
select
id,
case
when report_type = 'P'
then amount
when report_type = 'N'
then -amount
else null
end
from table
#2楼
SELECT CompanyName,
CASE WHEN Country IN ('USA', 'Canada') THEN 'North America'
WHEN Country = 'Brazil' THEN 'South America'
ELSE 'Europe' END AS Continent
FROM Suppliers
ORDER BY CompanyName;
#3楼
SELECT id, amount
FROM report
WHERE type='P'
UNION
SELECT id, (amount * -1) AS amount
FROM report
WHERE type = 'N'
ORDER BY id;
#4楼
最简单的方法是使用IF() 。 是的Mysql允许你做条件逻辑。 IF函数需要3个参数条件,真实的结果,错误的结果。
所以逻辑是
if report.type = 'p'
amount = amount
else
amount = -1*amount
SQL
SELECT
id, IF(report.type = 'P', abs(amount), -1*abs(amount)) as amount
FROM report
如果所有不是+ ve,你可以跳过abs()
#5楼
你也可以尝试一下
Select id , IF(type=='p', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount from table
#6楼
我们来试试这个:
SELECT
id , IF(report.type = 'p', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount
FROM report
#7楼
使用case
陈述:
select id,
case report.type
when 'P' then amount
when 'N' then -amount
end as amount
from
`report`
#8楼
SELECT id,
IF(type = 'P', amount, amount * -1) as amount
FROM report
请参阅http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html 。
此外,您可以在条件为null时进行处理。 在空金额的情况下:
SELECT id,
IF(type = 'P', IFNULL(amount,0), IFNULL(amount,0) * -1) as amount
FROM report
部分IFNULL(amount,0)
表示金额不为空时返还金额,否则返回0 。