select a.typeid,c.parid,b.f4 as num,c.price,b.f5 as total from ptype a,kucun b,price c
where a.fullname=b.f3 and a.parid=c.parid
order by c.price
搂主测试看看，是不是你所需要的结果？
where a.fullname=b.f3 and a.parid=c.parid
order by c.price
搂主测试看看，是不是你所需要的结果？
调试欢乐多
1,如果不存在goods，
select a.typeid,c.parid,b.f4 as num,c.price,b.f5 as total
into goods
from ptype a,kucun b,price c
where a.fullname=b.f3 and a.parid=c.parid
2，如果存在：
insert goods(ptypeid,parid,num,price,total)
select a.typeid,c.parid,b.f4 as num,c.price,b.f5 as total
from ptype a,kucun b,price c
where a.fullname=b.f3 and a.parid=c.parid
ptypeid parid jobnum outfactorydate num price total
0000400003 00004 30 2.00 60.00
0000300002 00003 50 3.00 150.00
0000200004 00002 40 4.00 160.00
因为中间有两个空列。所以上述表达式有点错误，请问该如何纠正？
from ptype a inner join temp$ b on a.fullname=b.f3
inner join price c on a.typeid=c.ptypeid
order by a.typeid
这样为什么有是有那么多重复的？？
create table ptype(
typeid char(10),
parid char(5),
fullname varchar(50)
)create table kucun(
f1 int identity,
库存盘点表 char(11),
f3 varchar(50),
f4 int,
f5 money
)create table price(
ptypeid char(10),
parid char(5),
price money
)--插入数据
insert into ptype values('0000400003','00004','汇源果汁')
insert into ptype values('0000300002','00003','旺旺奶糖')
insert into ptype values('0000200004','00002','青龙山泉')insert into kucun values('0000200004','旺旺奶糖',50,150.00)
insert into kucun values('0000200004','青龙山泉',40,160.00)
insert into kucun values('0000200004','汇源果汁',30,60.00)insert into price values('0000300002','00003',3.00)
insert into price values('0000400003','00004',2.00)
insert into price values('0000200004','00002',4.00)--查询并把结果插入生成 goods 表
select a.typeid, a.parid, c.f4 num, b.price, c.f5 total
into goods
from ptype a left join (
select f3,sum(f4) f4,sum(f5) f5 from kucun
group by f3
)c on a.fullname=c.f3 ,price b
where a.typeid=b.ptypeid--查询 goods 表
select * from goods--结果
typeid parid num price total
---------- ----- ----------- --------------------- ---------------------
0000400003 00004 30 2.0000 60.0000
0000300002 00003 50 3.0000 150.0000
0000200004 00002 40 4.0000 160.0000（所影响的行数为 3 行）
insert into goods
select a.typeid, a.parid, c.f4 num, b.price, c.f5 totalfrom ptype a left join (
select f3,sum(f4) f4,sum(f5) f5 from kucun
group by f3
)c on a.fullname=c.f3 ,price b
where a.typeid=b.ptypeid
create table ptype
(
typeid varchar(10),
parid varchar(10),
fullname varchar(20)
)
create table kucun
(
f1 int,[库存盘点表] varchar(20),
f3 varchar(20),f4 int,f5 decimal(18,2)
)
create table price
(
ptypeid varchar(20),parid varchar(10),price decimal(18,2)
)
create table goods
(
ptypeid varchar(20),parid varchar(10),jobnum int,outfactorydate datetime,
num int,price decimal(18,2),total decimal(18,2)
)insert ptype
select '0000400003','00004','汇源果汁' union
select '0000300002','00003','旺旺奶糖' union
select '0000200004','00002','青龙山泉' insert kucun
select 1,'45698873113','旺旺奶糖',50,150.00 union
select 2,'56445631318','青龙山泉',40,160.00 union
select 3,'65451123778','汇源果汁',30,60.00insert price
select '0000300002','00003',3.00 union
select '0000400003','00004',2.00 union
select '0000200004','000002',4.00
go--测试
truncate table goods
go
insert goods(ptypeid,parid,num,price,total)
select A.typeid,A.parid,[num]=isnull(B.num,0),[price]=isnull(C.price,0),[total]=isnull(B.num*C.price,0)
from ptype A
left join (select f3,[num]=sum(f4) from kucun group by f3)B on A.fullname=B.f3
join price C on A.typeid=C.ptypeid order by C.price--查看
select * from goods--删除测试环境
drop table ptype,kucun,price,goods--结果
/*ptypeid parid jobnum outfactorydate num price total
-------------- ---------- ----------- ---------------- ----------- --------------
0000400003 00004 NULL NULL 30 2.00 60.00
0000300002 00003 NULL NULL 50 3.00 150.00
0000200004 00002 NULL NULL 40 4.00 160.00（所影响的行数为 3 行）
*/