问题描述

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

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

这是原始数据库里的数据

这是sql运行后的效果

解决方案

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);

效果图

解决方案二：

 select distinct strone,'',''
from Table_test
union select '',strtwo,''
from Table_test
union select '','',strthree
from Table_test

解决方案三：

如果想要第二列也是像第一列一样。那么第一行Sql语句显示字段改一下就行了。
select nvl2(type, '', address) address, nvl2(detail,'',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)

解决方案四：

改过后的效果图

解决方案五：

如果 你想 把 第二列紧挨着 。 把 语句 改成这种即可

select nvl2(type||detail, '', 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 all
select address,'', detail
from test
order by address,detail desc,type desc);

结果图: