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(+)
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(+)
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(+)
答案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作为主键体现在哪里
第二个 应该改成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;
问题二:进销对比,给出SQL达到如下效果GOODSID GOODSNAME SUQTY SAQTY1 青霉素 60 02 西瓜霜 70 803 创可贴 0 90 问题二 应该是楼主搞错了问题一: select a.goodsid, a.goodsname, nvl(b.suqty, 0) from GOODS a, SU b where a.goodsid = b.goodsid(+);
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;
第二题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);
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(+)
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(+)
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(+)
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作为主键体现在哪里
第二个 应该改成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;
问题二 应该是楼主搞错了问题一:
select a.goodsid, a.goodsname, nvl(b.suqty, 0)
from GOODS a, SU b
where a.goodsid = b.goodsid(+);
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;
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);