oracle 将两行合并为一行，同时替换空值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3263126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Merging two rows to one while replacing null values
提问by APC
Let's say I've got the following database table
假设我有以下数据库表
Name | Nickname | ID
----------------------
Joe Joey 14
Joe null 14
Now I want to do a select statement that merges these two columns to one while replacing the null values. The result should look like this:
现在我想做一个选择语句，将这两列合并为一，同时替换空值。结果应如下所示：
Joe, Joey, 14
Which sql statement manages this (if it's even possible)?
哪个 sql 语句管理这个（如果可能的话）？
回答by APC
Simplest solution:
最简单的解决方案：
SQL> select * from t69
2 /
NAME NICKNAME ID
---------- ---------- ----------
Joe Joey 14
Joe 14
Michael 15
Mick 15
Mickey 15
SQL> select max(name) as name
2 , max(nickname) as nickname
3 , id
4 from t69
5 group by id
6 /
NAME NICKNAME ID
---------- ---------- ----------
Joe Joey 14
Michael Mickey 15
SQL>
If you have 11gR2 you could use the new-fangled LISTAGG() functionbut otherwise it is simple enough to wrap the above statement in a SELECT which concatenates the NAME and NICKNAME columns.
如果您有 11gR2，您可以使用新奇的 LISTAGG() 函数，但除此之外，将上述语句包装在连接 NAME 和 NICKNAME 列的 SELECT 中很简单。
回答by Vijay
AFAIK, the question is not clear.so i am making some assumptions over here. your output has the first and 3rd columns for both the rows as same. Only the 2nd field is different.
AFAIK，问题不清楚。所以我在这里做一些假设。您的输出具有相同的两行的第一列和第三列。只有第二个字段不同。
so u can simply write a select quest
所以你可以简单地写一个选择任务
select one.name,two.nick_name,one.id from
(select name,id from your_tb group by name,id) one,
your_tb two
where two.nickname is not NULL
and two.name=one.name
and two.id=one.id;
may be we can tune this but i am not good in tuning sql squeries,but this is the way i suppose u need.
也许我们可以调整这个，但我不擅长调整 sql 查询，但这是我认为你需要的方式。
