假设我有
SELECT `Type`, `Originating Country Code`,`Originating Currency Code`, SUM(`Principal Amount`) as Total
FROM `colab`
WHERE (`Destination Country Code`='PH'
AND `Destination Currency Code`='PHP'
AND `Type` = 'PAID') || (`Destination Country Code`='PH'
AND `Destination Currency Code`='USD'
AND `Type`= 'PAID')
GROUP BY `Type`
工作正常并返回true,请参见下面的
country_code | Currency_Code | Type | Principal
=======================================================
| PH | PHP | PAID | 200
=======================================================
我的问题是我想把货币的总本金与美元价值分开。请查看我的数据库中的记录
country_code | Currency_Code | Type | Principal
=======================================================
| PH | PHP | PAID | 100
| PH | USD | PAID | 100
=======================================================
试试这个查询:
SELECT `Currency_Code`, SUM(`Principal Amount`) as Total
FROM `table`
GROUP BY `Currency_Code`
您可以这样使用GROUP BY
:
SELECT `Currency_Code`, SUM(`Principal Amount`) as Total
FROM `table`
WHERE `Country Code`='PH'
AND `Type` LIKE '%W%'
GROUP BY `Currency_Code`
如果您想添加更多条件,只需使用AND
或OR
在WHERE
子句中添加更多部分,例如:
SELECT `Currency_Code`, SUM(`Principal Amount`) as Total
FROM `table`
WHERE `Country Code`='PH'
AND `Type` LIKE '%W%'
AND `Currency_Code` IN('USD', 'CAD', 'YEN')
AND `Principal_Amount` > 10
GROUP BY `Currency_Code`
您需要使用GROUP BY
。
SELECT `Currency Code`, SUM(`Principal Amount`) as Total
FROM `table`
WHERE `Country Code`='PH'
AND `Type` LIKE '%W%'
GROUP BY `Currency Code`
你不能根据你的要求改变查询吗??我的意思是,从你的预期结果我推断,你需要基于类型或国家代码的结果,所以从查询中删除"AND Currency Code
='PHP'"会给你预期的结果。
将您的条件拆分为子查询并针对它进行连接。你想加多少规则就加多少。
SELECT `Country Code`, `Currency Code`, `Type`,
SUM(`Principal Amount`) as Total
FROM `table`
JOIN (
SELECT 'PH' as a, 'PHP' as b, '%W%' as c
UNION SELECT 'PH', 'USD', '%W%'
UNION SELECT 'PY', 'PYC', '%H%'
) AS criteria
ON (`table`.`Country Code` = criteria.a and
`table`.`Currency Code` = criteria.b and
`table`.`Type` like criteria.c)
GROUP BY `Country Code`, `Currency Code`, `Type`