各位大虾帮帮忙,有3个表 advertisementboard,LayoutSettings,LayoutSettingItem, 以LayoutSettings表为主表,oracle语句写出下面需要的结果. 谢谢咯..advertisementboardID advType(类型) txtContent(内容) merchant
---------------------------------
a1 1 俄语 商家a
a2 2 法语 商家a
a3 3 汉语 商家a
a4 4 粤语 商家a
a5 1 俄语 商家b
.....LayoutSettings
---------------------
ID Merchant(商家)b1 商家a
b2 商家b
b3 商家c
b4 商家d
.....LayoutSettingItem
-------------------
ID advId(advertisementboard表的ID) SettingId(LayoutSettings的ID)C1 a1 b1
C2 a2 b1
C3 a3 b1
C4 a4 b1
c5 a1 b2
....需要的结果:
merchant txtContent(advType=1) txtContent(advType=2) txtContent(advType=3) txtContent(advType=4) 商家a 俄语 法语 汉语 粤语
商家b 俄语 法语 汉语 粤语
.....
---------------------------------
a1 1 俄语 商家a
a2 2 法语 商家a
a3 3 汉语 商家a
a4 4 粤语 商家a
a5 1 俄语 商家b
.....LayoutSettings
---------------------
ID Merchant(商家)b1 商家a
b2 商家b
b3 商家c
b4 商家d
.....LayoutSettingItem
-------------------
ID advId(advertisementboard表的ID) SettingId(LayoutSettings的ID)C1 a1 b1
C2 a2 b1
C3 a3 b1
C4 a4 b1
c5 a1 b2
....需要的结果:
merchant txtContent(advType=1) txtContent(advType=2) txtContent(advType=3) txtContent(advType=4) 商家a 俄语 法语 汉语 粤语
商家b 俄语 法语 汉语 粤语
.....
解决方案 »
- oracle触发器的注意事项
- 请问怎么知道某个表放于哪个表空间?
- ORA-01775,同义词的循环链?
- oracle 10g数据库恢复出现问题 急救!!
- 请问这个哪里有问题呀?
- 如何设置纵深型应用程序的数据结构?
- 一段C#给oracle加参数的代码,红色文字ParameterDirection判断有什么作用??
- 求sql寫法
- 菜鸟提问几个问题:创建好数据库后,表空间是否需要创建,是使用system表空间吗,需要先连接数据库后建用户和表吗?语法又是啥呢?
- 有没有第三方的数据库同步软件(在线等)
- oracle全文检索能根据文件路径检索吗???
- 如何将尾数强制变成整数,如果不是0的,要四舍五入变成0
---------------------------------
a1 1 俄语 商家a
a2 2 法语 商家a
a3 3 汉语 商家a
a4 4 粤语 商家a
a5 1 俄语 商家b
.....LayoutSettings
---------------------
ID Merchant(商家)b1 商家a
b2 商家b
b3 商家c
b4 商家d
.....LayoutSettingItem
-------------------
ID advId(advertisementboard表的ID) SettingId(LayoutSettings的ID)C1 a1 b1
C2 a2 b1
C3 a3 b1
C4 a4 b1
c5 a1 b2
....需要的结果:
merchant txtContent(advType=1) txtContent(advType=2) txtContent(advType=3) txtContent(advType=4) 商家a 俄语 法语 汉语 粤语
商家b 俄语 法语 汉语 粤语
.....
MAX(DECODE(C.advType, 1, C.txtContent, NULL)),
MAX(DECODE(C.advType, 2, C.txtContent, NULL)),
MAX(DECODE(C.advType, 3, C.txtContent, NULL)),
MAX(DECODE(C.advType, 4, C.txtContent, NULL))
FROM LayoutSettings A
LEFT JOIN LayoutSettingItem B
ON A.ID = B.SettingId
LEFT JOIN advertisementboard C
ON B.advId = C.ID
GROUP BY A.Merchant
max(decode(a.typ, '1', '俄语')),
max(decode(a.typ, '2', '法语')),
max(decode(a.typ, '3', '汉语')),
max(decode(a.typ, '4', '粤语'))
from a, b, c
where a.aid = c.aid
and b.bid = c.bid
group by b.merchant
(select 'a1' aid,'1' typ,'俄语' txt,'商家a' merchant from dual union all
select 'a2' aid,'2' typ,'法语' txt,'商家a' merchant from dual union all
select 'a3' aid,'3' typ,'汉语' txt,'商家a' merchant from dual union all
select 'a4' aid,'4' typ,'粤语' txt,'商家a' merchant from dual union all
select 'a5' aid,'1' typ,'俄语' txt,'商家b' merchant from dual union all
select 'a6' aid,'2' typ,'法语' txt,'商家b' merchant from dual union all
select 'a7' aid,'3' typ,'汉语' txt,'商家b' merchant from dual union all
select 'a8' aid,'4' typ,'粤语' txt,'商家b' merchant from dual),
b as
(select 'b1' bid,'商家a' merchant from dual union all
select 'b2' bid,'商家b' merchant from dual union all
select 'b3' bid,'商家c' merchant from dual union all
select 'b4' bid,'商家d' merchant from dual),
c as
(select 'c1' cid,'a1' aid,'b1' bid from dual union all
select 'c2' cid,'a2' aid,'b1' bid from dual union all
select 'c3' cid,'a3' aid,'b1' bid from dual union all
select 'c4' cid,'a4' aid,'b1' bid from dual union all
select 'c5' cid,'a5' aid,'b2' bid from dual union all
select 'c6' cid,'a6' aid,'b2' bid from dual union all
select 'c7' cid,'a7' aid,'b2' bid from dual union all
select 'c8' cid,'a8' aid,'b2' bid from dual)select b.merchant,
max(decode(a.typ, '1', '俄语')),
max(decode(a.typ, '2', '法语')),
max(decode(a.typ, '3', '汉语')),
max(decode(a.typ, '4', '粤语'))
from a, b, c
where a.aid = c.aid
and b.bid = c.bid
group by b.merchant
刚才需要的结果没说清楚,
需要的结果 变了一下 一个商家需要显示几条该怎么写呢:
merchant txtContent(advType=1) txtContent(advType=2) txtContent(advType=3) txtContent(advType=4) 商家a 俄语 法语 汉语 粤语
商家a 俄语
商家a 法语
商家b 俄语 法语 汉语 粤语
商家a 俄语
商家b 俄语 法语 汉语 粤语
“商家a 俄语 法语 汉语 粤语”
和
“商家a 俄语 "" "" "" ”用 union all 起来不就行了。。
MAX(DECODE(C.advType, 1, C.txtContent, NULL)) AS A,
MAX(DECODE(C.advType, 2, C.txtContent, NULL)) AS B,
MAX(DECODE(C.advType, 3, C.txtContent, NULL)) AS C,
MAX(DECODE(C.advType, 4, C.txtContent, NULL)) AS D
FROM LayoutSettings A
LEFT JOIN LayoutSettingItem B
ON A.ID = B.SettingId
LEFT JOIN advertisementboard C
ON B.advId = C.ID
GROUP BY A.Merchantunion allselect f.merchant, f.txtContent as a, '' as b, '' as c, '' as d
from advertisementboard f