Hai Friends我对Toad for Oracle Query有疑问 [英] Hai Friends I Have A Question On Toad For Oracle Query

问题描述

我有一张桌子像



id = number phno = number

1 9999

2 8888



从表格中选择id，其中phno = 7777



如果表格中没有电话号码将显示空白

id



但我想要

0



thankx

解决方案

如果我正确理解了这个问题，你希望查询返回ID等于零的单行，如果记录是找不到。



但是，这不是SQL的设计方式。如果查询未找到任何匹配项，则结果集将返回为空，这实际上是很好的信息。这样你知道没有行符合条件。



因此，不要在结果数据中使用魔术数字，而是检查查询是否返回数据。或者


或许我错误地误解了这个问题？

Mikas答案是正确的，但可能不是你想要的。



但是有一个小问题，你已经在你的标签中声明你正在使用SQL Server 2008，而在主题行中它表示Oracle。



通常对于Oracle，您使用 NVL（）函数，但对于SQL Server，使用<$ c $可以实现相同的功能c> ISNULL（）。



因此，我将以 COALESCE（）。

 选择  COALESCE  （id， 0 ）来自  table  其中 phno = 7777 

但请考虑这样做相反，当你需要重构你正在编写的代码时，它可能会在将来产生重大影响




select count（id）其中phno = 7777

i have a table like

id=number phno=number
1 9999
2 8888

select id from table where phno=7777

if the phone number is not available in table it will show empty
id

but i want
0

thankx

解决方案

If I understand the question correctly, you would like the query to return a single row having ID equal to zero if a record is not found.

However, this is not how SQL is designed. In case the query doesn't find any matches the result set is returned as empty, which is actually good information. This way you know that no rows satisfied the condition.

So instead of using 'magic numbers' in the result data check if the query returns data or not.

Or perhaps I misunderstood the question incorrectly?

Mikas answer is the correct one but probably not what you wanted.

There's a slight problem though, you have stated in your tags that you're using SQL Server 2008, while in the subject line it says Oracle.

And normally for Oracle you'd use the NVL() function, but for SQL Server the same functionality would be achieved with ISNULL().

So to be generic I will answer with COALESCE().

select COALESCE(id,0) from table where phno=7777

But please do consider doing it the proper way instead, it might make a big difference in the future when you need to refactor the code you're writing at the moment

select count(id) where phno=7777

这篇关于Hai Friends我对Toad for Oracle Query有疑问的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！