PostgreSQL查询需求：空ARTID字段替换为CheckInStatus值

要实现需求，你可以用PostgreSQL的COALESCE函数处理ARTID字段——这个函数会依次返回参数里第一个非空的值，正好能满足当ExternalId为空时用CheckInStatus填充的需求，同时移除原查询中单独的CheckInStatus列。

修改后的完整SQL如下：

SELECT "SortingAttempts"."StartedAt",
       COALESCE("ExternalId", "CheckInStatus") as "ARTID",
       "Barcode",
       "LoadStationId",
       "CheckInLocationId",
       "Weight",
       "Length",
       "Height",
       "Width"
FROM "SortingAttempts"
FULL OUTER JOIN "SortingAttemptBarcodes" 
ON "SortingAttempts"."Id" = "SortingAttemptBarcodes"."SortingAttemptId"
FULL OUTER JOIN "Packages"
ON "SortingAttempts"."PackageId" = "Packages"."Id"
WHERE "StartedAt" >= '${yesterday} 8:00'
AND "EndedAt" <= '${today} 8:00'
ORDER BY 1

说明 #

• COALESCE("ExternalId", "CheckInStatus")：如果ExternalId不为空，就用它的值作为ARTID；如果ExternalId是空值，就用CheckInStatus的值填充。
• 原查询中的CheckInStatus列已从SELECT列表中移除，仅保留处理后的ARTID字段。

内容的提问来源于stack exchange，提问作者pnrdr