如何将Excel函数转换为Sql函数 [英] How Do I Convert Excel Function As Sql Function

问题描述

我希望此功能为（= IFERROR（IF（D4 / C4 = 1，（1-0.0000034226），IF（D4> C4，错误数据，D4 / C4）），NA））SQl查询

i want this function as (=IFERROR(IF(D4/C4=1,(1-0.0000034226),IF(D4>C4,"Wrong Data",D4/C4)),"NA")) SQl Query

推荐答案

select

case when c4=0 then 'NA'
when  (D4/C4)=1 then (1-0.0000034226)
when (D4>C4) then 'Wrong Data'
else
(D4/C4) end

首先，与Excel不同，您的SQL需要返回单一类型的数据（Excel具有常规列类型，允许您输入单词或数字，SQL mus有一个或另一个）



所以在这种情况下，一切都需要转换为Varchar或类似的



其次，Excel中的 IFERROR 函数允许您捕获错误情况并具有默认值 - 在这种情况下为NA。在SQL中，您需要自己处理该错误。据我所知，在这种情况下唯一会产生错误的是C4是0.有趣的是，如果C4为空，它也不会出错，但也没有得到任何明智的结果，所以这也需要照顾。



以下SQL将执行此操作：

Firstly, unlike Excel your SQL will need to return data of a single type (Excel has a 'General' column type that will allow you to put in words OR numbers, SQL must have one or the other)

So everything in this case will need to be converted to a Varchar or similar

Secondly, the IFERROR function in Excel allows you capture an error situation and have a default value - NA in this case. In SQL you will need to cater for that error yourself. As far as I can tell the only thing that would produce an error in this case is if C4 is 0. Interestingly it doesn't error if C4 is null but nor do you get any sensible result, so this needs to be catered for too.

The following SQL will do it:

SELECT result = CASE    WHEN ISNULL(C4, 0) = 0 THEN 'NA'
                        WHEN D4/C4 = 1 THEN CAST(1-0.0000034226 AS VARCHAR)
                        WHEN D4 <= C4 THEN CAST(D4/C4 AS VARCHAR)
                        ELSE 'Wrong Data'
                END

其中结果的类型为 varchar



我使用以下数据对此进行了测试（评论后的值是预期结果）

where result will be of type varchar

I tested this with the following data (values after the comments are the expected results)

CREATE TABLE CP3
(
    ID INT IDENTITY(1,1),
    D4 FLOAT,
    C4 FLOAT
)

INSERT INTO CP3 VALUES(3, 1) -- Wrong data
INSERT INTO CP3 VALUES(2, 2) -- 1-0.0000034226
INSERT INTO CP3 VALUES(2, 3) -- 0.66667
INSERT INTO CP3 VALUES(2, 0) -- 'NA
INSERT INTO CP3 VALUES(2,NULL) -- 'NA'

并得到以下结果：

test	result
1	Wrong Data
2	0.9999965774
3	0.666667
4	NA
5	NA

但值得重复 - 那些数字实际上是varchars

but it's worth repeating - those "numbers" are actually varchars

这篇关于如何将Excel函数转换为Sql函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！