oracle面试题求解

[麦田]

GOODS(货品表)

GOODSID(主键) GOODSNAME MEMO

1 青霉素

2 西瓜霜

3 创可贴

4 西洋参


SU(进货表)

GOODSID(主键) SUQTY

1 60

2 70


SA(销售表)

GOODSID(主键) SAQTY

1 80

2 90

问题一：进货记录，给出SQL达到如下效果

GOODSID GOODSNAME SUQTY

1 青霉素 60

2 西瓜霜 70

3 创可贴 0

4 西洋参 0


问题二：进销对比，给出SQL达到如下效果

GOODSID GOODSNAME SUQTY SAQTY

1 青霉素 60 0

2 西瓜霜 70 80

3 创可贴 0 90


(有会的请留下答案)

------------------------------------------------

sql优化问题

select * from sa where sa.G

[dzxccsu]

真是面试题目？是作业吧

[麦田]

[Quote=引用 1 楼 dzxccsu 的回复:]

真是面试题目？是作业吧
[/Quote]
今天上午刚面试完的，格式有点不好使，看着有点不好看。

[麦田]

csdn博客 原试题http://blog.csdn.net/itmyhome/article/details/7231238

[Phoenix_99]

with goods as (
select 1 goodsid,'青霉素' goodsname from dual
union all
select 2 goodsid,'西瓜霜' goodsname from dual
union all
select 3 goodsid,'创可贴' goodsname from dual
union all
select 4 goodsid,'西洋参' goodsname from dual
)
, su as (
select 1 goodsid,60 suqty from dual
union all
select 2 goodsid,70 suqty from dual
)
select goods.goodsid,goods.goodsname,nvl(su.suqty,0) suqty from goods,su where goods.goodsid = su.goodsid(+)
order by 1

with goods as (
select 1 goodsid,'青霉素' goodsname from dual
union all
select 2 goodsid,'西瓜霜' goodsname from dual
union all
select 3 goodsid,'创可贴' goodsname from dual
union all
select 4 goodsid,'西洋参' goodsname from dual
)
, su as (
select 1 goodsid,60 suqty from dual
union all
select 2 goodsid,70 suqty from dual
),sa as (
select 1 goodsid,80 saqty from dual
union all
select 2 goodsid,90 saqty from dual
)

select goods.goodsid,goods.goodsname,nvl(suqty,0) suqty,nvl(saqty,0) saqty from goods,su,sa where 
goods.goodsid=su.goodsid(+) and su.goodsid=sa.goodsid(+)

[Phoenix_99]

with goods as (
select 1 goodsid,'青霉素' goodsname from dual
union all
select 2 goodsid,'西瓜霜' goodsname from dual
union all
select 3 goodsid,'创可贴' goodsname from dual
union all
select 4 goodsid,'西洋参' goodsname from dual
)
, su as (
select 1 goodsid,60 suqty from dual
union all
select 2 goodsid,70 suqty from dual
),sa as (
select 1 goodsid,80 saqty from dual
union all
select 2 goodsid,90 saqty from dual
)

select goods.goodsid,goods.goodsname,nvl(suqty,0) suqty,nvl(saqty,0) saqty from goods,su,sa where 
goods.goodsid=su.goodsid(+) and su.goodsid=sa.goodsid(+)

[cutebear2008]

标准的left join！

[andhai]

create table su
(goodsid char(1),suqty number(3,0));

insert into su values('1',60);
insert into su values('2',70);

create table sa (goodsid char(1), saqty number(3,0));

insert into sa values('1',80);
insert into sa values('2',90);
create table goods
(goodsname char(1),memo varchar2(10));
insert into goods values('1','青霉素');
insert into goods values('2','西瓜霜');
insert into goods values('3','创可贴');
insert into goods values('4','西洋参');

1.
select goods.goodsname goodsid,goods.memo,nvl(su.suqty,0)
from goods,su
where
goods.goodsname= su.goodsid(+)
order by goods.goodsname

2.
select goods.goodsname goodsid ,goods.memo,nvl(su.suqty,0),nvl(sa.saqty,0)
from goods,su,sa
where goods.goodsname= su.goodsid(+)
and goods.goodsname= sa.goodsid(+)

