昨天的那个数据搞错了,重新再开一个..

cookies10wen 2009-10-13 11:01:56
MaterialAtInfo:
OrderNo Name Color AtAmount Place
------- ---- ------ ----------- -----
A01 AA Red 60 A1
A01 AA Yellow 500 A2
A02 BB Bule 0 A3
A03 CC Black 300 A4
A04 DD White 400 A5

MaterialInInfo:
OrderNo Name Color InAmount Place
------- ---- ------ ----------- -----
A01 AA Red 50 A1
A01 AA Red 50 A1
A01 AA Yellow 500 A2
A02 BB Bule 100 A3
A03 CC Black 300 A4
A04 DD White 400 A5

MaterialOutInfo:
OrderNo Name Color OutAmount Place
------- ---- ------ ----------- -----
A01 AA Red 20 A1
A01 AA Red 20 A1
A02 BB Bule 100 A3


结果:
OrderNo Name Color InAmount AtAmount OutAmount Place
------- ---- ------ ----------- ----------- ----------- -----
A01 AA Red 100 60 40 A1
A01 AA Yellow 500 500 0 A2
A02 BB Bule 100 0 100 A3
A03 CC Black 300 300 0 A4
A04 DD White 400 400 0 A5
...全文
186 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
dealer_man 2009-10-14
  • 打赏
  • 举报
