入库
产品ID 产品名 数量 金额
1 a 200 2000
2 b 100 1500
出库
产品ID 产品名 数量 金额
1 a 10 120
2 b 10 170
2 b 10 170
1 a 10 120
我想显示结构
产品ID 产品名 入库数量 入库金额 出库数量 出库金额 库存数量 库存金额
1 a 100 2000 20 240 180 1760
2 b 200 1500 20 340 180 1160
sql 语句两个表合并运算
答案:3 悬赏:40
解决时间 2021-03-03 20:51
- 提问者网友:若相守£卟离
- 2021-03-03 04:59
最佳答案
- 二级知识专家网友:随心随缘不随便
- 2021-03-03 05:39
-- 请修改 相应的表名称和字段名称
create table ruku(cid int,name varchar(20),shul numeric(18,0),jine numeric(18,0));
create table chuku(cid int,name varchar(20),shul numeric(18,0),jine numeric(18,0));
insert into ruku values(1,'a',200,2000);
insert into ruku values(2,'b',100,1500);
insert into chuku values(1,'2',10,120);
insert into chuku values(2,'b',10,170);
insert into chuku values(2,'b',10,170);
insert into chuku values(1,'2',10,120);
select a.cid,a.shul,a.jine,b.shul,b.jine,(a.shul -b.shul),(a.jine-b.jine) from ruku a ,(select cid,sum(shul) shul,sum(jine) jine from chuku group by cid) b where a.cid = b.cid
create table ruku(cid int,name varchar(20),shul numeric(18,0),jine numeric(18,0));
create table chuku(cid int,name varchar(20),shul numeric(18,0),jine numeric(18,0));
insert into ruku values(1,'a',200,2000);
insert into ruku values(2,'b',100,1500);
insert into chuku values(1,'2',10,120);
insert into chuku values(2,'b',10,170);
insert into chuku values(2,'b',10,170);
insert into chuku values(1,'2',10,120);
select a.cid,a.shul,a.jine,b.shul,b.jine,(a.shul -b.shul),(a.jine-b.jine) from ruku a ,(select cid,sum(shul) shul,sum(jine) jine from chuku group by cid) b where a.cid = b.cid
全部回答
- 1楼网友:承载所有颓废
- 2021-03-03 08:24
(select p, j --p是产品,j是价格
from (select p, j
from a
union
select p, j from b) a ----将两个表的所有数据连接,组成临时表
group by a.p
having count(*) = 1) --根据产品分组,将临时表a中的a,b单独拥有产品列出
union
(select a.p, (select a.j - b.j from a, b where a.p = bj) j
from a, b
where a.p = b.p) --a.b共有的,求差
- 2楼网友:我叫很个性
- 2021-03-03 07:10
select A.产品id as 产品id,
A.产品名 as 产品名,
A.rksl as 入库数量,
A.rkje as 入库金额,
B.cksl as 出库数量,
B.ckje as 出库金额
A.rkje-B.chje as 库存金额
from
(select t1.产品id,
t1.产品名,
sum(t1.数量) as rksl,
sum(t1.金额) as rkje,
from 入库 t1
group by t2.产品id,t1.产品名) A,
(select t2.产品id,
t2.产品名,
sum(t2.数量) as cksl,
sum(t2.金额) as chje,
from 出库 t2
group by t2.产品id,t2.产品名) B
where A.产品id =B.产品id
这样楼主都不采纳,我只能说在也不相信爱情了!
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