不在结果中返回null值的SQL查询

I am really newbie with sqlserver.

我是sqlserver的新手。

I am querying some data from database and it is also returning null values with result.

我正在查询数据库中的一些数据,并且还返回带有结果的空值。

My query is;

我的疑问是;

select amount, distributorid from paymants

Some distributors has null values in amount column.

某些分销商在金额列中具有空值。

Please help!

Thanks

6 个解决方案

#1

You should use is null (or is not null) to filter null values.

您应该使用is null(或非null)来过滤空值。

select amount, distributorid 
from paymants
where amount is not null

If you need all records with null amount with another value (say, -1) you could use isnull or coalesce as below.

如果您需要所有具有空值和另一个值(例如-1)的记录,您可以使用isnull或coalesce,如下所示。

select coalesce(amount,-1) amount, distributorid 
from paymants

Or, if you need only amount null records, you could do;

或者,如果您只需要金额空记录,您可以这样做;

select amount, distributorid 
from paymants
where amount is null

#2

You can filter out columns with null values for amount using

您可以使用空值过滤掉具有空值的列

SELECT amount, distributorid FROM paymants WHERE amount IS NOT NULL

However, if you're expecting amounts for all rows, then it may be a good idea to ask why those nulls are being inserted in the first place.

但是,如果您期望所有行的数量,那么最好先询问为什么要插入这些空值。

When you define the table, if there's a column in which you don't want to allow null values, you can specify the NOT NULL property

定义表时,如果存在不希望允许空值的列,则可以指定NOT NULL属性

CREATE TABLE example ( someColumn INT NOT NULL )

#3

If you want the rows that has NULL values, but that the value should be 0 instead you can write:

如果您希望行具有NULL值,但该值应为0,则可以编写:

SELECT ISNULL(amount,0), distributorid FROM paymants

Link with info about ISNULL -> http://technet.microsoft.com/en-us/library/ms184325.aspx

链接有关ISNULL的信息 - > http://technet.microsoft.com/en-us/library/ms184325.aspx

and as pointed out in many other answers, if you don't want those rows to be returned at all, you can simply write:

正如许多其他答案中所指出的,如果你不想要返回那些行,你可以简单地写:

select amount, distributorid 
from paymants
where amount is not null

#4

If you have no filter (WHERE clause) then rows won't be filtered.

如果没有过滤器(WHERE子句),则不会过滤行。

SELECT amount, distributorid 
FROM paymants
WHERE amount IS NOT NULL

#5

SELECT amount, distributorid FROM paymants WHERE amount IS NOT NULL

SELECT amount,distributorid FROM paymants WHERE amount IS NOT NULL

#6

SOLUTION-1:

If you simply need to return values that are not null, then try the following:

如果您只需要返回非null值,请尝试以下操作:

select amount, distributorid 
from paymants
where amount is not null

SOLUTION-2:

If you not only need to return values that are not null, but also need to change the null values into empty strings or any other default value, then try the following:

如果您不仅需要返回非null值,还需要将空值更改为空字符串或任何其他默认值,请尝试以下操作:

Firstly, change all null values to empty strings or a default value, let's say, 0:

首先,将所有空值更改为空字符串或默认值,假设为0:

update paymants
set amount = ''
or amount = 0
where amount is null

Next, fetch the records:

接下来,获取记录:

select amount, distributorid
from paymants

Hope these solutions cleared your confusion.

希望这些解决方案能够让您感到困惑

#1