简单的结构如下:
表tt
id name tel
1 m 12345
1 m 12345
1 m
2 w
3 c 23434
4 t
要查询的结果
id name tel
1 m 12345
2 w
3 c 23434
4 t
如果相同id值的取tel有数据一条
表tt
id name tel
1 m 12345
1 m 12345
1 m
2 w
3 c 23434
4 t
要查询的结果
id name tel
1 m 12345
2 w
3 c 23434
4 t
如果相同id值的取tel有数据一条
select id, name, tel
from (select id, name, tel,
row_number() over(partition by id, name order by tel) as isort
from table_name) t
where isort = 1;
select *
from (
select id, name, max(tel) tel
from tt
group by id, name
)
order by id;
with tb1 as (
select 1 id,'m' name,12345 tel from dual union all
select 1 id,'m' name,12345 tel from dual union all
select 1 id,'m' name,null tel from dual union all
select 2 id,'w' name,null tel from dual union all
select 3 id,'c' name,23434 tel from dual union all
select 4 id,'t' name,null tel from dual
)
select max(id) id,name,max(tel) tel from tb1 group by name order by id;