如何删除空值或如何将多列值合并为一个值 [英] How to remove null values or how to bring multiple columns value into one

问题描述

col1 col2 col3

100 null null

null 200 null

null null 300







输出

col1

100

200

$



col col2 col3

100 200 300

col1 col2 col3
100 null null
null 200 null
null null 300



output
col1
100
200
300

col col2 col3
100 200 300

推荐答案

这是一个你想要执行和获取的完全错误的方法或输出。一个表，更像是现实世界中的一个对象，有时它们的数据在没有特定于它的任何东西时为空。你的这个输出，似乎是某人的手臂，某人的腿和别人的心。这完全是一个糟糕的电话。



无论如何，要删除值为null的记录，可以使用以下命令，

That is a totally wrong method or output that you're trying to perform and get. A table, is more like an object in real-world, sometime their data is null when they don't have anything specific to it. This output of yours, seems like arm of someone, leg of someone and heart of someone else. That is totally a bad call.

Anyhow, to remove the records where the values are null, you can use this following command,

DELETE FROM table_name WHERE column_name IS NULL

以上将删除那些column_name为空值的记录。但是，不要尝试组合不同的记录并组成一条记录，只是因为他们将其他字段保留为空。这不是数据库如何 compact 。

The above would remove those records, where the column_name is a null value. But, do not try to combine different records and make up one record, only because they were having their other fields left as null. That is not how database can be compact.

我不确定你是否正在尝试做正确的事：如果有两行值，会发生什么在col1？我怀疑你需要重新考虑你的数据结构。



但是：

第一个输出版本：

I'm not sure you are trying to do the right thing: what happens if there are two rows with values in col1? I suspect you need to rethink your data structure a little.

But:
The first output version:

SELECT ISNULL(col1, 0)+ISNULL(col2, 0)+ISNULL(col3, 0) FROM MyTable

第二种：

The second:

SELECT SUM(col1), SUM(col2), SUM(col3) FROM MyTable

Don我不知道你想要什么，但另一种方法可能就像

Don't know what exactly you want but an alternative approach could be like

CREATE GLOBAL TEMPORARY TABLE TMP_TBLE
    (COL1 INT,
     COL2 INT,
     COL3 INT)
  ON COMMIT DELETE ROWS;

INSERT INTO TMP_TBLE(COL1, COL2, COL3)
SELECT 100, NULL, NULL
UNION ALL
SELECT NULL, 200, NULL
UNION ALL
SELECT NULL, NULL, 300

SELECT COALESCE(COL1, COL2, COL3)
FROm TMP_TBLE

SELECT  SUM(COL1) COL1, SUM(COL2) COL2, SUM(COL3) COL3
FROm TMP_TBLE

这篇关于如何删除空值或如何将多列值合并为一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！