求:在合同有效期内的合同数量及验收数量

Question

table A(计划表)
id wzmc htsl hth
-- 物资名称         合同数量          合同号
01 照相机 10 ht01
02 手机 10 ht01
03 照相机 10 ht02table B (合同表)id hth A_id rq_begin           rq_end
-- 合同号 表A的id 起始日期           结束日期
01 ht01 01,02 2011-01-01 2011-12-30
02 ht02 03 2010-10-01 2011-10-30table C(验收表)id hth wzmc yssl      yssj
-- 合同号 物资名称        验收数量          验收日期
01 ht01 照相机 5 2011-05-01
02 ht01 照相机 5 2011-06-01
03 ht01 手机 2 2011-06-01
想要的结果:物资名称   合同数量         验收数量
照相机 20 10
手机 10 2条件:
统计时的日期（今天）处在B表的开始和结束日期内(合同有效期内),统计各类物资有多少合同数量和验收数量。不好意思,分不多了,问题又多,所以少给点分,各位高手别见怪.

Answer 1

create table #A--(计划表)
(id nvarchar(10),wzmc nvarchar(20),htsl int,hth nvarchar(10))-- 物资名称 合同数量 合同号
insert #A
select '01','照相机', 10, 'ht01' union all
select '02','手机', 10, 'ht01' union all
select '03','照相机', 10, 'ht02'create table #B --(合同表)
(id nvarchar(10),hth nvarchar(10),A_id nvarchar(200),rq_begin datetime,rq_end datetime)-- 合同号 表A的id 起始日期 结束日期
insert #B
select '01', 'ht01', '01,02', '2011-01-01', '2011-12-30' union all
select '02', 'ht02', '03', '2010-10-01', '2011-10-30'create table #C--(验收表)
(id nvarchar(10),hth nvarchar(10),wzmc nvarchar(20), yssl int,yssj datetime)-- 合同号 物资名称 验收数量 验收日期
insert #C
select '01' ,'ht01' ,'照相机', 5,'2011-05-01' union all
select '02' ,'ht01' ,'照相机', 5,'2011-06-01' union all
select '03' ,'ht01' ,'手机', 2,'2011-06-01'select a.wzmc as 物资名称,sum(a.htsl) as 合同数量, 
(select sum(yssl) from #C as c where c.wzmc=a.wzmc) as 验收数量 
from #A as a Left join #B as b 
on charindex(','+a.id+',',','+A_id+',')>0 and getdate() between rq_begin and rq_end
group by a.wzmc--物资名称             合同数量     验收数量
---------------------- ----------- -----------
--手机                  10          2
--照相机                20          10

Answer 2

1樓錯了..應該用join才合適select a.wzmc as 物资名称,sum(a.htsl) as 合同数量,
(select sum(yssl) from #C as c where c.wzmc=a.wzmc) as 验收数量 
from #A as a join #B as b 
on charindex(','+a.id+',',','+A_id+',')>0 and getdate() between rq_begin and rq_end
group by a.wzmc

Answer 3

select A.wzmc 物资名称,sum(A.htsl) 合同数量 ,sum(C.yssl) 验收数量
from B inner join A ON charindex(cast(A.id as varchar(10)),B.A_id)!=0
inner join C on A.hth=C.hth and A.wzmc=C.wzmc
where rq_begin<=getdate() and rq_end>=getdate()
group by A.wzmc
order by A.wzmc desc

Answer 4

create table t1
(
id varchar(2),
wzmc varchar(20),
htsl int,
hth varchar(5)
)
insert into t1
select '01', '照相机', 10, 'ht01' union all
select '02', '手机', 10, 'ht01' union all
select '03', '照相机', 10, 'ht02'
create table t2
(
id varchar(2),
hth varchar(5),
t1id varchar(10),
beginriqi datetime,
endriqi datetime
)
insert into t2
select '01', 'ht01', '01,02', '2011-01-01', '2011-12-30' union all
select '02', 'ht02', '03', '2010-10-01', '2011-10-30'
create table t3
(
id varchar(2),
hth varchar(5),
wzmc varchar(20),
yssl int,
ysri datetime
)
insert into t3
select '01', 'ht01', '照相机', 5, '2011-05-01' union all
select '02', 'ht01', '照相机', 5, '2011-06-01' union all
select '03', 'ht01', '手机', 2, '2011-06-01'
select * from t1
select * from t2
select * from t3;with aaa as
(select wzmc,SUM(htsl) as htsl from t1 group by wzmc)
,bbb as
(select wzmc,SUM(yssl) as yssl from t3 inner join t2 on t3.hth=t2.hth and t3.ysri>=t2.beginriqi and t3.ysri<=t2.endriqi
group by t3.wzmc)
select aaa.wzmc as '物资名称',aaa.htsl as '合同数量',bbb.yssl as '验收数量' from aaa inner join bbb on aaa.wzmc=bbb.wzmc

Answer 5

用了charindex这个函数,执行起来特别慢,有没有别的方案呢?因为每个表都有'hth',能不能用这个字关联.只要能达到:统计在一定合同时间范围内的计划数量和验收数量就行,不管是哪份合同的.

Answer 6

create table #A--(计划表)
(id nvarchar(10),wzmc nvarchar(20),htsl int,hth nvarchar(10))-- 物资名称 合同数量 合同号
insert #A
select '01','照相机', 10, 'ht01' union all
select '02','手机', 10, 'ht01' union all
select '03','照相机', 10, 'ht02'create table #B --(合同表)
(id nvarchar(10),hth nvarchar(10),A_id nvarchar(200),rq_begin datetime,rq_end datetime)-- 合同号 表A的id 起始日期 结束日期
insert #B
select '01', 'ht01', '01,02', '2011-01-01', '2011-12-30' union all
select '02', 'ht02', '03', '2010-10-01', '2011-10-30'create table #C--(验收表)
(id nvarchar(10),hth nvarchar(10),wzmc nvarchar(20), yssl int,yssj datetime)-- 合同号 物资名称 验收数量 验收日期
insert #C
select '01' ,'ht01' ,'照相机', 5,'2011-05-01' union all
select '02' ,'ht01' ,'照相机', 5,'2011-06-01' union all
select '03' ,'ht01' ,'手机', 2,'2011-06-01'
SELECT a.wzmc,SUM(a.htsl) htsl,SUM(c.yssl) yssl
from #A a,#B b,#C c
WHERE a.hth=b.hth AND a.hth=c.hth AND a.wzmc=c.wzmc AND c.yssj<b.rq_end AND GETDATE() BETWEEN b.rq_begin AND b.rq_end
GROUP BY a.wzmc--result
wzmc                 htsl        yssl
-------------------- ----------- -----------
手机                   10          2
照相机                  20          10(2 row(s) affected)

Answer 7

俺说加这一列。。是为了让结构更清晰一些。。
但楼主楼主想要的结果应该是按物资来统计的。。这样统计应该跟物资在那个计划或那个验收中无关吧？
只跟合同日期相关吧？select a.wzmc as 物资名称,sum(a.htsl) as 合同数量,
(select sum(yssl) from #C as c where c.wzmc=a.wzmc) as 验收数量 
from #A as a join #B as b 
on charindex(','+a.id+',',','+A_id+',')>0 and getdate() between rq_begin and rq_end
group by a.wzmc--物资名称               合同数量    验收数量
---------------------- ----------- -----------
--手机                   10          2
--照相机                 20          10这样的语句有问题么？