如何避免空列 [英] 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屋!
查看全文