select a.id,a.name,a.age from A a,(select distinct id,
first_value(address) over(partition by id) pay from B b) c where a.id=c.id
能显示第一条的重复数据,但是不符合我的需求,我该怎么写那?
first_value(address) over(partition by id) pay from B b) c where a.id=c.id
能显示第一条的重复数据,但是不符合我的需求,我该怎么写那?
select 1 id, '丽丽' name, 11 age from dual union all
select 2, '明明', 12 from dual),
b as(
select 1 id, '北环路' address from dual union all
select 1 ,'南阳路' from dual union all
select 2,'商城路' from dual union all
select 2,'王府井' from dual)
select a.id, a.name, a.age, wmsys.wm_concat(address)
from a, b
where a.id = b.id(+)
group by a.id, a.name, a.age
with a as(
select 1 id, '丽丽' name, 11 age from dual union all
select 2, '明明', 12 from dual),
b as(
select 1 id, '北环路' address from dual union all
select 1 ,'南阳路' from dual union all
select 2,'商城路' from dual union all
select 2,'王府井' from dual)
select a.id, a.name, a.age, replace(wmsys.wm_concat(address),',',' ')
from a, b
where a.id = b.id(+)
group by a.id, a.name, a.age
(select 1 id, 'Beihuanroad' address
from dual
union all
select 1 id, 'Nanyangroad' address
from dual
union all
select 2 id, 'Shangchengroad' address
from dual
union all
select 2 id, 'Wangfujin' address
from dual),
t1 as
(select 1 id, 'lily' nm, 12 age
from dual
union all
select 2 id, 'Mingming' nm, 12 age
from dual)
select *
from t1,
(select id, replace(to_char(wm_concat(address)), ',', ' ')
from t
group by id) t2
where t1.id = t2.id;
--表A
create table A (id number,name varchar2(50),age number);
insert into A (id,name,age)
select 1,'丽丽',11 from dual union
select 2,'明明',12 from dual;select * from A;--表B
create table B (Aid number,address varchar2(50));
insert into B (Aid,address)
select 1,'北环路' from dual union
select 1,'南阳路' from dual union
select 2,'商城路' from dual union
select 2,'王府井' from dual;select * from B;--wm_concat(column)函数 http://database.51cto.com/art/201010/231126.htm
--第一步:将b表先合并
select Aid ,wm_concat(address) from B group by aid;
--第二步:用join 联合查询得出最后的结果select a.id,a.name,a.age,b.address from A a join (select Aid ,wm_concat(address) address from B group by aid) b on a.id = b.aid
WITH A AS (
SELECT 1 ID,'丽丽' name, 11 age FROM dual UNION ALL
SELECT 2,'明明',12 FROM dual
),
B AS (
SELECT 1 id,'北环路' address FROM dual UNION ALL
SELECT 1,'南阳路' FROM dual UNION ALL
SELECT 2,'商城路' FROM dual UNION ALL
SELECT 2,'王府井' FROM dual
)SELECT T.ID,A.NAME,A.AGE,REPLACE(T.ADDRESS,',',' ') FROM (SELECT A.ID,wmsys.wm_concat(B.ADDRESS) ADDRESS FROM B,A
WHERE A.ID=B.ID(+) GROUP BY A.ID) T,A WHERE T.ID=A.ID(+)