求大神解析一下

[snwgija]

数据如下所示：
表TA
订单单号 客户 订单物料编码 订单数量 约定交货日期
1 X 11T 1000 2012-12-02
1 X 11Y 1000 2012-12-03
1 X 11Z 1000 2012-12-04

表TB
订单单号 工单单号 工单物料编码 生产数量 计划回复日期
1 503 11T 1010 2012-12-10
1 504 11Y 1020 2012-12-15


表TC
工单单号 完工单号 完工数量 生产完工日期
503 0001 500 2012-12-04
503 0002 400 2012-12-06
503 0003 100 2012-12-07
504 0004 500 2012-12-05


判断：
若累加的完工数量<订单数量，则“生产完工日期”为空
若累加的完工数量>=订单数量，则显示“最后一笔”完工单号所对应的“完工日期”
若表TB中没有工单对应，则“计划回复日期”为空
结果：
订单单号 客户 订单物料 订单数量 约定交货日期 计划回复日期 生产完工日期
1 X 11T 1000 2012-12-02 2012-12-10 2012-12-07
1 X 11Y 1000 2012-12-03 2012-12-15
1 X 11Z 1000 2012-12-04




求大神解答

[就是just4]

--拼视图做法不好，建议用帐本回填处理
DECLARE @ta TABLE(ddbh VARCHAR(20),kh VARCHAR(50),wlbm VARCHAR(20),ddsl DECIMAL(18,2),jhjhrq DATETIME)

INSERT INTO @ta(ddbh,kh,wlbm,ddsl,jhjhrq)
select '1','X','11T','1000','2012-12-02' union all
select '1','X','11Y','1000','2012-12-03' union all
select '1','X','11Z','1000','2012-12-04'

DECLARE @tb TABLE(ddbh VARCHAR(20),gddh VARCHAR(20),wlbm VARCHAR(20),scsl DECIMAL(18,2),jhhfrq DATETIME)
INSERT INTO @tb(ddbh,gddh,wlbm,scsl,jhhfrq)
select '1','503','11T','1010','2012-12-10' union all
select '1','504','11Y','1020','2012-12-15'

DECLARE @tc TABLE(gddh VARCHAR(20),wgdh VARCHAR(20),wgsl DECIMAL(18,2),scwgrq DATETIME)
INSERT INTO @tc(gddh,wgdh,wgsl,scwgrq)
select '503',' 0001',' 500','2012-12-04' union all
select '503',' 0002',' 400','2012-12-06' union all
select '503',' 0003',' 100','2012-12-07' union all
select '504',' 0004',' 500','2012-12-05'

SELECT t.ddbh,t.kh,t.wlbm,t.ddsl,t.jhjhrq,tt.jhhfrq,
	CASE WHEN ISNULL(tt.wgsl,0) < ISNULL(t.ddsl,0) THEN NULL ELSE tt.scwgrq END AS scwgrq
FROM @ta AS t LEFT JOIN (
		SELECT t1.ddbh,t1.wlbm,SUM(ISNULL(t1.scsl,0)) AS scsl,MIN(t1.jhhfrq) AS jhhfrq,SUM(ISNULL(t2.wgsl,0)) AS wgsl,MAX(t2.scwgrq) AS scwgrq
		FROM @tb AS t1 LEFT JOIN (SELECT gddh,SUM(ISNULL(wgsl,0)) AS wgsl,MAX(scwgrq) AS scwgrq FROM @tc GROUP BY gddh) AS t2 ON t1.gddh = t2.gddh
		GROUP BY t1.ddbh,t1.wlbm
	) AS tt ON t.ddbh = tt.ddbh AND t.wlbm = tt.wlbm

--
ddbh                 kh                                                 wlbm                 ddsl                                    jhjhrq                  jhhfrq                  scwgrq
-------------------- -------------------------------------------------- -------------------- --------------------------------------- ----------------------- ----------------------- -----------------------
1                    X                                                  11T                  1000.00                                 2012-12-02 00:00:00.000 2012-12-10 00:00:00.000 2012-12-07 00:00:00.000
1                    X                                                  11Y                  1000.00                                 2012-12-03 00:00:00.000 2012-12-15 00:00:00.000 NULL
1                    X                                                  11Z                  1000.00                                 2012-12-04 00:00:00.000 NULL                    NULL

(3 行受影响)

[ChangeMyself2012]

--创建测试数据
if OBJECT_ID('TA') is not null drop table TA
create table TA(订单单号 int, 客户 nvarchar(20),订单物料编码 nvarchar(20),订单数量 int,约定交货日期 datetime)
go
insert into TA
select 1,'X','11T',1000,'2012-12-02' union all
select 1,'X','11Y',1000,'2012-12-03' union all
select 1,'X','11Z',1000,'2012-12-04'

if OBJECT_ID('TB') is not null drop table TB
create table TB(订单单号 int, 工单单号 nvarchar(20),工单物料编码 nvarchar(20),生产数量 int,计划回复日期 datetime)
go
insert into TB
select 1,'503','11T',1010,'2012-12-10' union all
select 1,'504 ','11Y',1020,'2012-12-15'

if OBJECT_ID('TC') is not null drop table TC
create table TC(工单单号 int, 完工单号 nvarchar(20), 完工数量 int,生产完工日期 datetime)
go
insert into TC
select '503','0001',500,'2012-12-04' union all
select '503','0002',400,'2012-12-06' union all
select '503','0003',100,'2012-12-07' union all
select '504','0004',500,'2012-12-05'

--查询
;with T as(
select 工单单号,SUM(完工数量)完工数量,MAX(convert(nvarchar(10),生产完工日期,120))生产完工日期 from TC group by 工单单号
)
select a.订单单号,a.客户,a.订单物料编码,a.订单数量,
convert(nvarchar(10),a.约定交货日期,120)约定交货日期,
isnull(convert(nvarchar(10),b.计划回复日期,120),'')计划回复日期,
case when isnull(c.完工数量,0)<isnull(a.订单数量,0)then '' else convert(nvarchar(10),c.生产完工日期,120) end 完工日期
from TA a left join TB b 
on a.订单单号=b.订单单号 and a.订单物料编码=b.工单物料编码
left join T c on c.工单单号=b.工单单号 
/*查询结果
订单单号        客户                   订单物料编码               订单数量        约定交货日期     计划回复日期     完工日期
----------- -------------------- -------------------- ----------- ---------- ---------- ----------
1           X                    11T                  1000        2012-12-02 2012-12-10 2012-12-07
1           X                    11Y                  1000        2012-12-03 2012-12-15 
1           X                    11Z                  1000        2012-12-04            

(3 行受影响)
*/

--删除测试数据
drop table TA
drop table TB
drop table TC

[snwgija]

谢谢各位大神！明天再自己做一下，再次感谢！