为什么要中间表,1个SQL就可以了阿select b.TITLE_id,max(decode(b.AU_ORD,1,a.AU_NAME)) au_NAME1,max(decode(b.AU_ORD,2,a.AU_NAME)) au_name2 from table1 a ,table2 b where a.AU_ID = b.AU_ID group by a.AU_ID
不好意思,group by 的字段写错了 select b.TITLE_id,max(decode(b.AU_ORD,1,a.AU_NAME)) au_NAME1,max(decode(b.AU_ORD,2,a.AU_NAME)) au_name2 from table1 a ,table2 b where a.AU_ID = b.AU_ID group by b.TITLE_id
非常感谢楼上的,很抱歉我忘记了还要加上表3的booktype,price,title_name一起查询,我试了一下提示说 price *ORA-00979: 不是 GROUP BY 表达式 是不是先用你给的语句生成一个表再与表3合并(怎么合并?)?谢谢
select b.TITLE_id,c.TITLE_NAME,c.BOOKTYPE,max(decode(b.AU_ORD,1,a.AU_NAME)) au_NAME1,max(decode(b.AU_ORD,2,a.AU_NAME)) au_name2 from table1 a ,table2 b,table3 c where a.AU_ID = b.AU_ID and b.TITLE_id = c.TITLE_id group by b.TITLE_id,c.TITLE_NAME,c.BOOKTYPE
漏了price select b.TITLE_id,c.TITLE_NAME,c.BOOKTYPE,c.price,max(decode(b.AU_ORD,1,a.AU_NAME)) au_NAME1,max(decode(b.AU_ORD,2,a.AU_NAME)) au_name2 from table1 a ,table2 b,table3 c where a.AU_ID = b.AU_ID and b.TITLE_id = c.TITLE_id group by b.TITLE_id,c.TITLE_NAME,c.BOOKTYPE,c.price
from table1 a ,table2 b
where a.AU_ID = b.AU_ID
group by a.AU_ID
T0001 张三 但我不知道如何连接表1和上表,使得生成
TITLE_id au_NAME(第一作者) au_name2(第二作者)
T0001 王二 张三
T0002 王二
T0003 李四
T0004 张三 这两个表行列数并不一样,用oracle语句该如何连接呢?
另外这个查询用存储过程怎么完成?
1楼和二楼的大侠帮帮忙,谢谢!
select b.TITLE_id,max(decode(b.AU_ORD,1,a.AU_NAME)) au_NAME1,max(decode(b.AU_ORD,2,a.AU_NAME)) au_name2
from table1 a ,table2 b
where a.AU_ID = b.AU_ID
group by b.TITLE_id
price
*ORA-00979: 不是 GROUP BY 表达式
是不是先用你给的语句生成一个表再与表3合并(怎么合并?)?谢谢
from table1 a ,table2 b,table3 c
where a.AU_ID = b.AU_ID and b.TITLE_id = c.TITLE_id
group by b.TITLE_id,c.TITLE_NAME,c.BOOKTYPE
select b.TITLE_id,c.TITLE_NAME,c.BOOKTYPE,c.price,max(decode(b.AU_ORD,1,a.AU_NAME)) au_NAME1,max(decode(b.AU_ORD,2,a.AU_NAME)) au_name2
from table1 a ,table2 b,table3 c
where a.AU_ID = b.AU_ID and b.TITLE_id = c.TITLE_id
group by b.TITLE_id,c.TITLE_NAME,c.BOOKTYPE,c.price