I want to get only rows having a value and some other value than NULL for a particular username column.
我想只获取具有值的行以及特定用户名列的NULL以外的其他值。
If both rows have null for that particular username then it should show Null only once in output. If there are more than two rows for same username with null and some other value then display the value only not null.
如果两个行对于该特定用户名都为null,那么它应该在输出中仅显示Null一次。如果同一用户名的行数超过两行且为null,则显示该值的值不为空。
Below is example sample and output. How it can be done using sql query?
下面是示例示例和输出。如何使用sql查询完成?
Table:
Col1 | Col2
-------------------------
a | abc
a | bc
b | null
b | null
c | der
c | null
Output:
Col1 | Col2
-------------------------
a | abc
a | bc
b | null
c | der
3 个解决方案
#1
Outlining the idea, there might be some syntax errors, don't have access to oracle.
概述这个想法,可能会有一些语法错误,无法访问oracle。
SELECT * FROM
( SELECT DISTINCT USERNAME FROM <TABLE> ) USERS
LEFT OUTER JOIN
( SELECT USERNAME, COL2 FROM <TABLE> WHERE COL2 IS NOT NULL) USERS_COL2
ON
USRES.USERNAME = USERS_COL2.USERNAME
#2
you use listagg () or stragg ()
你使用listagg()或stragg()
drop table test;
create table test (
col1 varchar2(10),
col2 varchar2(10)
);
insert into test values ( 'a','abc');
insert into test values ( 'a','abc');
insert into test values ( 'b',null);
insert into test values ( 'b',null);
insert into test values ( 'c','der');
insert into test values ( 'c',null);
commit;
select col1,
listagg (col2,',') within group (order by col1) col2
from test
group by col1;
COL1 COL2
---------- -----------
a abc,abc
b
c der
select col1, stragg (col2)
from test
group by col1;
#3
select col1, col2, count(*)
from omc.test
group by col1,col2;
you can remove count(*)
你可以删除计数(*)
#1
Outlining the idea, there might be some syntax errors, don't have access to oracle.
概述这个想法,可能会有一些语法错误,无法访问oracle。
SELECT * FROM
( SELECT DISTINCT USERNAME FROM <TABLE> ) USERS
LEFT OUTER JOIN
( SELECT USERNAME, COL2 FROM <TABLE> WHERE COL2 IS NOT NULL) USERS_COL2
ON
USRES.USERNAME = USERS_COL2.USERNAME
#2
you use listagg () or stragg ()
你使用listagg()或stragg()
drop table test;
create table test (
col1 varchar2(10),
col2 varchar2(10)
);
insert into test values ( 'a','abc');
insert into test values ( 'a','abc');
insert into test values ( 'b',null);
insert into test values ( 'b',null);
insert into test values ( 'c','der');
insert into test values ( 'c',null);
commit;
select col1,
listagg (col2,',') within group (order by col1) col2
from test
group by col1;
COL1 COL2
---------- -----------
a abc,abc
b
c der
select col1, stragg (col2)
from test
group by col1;
#3
select col1, col2, count(*)
from omc.test
group by col1,col2;
you can remove count(*)
你可以删除计数(*)