[huxingz]

答案1:
SELECT G.GOODSID, G.GOODSNAME, NVL(SUM(S.SUQTY), 0) AS SUQTY
FROM GOODS G, SU S
WHERE G.GOODSID = S.GOODSID(+)
GROUP BY G.GOODSID, G.GOODSNAME
答案2:

SELECT G.GOODSID,
G.GOODSNAME,
NVL(SUM(S.SUQTY), 0) AS SUQTY,
NVL(SUM(A.SAQTY), 0) AS SAQTY
FROM GOODS G, SU S, SA A
WHERE G.GOODSID = S.GOODSID(+)
AND G.GOODSID = A.GOODSID(+)
GROUP BY G.GOODSID, G.GOODSNAME
HAVING NVL(SUM(S.SUQTY), 0) + NVL(SUM(A.SAQTY), 0) > 0


另外,如果题目没有出错的话,那么估计是LZ自己写错了,你的结果根本就是看的莫名其妙,请问GOODSID作为主键体现在哪里

[陈大鱼]

[Quote=引用 6 楼 andhai 的回复:]
create table su
(goodsid char(1),suqty number(3,0));

insert into su values('1',60);
insert into su values('2',70);

create table sa (goodsid char(1), saqty number(3,0));

insert into sa value……
[/Quote]

第二个 应该改成

select goods.goodsname goodsid ,goods.memo,nvl(su.suqty,0),nvl(sa.saqty,0)
from goods,su,sa
where goods.goodsname= su.goodsid(+)
and goods.goodsname-1= sa.goodsid(+)
and rownum<4
order by goodsname;

[merrill]

问题二：进销对比，给出SQL达到如下效果

GOODSID GOODSNAME SUQTY SAQTY

1 青霉素 60 0

2 西瓜霜 70 80

3 创可贴 0 90
问题二 应该是楼主搞错了

问题一：
select a.goodsid, a.goodsname, nvl(b.suqty, 0)
from GOODS a, SU b
where a.goodsid = b.goodsid(+);

[mingchaoyan]

[Quote=引用 4 楼 phoenix_99 的回复:]

SQL code

with goods as (
select 1 goodsid,'青霉素' goodsname from dual
union all
select 2 goodsid,'西瓜霜' goodsname from dual
union all
select 3 goodsid,'创可贴' goodsname from dual
union all
select 4 good……
[/Quote]

漂亮呀！！！

[jmt1111]

with goods as (
select 1 goodsid,'青霉素' goodsname from dual
union all
select 2 goodsid,'西瓜霜' goodsname from dual
union all
select 3 goodsid,'创可贴' goodsname from dual
union all
select 4 goodsid,'西洋参' goodsname from dual
)
, su as (
select 1 goodsid,60 suqty from dual
union all
select 2 goodsid,70 suqty from dual
),sa as (
select 2 goodsid,80 saqty from dual
union all
select 3 goodsid,90 saqty from dual
)

--select a.goodsid,goodsname,nvl(suqty,0) from goods a full outer join su b on (a.goodsid=b.goodsid);

--select a.goodsid,goodsname,nvl(suqty,0),nvl(saqty,0) 
--from goods a full outer join su b on (a.goodsid=b.goodsid) full outer join sa c on (a.goodsid=c.goodsid)
--where a.goodsid=b.goodsid or a.goodsid=c.goodsid;

[buryMyLove]

第二题LZ搞错了，根据第二题的答案，销售表的两个id应该是2，3而不是1，2

第一题：
select a.*,nvl(b.SUQTY) from GOODS a,SU b where a.GOODSID=b.GOODSID(+);

第二题是因为西洋参没有进也没有销所以结果里不出么，还是说进销相抵以后库存是0？不出的原因不同sql的写法也不同，如果是因为西洋参的进销都没有所以不出这条数据：
select a.*,nvl(b.SUQTY)，nvl(c.SAQTY) from GOODS a,SU b,SA c
where a.GOODSID=b.GOODSID(+)
and a.GOODSID=c.GOODSID(+)
and a.GOODSID in (select GOODSID from SU union select GOODSID from SA);