postgresql postgres 中的 IIF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45919937/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IIF in postgres
提问by Daniel L. VanDenBosch
I am attempting to convert an MS-Access query to a postgres statement so I can use it in SSRS. Seems to work great except for the IIF statement.
我正在尝试将 MS-Access 查询转换为 postgres 语句，以便我可以在 SSRS 中使用它。除了 IIF 语句外，似乎效果很好。
SELECT labor_sort_1.ncm_id
,IIf(labor_sort_1.sortby_employeeid = 3721
, ((labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 29 * labor_sort_1.number_of_ops)
, IIf(labor_sort_1.sortby_employeeid = 3722
, ((labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops)
, IIf(labor_sort_1.sortby_employeeid = 3755, ((labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops)
, ((labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 17 * labor_sort_1.number_of_ops)))) AS labor_cost
FROM ...
it returns the following message
function iif(boolean, interval, interval) does not existHow would I solve this problem?
它返回以下消息
function iif(boolean, interval, interval) does not exist我将如何解决这个问题？
采纳答案by Peter Krauss
Same as @Daniel's answer, but generalizing to any datatype.
与@Daniel 的回答相同，但适用于任何数据类型。
CREATE or replace FUNCTION iIF(
condition boolean, -- IF condition
true_result anyelement, -- THEN
false_result anyelement -- ELSE
) RETURNS anyelement AS $f$
SELECT CASE WHEN condition THEN true_result ELSE false_result END
$f$ LANGUAGE SQL IMMUTABLE;
SELECT iif(0=1,1,2);
SELECT iif(0=0,'Hello'::text,'Bye'); -- need to say that string is text.
Good when you are looking for a public-snippets-library.
当您正在寻找public-snippets-library时很好。
回答by JNevill
You'll need to switch the logic over to a CASEstatement. CASE statements are standard for most RDBMS's so it's worth learning. In your case (pun intended) it would translate to:
您需要将逻辑切换到CASE语句。CASE 语句是大多数 RDBMS 的标准，因此值得学习。在你的情况下（双关语），它会转化为：
CASE
WHEN labor_sort_1.sortby_employeeid = 3721
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 29 * labor_sort_1.number_of_ops
WHEN labor_sort_1.sortby_employeeid = 3722
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops
WHEN labor_sort_1.sortby_employeeid = 3755
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops
ELSE
(labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 17 * labor_sort_1.number_of_ops)
END AS labor_cost
Which is a lot cleaner looking since you don't have to monkey with nested iif()issues and all that and should you need to add more employeeid's to the list of hard-coded labor costs, it's no biggie.
这看起来更清晰，因为您不必处理嵌套iif()问题等等，并且如果您需要将更多员工 ID 添加到硬编码人工成本列表中，这没什么大不了的。
You might also find it advantageous to us the INcondition here to so you only need two WHENclauses:
您可能还会发现IN这里的条件对我们有利，因此您只需要两个WHEN子句：
CASE
WHEN labor_sort_1.sortby_employeeid = 3721
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 29 * labor_sort_1.number_of_ops
WHEN labor_sort_1.sortby_employeeid IN (3722, 3755)
THEN (labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 24 * labor_sort_1.number_of_ops
ELSE
(labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime) * 24 * 17 * labor_sort_1.number_of_ops)
END AS labor_cost
Also, you could move the CASE statement into the equation to just put out whatever number you wish to multiply by:
此外，您可以将 CASE 语句移动到等式中，以输出您希望乘以的任何数字：
(labor_sort_1.MaxUpdatedAt - labor_sort_1.MinNCMScanTime)
* 24
* CASE
WHEN labor_sort_1.sortby_employeeid = 3721 THEN 29
WHEN labor_sort_1.sortby_employeeid IN (3722,3755) THEN 24
ELSE 17
END
* labor_sort_1.number_of_ops AS labor_cost
回答by Daniel L. VanDenBosch
I know this has been sitting around for a while but another option is to create a user defined function. If you happen to stumble upon this in your internet searches, this may be a solution for you.
我知道这已经有一段时间了，但另一个选择是创建一个用户定义的函数。如果您在互联网搜索中偶然发现了这一点，这可能是您的解决方案。
CREATE FUNCTION IIF(
condition boolean, true_result TEXT, false_result TEXT
) RETURNS TEXT LANGUAGE plpgsql AS $$
BEGIN
IF condition THEN
RETURN true_result;
ELSE
RETURN false_result;
END IF;
END
$$;
SELECT IIF(2=1,'dan the man','false foobar');
Should text not tickle your fancy then try function overloading
文本不应该引起您的兴趣，然后尝试函数重载
