如何在select all query中检查空值？

I have a situation in which I need to select all columns from a table and the query is something like:

我有一种情况,我需要从表中选择所有列,查询类似于:

select     sr.*,
           cs.subjectid,
           cs.priority,
           cs.subjectname   
from       sometable sr,   
           otherTable cs   
where      sr.col1='E2011FT0'   
  and      sr.col2='5'     
  and      sr.col3=  '66018'  
  and      cs.col1=sr.col1
order by   cs.col2;

How do I check for null values in the sr.* columns and replace them with a different value? There are a lot of columns in the sometable table and I'm trying to avoid listing them all explicitly.

如何检查sr。*列中的空值并用不同的值替换它们?在sometable表中有很多列,我试图避免明确地列出它们。

2 个解决方案

#1

I don't believe what you're after is possible in either standard SQL or MySQL (well, not directly on the table, but see below for a possible solution).

我不相信你在标准SQL或MySQL中可能会得到什么(好吧,不是直接在桌面上,但是请参阅下面的可能的解决方案)。

There's no option to perform a common column manipulation on sr.*, you will have to do the columns individually, something like:

没有选项在sr。*上执行常见的列操作,您必须单独执行列,例如:

select sr.column1,
       sr.column2,
       coalesce (sr.column3, 0),
       sr.column4,
       cs.subjectid ...

One possibility, though it's a bit kludgy, is to provide a view over the actual table where each column of the view is similarly named but defined as a coalesce on the equivalent table column. By that I mean something like:

一种可能性,虽然它有点kludgy,是提供实际表的视图,其中视图的每列被类似地命名,但在等效表列上定义为合并。我的意思是:

create view sometableview (column1, column2, column3, column4)
    as select column1, column2, coalesce (column3, 0), column4
        from sometable;

select srv.*,
       cs.subjectid, ...
:
from sometableview srv,   
     otherTable cs
where ...

That won't make it any faster but it'll keep your query simpler, which seems to be what you're after. I'm not entirely certain why that's a requirement since queries tend to be set up once and changed rarely so it's unusual to worry about their length. But I'm going to assume you have a good reason for the requirement until informed otherwise :-)

这不会让它变得更快,但它会让你的查询更简单,这似乎是你所追求的。我不完全确定为什么这是一个要求,因为查询往往设置一次并且很少改变,因此担心它们的长度是不寻常的。但是我会假设你有充分的理由要求,直到另有通知:-)

#2

Use SELECT with the COUNT function to count all rows of a given column, including the null values, use the ISNULL function. The ISNULL function can replace the null value with a valid value. With the IsNULL function, NULL is replaced with 0.

使用SELECT和COUNT函数来计算给定列的所有行,包括空值,使用ISNULL函数。 ISNULL函数可以用有效值替换空值。使用IsNULL函数,NULL将替换为0。

CREATE TABLE tabcount (
  pkey int IDENTITY NOT NULL CONSTRAINT pk_tabcount PRIMARY KEY,
  col1 int NULL)
GO

INSERT tabcount (col1) VALUES (10)
GO
INSERT tabcount (col1) VALUES (15)
GO
INSERT tabcount (col1) VALUES (20)
GO
INSERT tabcount (col1) VALUES (NULL)
GO

SELECT AVG(col1) A1,                                 (1)
       AVG(ISNULL(col1,0)) A2,                       (2)
       COUNT(col1) C1,                               (3)
       COUNT(ISNULL(col1,0)) C2,                     (4)
       COUNT(*) C3                                   (5)
FROM tabcount
GO






A1          A2          C1          C2          C3
----------- ----------- ----------- ----------- ---
15          11          3           4           4

--Null value is eliminated by an aggregate or other SET operation.

- 通过聚合或其他SET操作消除空值。

(1) - NULL values are eliminated. (2) - With the IsNULL function, NULL is replaced with 0. (3) - NULL values are eliminated. (4) - With the IsNULL function, NULL is replaced with 0. (5) - COUNT(*) calculates all rows, even those with NULLs.

(1) - 消除NULL值。 (2) - 使用IsNULL函数,NULL替换为0.(3) - 消除NULL值。 (4) - 使用IsNULL函数,NULL替换为0.(5) - COUNT(*)计算所有行,即使是那些具有NULL的行。

#1