一条sql怎么出来下面的效果，用distinct和union来解决

Question

本人数据库不熟，拜托大家了

这是原始数据库里的数据

这是sql运行后的效果

Answer 1

QQ邮件我已经回复你了
create table TEST
(
ADDRESS VARCHAR2(20),
TYPE VARCHAR2(20),
DETAIL VARCHAR2(20)
);

insert into TEST (ADDRESS, TYPE, DETAIL)
values ('上海', '闸北', 'A地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('上海', '宝山', 'A地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('上海', '普陀', 'A地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('上海', '浦东', 'A地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('河南', '周口', 'A地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('河南', '郑州', 'A地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('河南', '驻马店', 'A地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('河南', '洛阳', 'A地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('上海', '闸北', 'B地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('上海', '宝山', 'B地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('上海', '普陀', 'B地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('上海', '浦东', 'B地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('河南', '周口', 'B地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('河南', '郑州', 'B地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('河南', '驻马店', 'B地');
insert into TEST (ADDRESS, TYPE, DETAIL)
values ('河南', '洛阳', 'B地');
commit;

select nvl2(type, '', address) address, type, detail
from (select distinct address, '' type, '' detail
from test
union
select address, type, null detail
from test
group by type, address
union
select address, type, detail
from test
order by address, type desc, detail desc);

效果图