..两个表..一对多的关系..简单理解为..
customer{
customerNo:
customerName:
}
contact{
type:
code:
customerNo:
}现在拼成一个试图..
一条..customerId可以连接多个联系方式..没有则为空..
customerId,customerName, code1, code2,code3.
请问这个..view该怎么写..
customer{
customerNo:
customerName:
}
contact{
type:
code:
customerNo:
}现在拼成一个试图..
一条..customerId可以连接多个联系方式..没有则为空..
customerId,customerName, code1, code2,code3.
请问这个..view该怎么写..
customerNo customerName
10000 test1
10001 test2 code customerNo
0102233444 10000
13789007889 10000
01032324243 10001
13789007889 10001
怎么得到试图..
view_customer_contact
customerNo customerName code1 code2 code3
10000 test1 0102233444 13789007889
10001 test2 01032324243 13789007889 救助...呵呵...
customername,
max(decode(rn, 1, code, null)) code1,
max(decode(rn, 2, code, null)) code2,
max(decode(rn, 3, code, null)) code3
from
(select a.customerno,
a.customername,
b.code,
row_number() over(partition by a.customerno order by b.code) rn
from customer a, contact b
where a.customerno = b.customerno)
group by customerno, customername;
答案跟大家分享下...