如何避免空列 [英] how to avoid null columns

问题描述

大家好，

如何避免select语句中的空列？

实际上在我的表中，单行中有多列，有些是null，有些不是一排的。那么如何在选择中避免它们。我想这样做因为我在图表中显示这些数据，所以当它选择空值时，图表突然变为0（零）。所以想避免这种情况。



提前谢谢。

Hi to all,
How to avoid null columns in select statement?
In fact in my table in a single row there are multiple columns, some are null and some are not in a single row. So how to avoid them in select. I want to do that because I am showing these data in chart, so when it selects a null value the chart suddenly goes to 0(zero). So want to avoid that.

Thanks in advance.

推荐答案

使用 COALESCE [ ^ ]函数。

Use COALESCE[^] function.

DECLARE @temp TABLE (Value1 INT, Value2 INT)
 
INSERT INTO @temp (Value1, Value2)
VALUES  (500, NULL)
INSERT INTO @temp (Value1, Value2)
VALUES  (1001, NULL)
INSERT INTO @temp (Value1, Value2)
VALUES  (NULL, 4002)
INSERT INTO @temp (Value1, Value2)
VALUES  (NULL, 8003)
INSERT INTO @temp (Value1, Value2)
VALUES  (8521, NULL)
INSERT INTO @temp (Value1, Value2)
VALUES (NULL, 2647)

SELECT COALESCE(Value1,0) AS Value1, COALESCE(Value2,0) AS Value2
FROM @temp

结果：

Result:

500	0
1001	0
0	4002
0	8003
8521	0
0	2647

DECLARE @temp TABLE (ID INT, Value1 VARCHAR(20), Value2 VARCHAR(20))

INSERT INTO @temp
        (ID, Value1, Value2)
VALUES
        (1, 'Rajan', NULL),
        (3, 'Vijayan', NULL),
        (1, NULL, 'Ravi'),
        (3, NULL, 'sudeep'),
        (2, 'kumar', NULL),
        (2, NULL, 'venkat')

SELECT DISTINCT
   ID,
   (SELECT Value1 FROM @temp t2 WHERE t2.ID = t.ID AND Value1 IS NOT NULL) AS 'Value1',
   (SELECT Value2 FROM @temp t2 WHERE t2.ID = t.ID AND Value2 IS NOT NULL) AS 'Value2'
FROM
   @temp t

请参阅这个例子..这可能对你有所帮助......试试这个......

Pls Refer this Example.. may this help You...Try this..

这篇关于如何避免空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！