短划线表示if语句和将其转换为PostgreSQL之间的访问

Question

我从一个访问数据库中得到了一个查询，我正试图用postgreSQL编写该数据库。我在将这行iif(isNull(table2.salaried), 0, table2.salaried) - iif(isNull(table1.paymet_due), 0, table1.paymet_due) AS salary_due翻译成PostgreSQL时遇到了问题。我首先感到困惑的是查询中的-。这是什么意思？如何将这一行转换为PostgreSQL？

SELECT
* 
FROM
(
SELECT
table1."customer_id",
iif(isNull(table2.salaried), 0, table2.salaried) - iif(isNull(table1.paymet_due), 0, table1.paymet_due) AS salary_due 
--coalesce(table1.paymet_due, 0) AS salary_due
FROM
(
SELECT
tbl_archive."customer_id",
SUM(tbl_sent."grand_total_paymet_due") AS paymet_due 
FROM
tbl_sent 
LEFT JOIN
tbl_archive 
ON tbl_sent."npi" = tbl_archive."cust_badge_numb" 
)
AS table1 
LEFT JOIN
(
SELECT
tbl_archive."customer_id",
SUM(wage."Amount") AS salaried 
FROM
wage 
LEFT JOIN
tbl_archive 
ON wage."sum_amount" = tbl_archive."cust_badge_numb" 
)
AS table2 
ON table1."customer_id" = table2."customer_id"
)
AS tbl_salaried

Answer 1

这是一个简单的减法：

• 如果table2.salaried为NULL，则值A为0，否则为table2.salaried。

• 如果table1.paymetdue为NULL，则值B为0，否则为table1.paymetdue。

查询中显示的值是值A减去值B。

所以正确的翻译应该是

coalesce(table2.salaried, 0) - coalesce(table1.paymet_due, 0)

Answer 2

您可以使用返回第一个非null参数的coalesce函数。

例如：

coalesce(field1,field2,0) - will return field 1 unless it's null. If field1 is null, it will return field2 unless it's null. If both field1 and field2 are null, it will return 0.

在您的情况下，您可以使用：coalesce(table2.salaried, 0)而不是iif(isNull(table2.salaried), 0, table2.salaried)

关于-，它实际上取决于字段是数字还是日期。

• 对于数字-您可以保持-原样
• 对于日期，你必须知道你想要提取的具体内容——日期之间的天数/月数等