回复
顶一个。
SQL77 2009-10-13
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 dawugui 的回复:]
SQL codecreatetable tb1(OrderNovarchar(10),Namevarchar(10),Colorvarchar(10), AtAmountint, Placevarchar(10))insertinto tb1values('A01','AA','Red' ,60 ,'A1')insertinto tb1values('A01','AA','Yellow¡­
[/Quote]
顶乌龟大侠
gsk09 2009-10-13
  • 打赏
  • 举报
回复
这样的数据要什么结果
MaterialAtInfo:
OrderNo Name Color AtAmount Place
------- ---- ------ ----------- -----
A01 AA Red 60 A1
A01 AA Yellow 500 A2
A02 BB Bule 0 A3
A03 CC Black 300 A4
A04 DD White 400 A5

MaterialInInfo:
OrderNo Name Color InAmount Place
------- ---- ------ ----------- -----
A01 AA Red 50 A1
A01 AA Red 50 A1
A01 AA Yellow 500 A2
A02 BB Bule 100 A3
A03 CC Black 300 A4


MaterialOutInfo:
OrderNo Name Color OutAmount Place
------- ---- ------ ----------- -----
A01 AA Red 20 A1
A01 AA Red 20 A1
A02 BB Bule 100 A3
A04 DD White 400 A5
dawugui 2009-10-13
  • 打赏
  • 举报
回复
我好象记得昨天就给你写了.
dawugui 2009-10-13
  • 打赏
  • 举报
回复
create table tb1(OrderNo varchar(10),Name varchar(10),Color varchar(10), AtAmount  int,  Place varchar(10))
insert into tb1 values('A01', 'AA', 'Red' , 60 , 'A1')
insert into tb1 values('A01', 'AA', 'Yellow', 500 , 'A2')
insert into tb1 values('A02', 'BB', 'Bule' , 0 , 'A3')
insert into tb1 values('A03', 'CC', 'Black' , 300 , 'A4')
insert into tb1 values('A04', 'DD', 'White' , 400 , 'A5')

create table tb2(OrderNo varchar(10),Name varchar(10),Color varchar(10),InAmount int, Place varchar(10))
insert into tb2 values('A01' , 'AA' , 'Red ' ,50 , 'A1 ')
insert into tb2 values('A01' , 'AA' , 'Red ' ,50 , 'A1 ')
insert into tb2 values('A01' , 'AA' , 'Yellow' ,500 , 'A2 ')
insert into tb2 values('A02' , 'BB' , 'Bule ' ,100 , 'A3 ')
insert into tb2 values('A03' , 'CC' , 'Black ' ,300 , 'A4 ')
insert into tb2 values('A04' , 'DD' , 'White ' ,400 , 'A5 ')

create table tb3(OrderNo varchar(10) ,Name varchar(10),Color varchar(10),OutAmount int, Place varchar(10))
insert into tb3 values('A01' , 'AA' , 'Red ' , 20 , 'A1 ')
insert into tb3 values('A01' , 'AA' , 'Red ' , 20 , 'A1 ')
insert into tb3 values('A02' , 'BB' , 'Bule' , 100 , 'A3 ')
go

select OrderNo, Name ,Color, sum(InAmount) InAmount , sum(AtAmount) AtAmount , sum(OutAmount) OutAmount, Place from
(
select OrderNo, Name ,Color , InAmount =0 , AtAmount , OutAmount = 0, Place from tb1
union all
select OrderNo, Name ,Color , InAmount , AtAmount =0 , OutAmount = 0, Place from tb2
union all
select OrderNo, Name ,Color , InAmount =0, AtAmount =0 , OutAmount , Place from tb3
) t
group by OrderNo, Name ,Color, Place

drop table tb1,tb2,tb3

/*
OrderNo Name Color InAmount AtAmount OutAmount Place
---------- ---------- ---------- ----------- ----------- ----------- ----------
A01 AA Red 100 60 40 A1
A01 AA Yellow 500 500 0 A2
A02 BB Bule 100 0 100 A3
A03 CC Black 300 300 0 A4
A04 DD White 400 400 0 A5

(所影响的行数为 5 行)
*/
华夏小卒 2009-10-13
  • 打赏
  • 举报
回复

if object_id('tb1') is not null drop table tb1
go
create table tb1(OrderNo varchar(10),Name varchar(10), Color varchar(10), AtAmount int, Place varchar(10))
insert into tb1 select
'A01', 'AA', 'Red' , 60 , 'A1' union all select
'A01', 'AA', 'Yellow', 500 , 'A2' union all select
'A02', 'BB', 'Bule' , 0 , 'A3' union all select
'A03', 'CC', 'Black' , 300 , 'A4' union all select
'A04', 'DD', 'White' , 400 , 'A5'

if object_id('tb2') is not null drop table tb2
go
create table tb2(OrderNo varchar(10),Name varchar(10), Color varchar(10),inAmount int, Place varchar(10))
insert into tb2 select
'A01', 'AA', 'Red' , 50 , 'A1' union all select
'A01', 'AA', 'Red' , 50 , 'A1' union all select
'A01', 'AA' , 'Yellow', 500 , 'A2' union all select
'A02', 'BB' , 'Bule' , 100 , 'A3' union all select
'A03', 'CC' , 'BLACK' , 300 , 'A4' union all select
'A04', 'DD' , 'White', 400 , 'A5'


if object_id('tb3') is not null drop table tb3
go
create table tb3(OrderNo varchar(10),Name varchar(10), Color varchar(10), outAmount int, Place varchar(10))
insert into tb3 select
'A01', 'AA' , 'Red', 20 , 'A1' union all select
'A01', 'AA' , 'Red' , 20 , 'A1' union all select
'A02', 'BB', 'Bule' , 100 , 'A3'


select a.OrderNo,a.Name,a.Color,
InAmount=isnull(InAmount,0),
AtAmount=isnull(AtAmount,0),
OutAmount=isnull(OutAmount,0),a.Place
from tb1 a
left join
(select OrderNo, Name,Color,InAmount=sum(InAmount)
from tb2 group by OrderNo, Name,Color) b
on a.OrderNo=b.OrderNo and a.Name=b.Name and a.Color=b.Color
left join
(select OrderNo, Name,Color,outAmount=sum(outAmount)
from tb3 group by OrderNo, Name,Color)c
on a.OrderNo=c.OrderNo and a.Name=c.Name and a.Color=c.Color


OrderNo Name Color InAmount AtAmount OutAmount Place
---------- ---------- ---------- ----------- ----------- ----------- ----------
A01 AA Red 100 60 40 A1
A01 AA Yellow 500 500 0 A2
A02 BB Bule 100 0 100 A3
A03 CC Black 300 300 0 A4
A04 DD White 400 400 0 A5

(5 行受影响)

dawugui 2009-10-13
  • 打赏
  • 举报
回复
就是全部union all,然后group by
solarcy 2009-10-13
  • 打赏
  • 举报
回复
select MaterialAtInfo .OrderNo, MaterialAtInfo .Name, MaterialAtInfo .Color, MaterialAtInfo .AtAmount, MaterialAtInfo .Place,MaterialInInfo.InAmount
,MaterialOutInfo .OutAmount from MaterialAtInfo
inner join MaterialInInfo
on
MaterialAtInfo .orderNo=MaterialInInfo.orderNo and MaterialAtInfo .Name =MaterialInInfo.Name and MaterialAtInfo .color=MaterialInInfo.color
inner join MaterialOutInfo
MaterialAtInfo .orderNo=MaterialOutInfo.orderNo and MaterialAtInfo .Name =MaterialOutInfo.Name and MaterialAtInfo .color=MaterialOutInfo.color
guguda2008 2009-10-13
  • 打赏
  • 举报
回复
两个INNER JOIN搞定
luoyoumou 2009-10-13
  • 打赏
  • 举报
回复
--我不是四眼青蛙,看不懂楼主的意思,
--楼主就不能说详细点?
guguda2008 2009-10-13
  • 打赏
  • 举报
回复
BD
navy887 2009-10-13
  • 打赏
  • 举报
回复
sf

22,199

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