ORACLE SQL：需要对两个值求和，但一个可能不存在......这可能吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/299176/
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
ORACLE SQL: Need to sum two values, but one may not exist... Is this possible?
提问by
I've been running into some problems duplicating some old ASP files into some newer .NET 2.0 code. One of the tasks is to merge the 4-5 SQL statements into one. While I have done this and had some success in performance boosts, Oracle is a new bag for me. This problem however surpasses my own SQL skills as I haven't done this before.
我在将一些旧的 ASP 文件复制到一些较新的 .NET 2.0 代码中时遇到了一些问题。其中一项任务是将 4-5 条 SQL 语句合二为一。虽然我已经这样做并在性能提升方面取得了一些成功，但 Oracle 对我来说是一个新包。然而，这个问题超过了我自己的 SQL 技能，因为我以前没有这样做过。
Basically, I have a QUANTITY in one table from a bunch of Sales. Each sale has an integer value. Each sale also has an ITEM attached to it. Each ITEM has a CONVERSION factor as in if I sell 1 bag of something = 10 bundles of something. So, when I run a report and want to find out the end value, I need to take each sale and its quantity and multiple it by its conversion factor. Most are simple 1 to 1, so it's basically doing 25 * 1, 30 * 1 etc.
基本上，我在一堆 Sales 的一张桌子上有一个 QUANTITY。每个销售都有一个整数值。每笔销售还附有一个 ITEM。每个 ITEM 都有一个转换系数，就像我卖 1 袋东西 = 10 包东西一样。因此，当我运行报告并想要找出最终价值时，我需要获取每笔销售额及其数量并将其乘以其转换系数。大多数都是简单的 1 比 1，所以它基本上是 25 * 1、30 * 1 等。
My problem is that there are past sales in my records in which the ITEM has been removed from our system, therefore the CONVERSION factor does not exist. Those records get dropped from my query because the FACTOR is gone.
我的问题是我的记录中有过去的销售，其中 ITEM 已从我们的系统中删除，因此不存在 CONVERSION 因素。这些记录从我的查询中删除，因为 FACTOR 消失了。
SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code
FROM salesdetails rth, salesheader rsh
WHERE rsh.number = rth.number(+)
AND rsh.customer_code = '05'
AND rsh.r_code = '01'
AND rsh.location_code = '12'
AND rth.sale_code IN('ITEM07')
AND rth.c_code = 'WLMT'
AND rsh.year = '2008'
This is my first QUERY. If I add the conversion in:
这是我的第一个 QUERY。如果我将转换添加到：
SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code, rth.quantity * cf.conversion
FROM salesdetails rth, salesheader rsh, conversionfactor cf
WHERE rsh.number = rth.number(+)
AND rsh.customer_code = '05'
AND rsh.r_code = '01'
AND rsh.location_code = '12'
AND rth.sale_code IN('ITEM07')
AND rth.c_code = 'WLMT'
AND rsh.year = '2008'
AND cf.item_code = rth.item_code
and cf.code = '01'
and cf.loc_code = '00001'
This works to an extent. It lists all the same records, but it is missing any records in which the CONVERSION factor did not exist. Is there anyway I can still include those records where the FACTOR didn't exist in the second query, short from going line by line and doing the conversion that way.
这在一定程度上起作用。它列出了所有相同的记录，但缺少任何不存在 CONVERSION 因子的记录。无论如何，我仍然可以包含那些在第二个查询中不存在 FACTOR 的记录，而不是逐行进行转换并以这种方式进行转换。
回答by Charles Bretana
You could also use the Coalesce function, with a subquery...
您还可以使用 Coalesce 函数和子查询...
< Coalesce(arg0, arg1, arg2, ..... etc argN)
< Coalesce(arg0, arg1, arg2, ..... etc argN)
evaluates each argument in turn and returns the first one it finds that is not Null. >
依次计算每个参数并返回它找到的第一个不是 Null 的参数。>
Select D.CODE, D.NUMBER, D.QUANTITY, D.sale_code,
D.quantity * Coalesce((Select conversion
From conversionfactor
Where item_code = H.item_code
And code = '01'
And loc_code = '00001'), 1.0)
From salesheader H
Left Join salesdetails D
On D.Number = H.Number
Where H.customer_code = '05'
And H.r_code = '01'
And H.location_code = '12'
And H.year = '2008'
And D.sale_code In ('ITEM07')
And D.c_code = 'WLMT'
replace the 1.0 at the end of the subquery with whatever you want the "default" value of the conversion factor to be when the record is no longer in the conversionfactor table...
当记录不再在转换因子表中时，将子查询末尾的 1.0 替换为您希望转换因子的“默认”值...
回答by Tony Andrews
It doesn't make sense to outerjoin from rsh to rth but then innerjoin from rth to cf. Make them both outer joins. Also, the (+) symbol must appear on the "outer" side of all join conditions for rth:
从 rsh 到 rth 的外部连接然后从 rth 到 cf 的内部连接没有意义。使它们都是外连接。此外，(+) 符号必须出现在 rth 的所有连接条件的“外部”侧：
SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code,
rth.quantity * cf.conversion
FROM salesdetails rth, salesheader rsh, conversionfactor cf
WHERE rsh.number = rth.number(+)
AND rsh.customer_code = '05'
AND rsh.r_code = '01'
AND rsh.location_code = '12'
AND rth.sale_code (+) IN('ITEM07')
AND rth.c_code (+) = 'WLMT'
AND rsh.year = '2008'
AND cf.item_code (+) = rth.item_code
and cf.code (+) = '01'
and cf.loc_code (+) = '00001'
This is very old-fashioned syntax, by the way. Why not use the more modern:
顺便说一下，这是非常老式的语法。为什么不使用更现代的：
SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code,
rth.quantity * cf.conversion
FROM salesheader rsh
LEFT OUTER JOIN salesdetails rth
ON rsh.number = rth.number
AND rth.sale_code IN ('ITEM07')
AND rth.c_code = 'WLMT'
LEFT OUTER JOIN conversionfactor cf
ON cf.item_code = rth.item_code
AND cf.code = '01'
AND cf.loc_code = '00001'
WHERE rsh.customer_code = '05'
AND rsh.r_code = '01'
AND rsh.location_code = '12'
AND rsh.year = '2008'
回答by jlrolin
Actually, you're right about the OUTER JOIN. I was thinking there was a technicality in what it was doing, but I see exactly why it still wasn't including the value. The other two criteria needed to be (+) in order to include the NULL. I combined that with the COALESCE, and it seems to be working.
实际上，您对 OUTER JOIN 的看法是正确的。我在想它正在做的事情有技术性，但我明白为什么它仍然没有包括价值。其他两个标准需要是 (+) 才能包含 NULL。我将它与 COALESCE 结合起来，它似乎奏效了。
回答by Bert
I imagine you are losing records because you are attempting to multiply by a null value. Take a look at using NVL() with your conversion factor to default it to 1 or some other default value that works for you.
我想你正在丢失记录，因为你试图乘以一个空值。看看使用 NVL() 和您的转换因子将其默认为 1 或其他适合您的默认值。
Like,
喜欢，
SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code, rth.quantity * NVL(cf.conversion,1)
FROM salesdetails rth, salesheader rsh, conversionfactor cf
WHERE rsh.number = rth.number(+)
AND rsh.customer_code = '05'
AND rsh.r_code = '01'
AND rsh.location_code = '12'
AND rth.sale_code IN('ITEM07')
AND rth.c_code = 'WLMT'
AND rsh.year = '2008'
AND cf.item_code = rth.item_code
and cf.code = '01'
and cf.loc_code = '00001'
Double check the NVL syntax though. It's been a couple years since I worked with Oracle.
不过，请仔细检查 NVL 语法。我在 Oracle 工作已经有几年了。
回答by RudeUrm
If you deal with null values and numbers think about using the "coalesce" function where out of a list the first non null value is passed back as result.
如果您处理空值和数字，请考虑使用“coalesce”函数，其中列表中的第一个非空值作为结果传回。
回答by Dheer
Right now I can't access to Oracle. Check out this query:
现在我无法访问 Oracle。看看这个查询：
SELECT rth.CODE, rth.NUMBER, rth.QUANTITY, rth.sale_code, rth.quantity * cf.conversion
FROM salesdetails rth, salesheader rsh, conversionfactor cf
WHERE rsh.number = rth.number(+)
AND rsh.customer_code = '05'
AND rsh.r_code = '01'
AND rsh.location_code = '12'
AND rth.sale_code IN('ITEM07')
AND rth.c_code = 'WLMT'
AND rsh.year = '2008'
AND rth.item_code = cf.item_code (+)
and cf.code = '01'
and cf.loc_code = '00001'
回答by Dheer
Nope, I believe the OUTER JOIN doesn't work because the record is not actually there. It isn't null, it just doesn't exist anymore.
不，我相信 OUTER JOIN 不起作用，因为记录实际上并不存在。它不是空的，它只是不存在了。
回答by kristof
I think that should work. It assumes that conversion factor equals to 1 when it does not exist
我认为这应该有效。它假设转换因子不存在时等于 1
select
rth.code, rth.number, rth.quantity, rth.sale_code, rth.quantity * coalesce(cf.conversion,1)
from
salesdetails rth
inner join salesheader rsh on rsh.number = rth.number
left join conversionfactor cf on cf.item_code = rth.item_code
where
rsh.customer_code = '05'
and rsh.r_code = '01'
and rsh.location_code = '12'
and rth.sale_code in('item07')
and rth.c_code = 'wlmt'
and rsh.year = '2008'
and cf.code = '01'
and cf.loc_code = '00001'
回答by kristof
I believe that SQL assumes I have a null record. I don't, I have no record at all when the conversion does not exist. If I can find a way to return a null for those CONVERSIONs that do not exist in my DB, I could do that.
我相信 SQL 假设我有一个空记录。我没有，当转换不存在时，我根本没有记录。如果我能找到一种方法为那些在我的数据库中不存在的转换返回空值，我可以做到这一点。
