有2个表格
A:
Shop_cd year_A month_A Fromdate Todate
01 2006 1 2006/01/01 2006/07/31
01 2006 2 2006/01/01 2006/07/31
02 2006 3 2006/01/01 2006/07/31
02 2006 4 2006/01/01 2006/07/31
01 2006 8 2006/08/01 2006/12/31
01 2006 9 2006/08/01 2006/12/31
02 2006 10 2006/08/01 2006/12/31
02 2006 11 2006/08/01 2006/12/31B:
Shop_cd year_A month_A value
01 2006 1 1
01 2006 2 2
01 2006 3 1
01 2006 4 1
01 2006 5 1
01 2006 6 1
01 2006 7 1然后我想得到下面的表格
Shop_cd year_A month_A value value_c
01 2006 1 1 8
01 2006 2 2 8
02 2006 3 0 0
02 2006 4 0 0
01 2006 8 0 0
01 2006 9 0 0
02 2006 10 0 0
02 2006 11 0 0sql问应该怎写,谢谢
A:
Shop_cd year_A month_A Fromdate Todate
01 2006 1 2006/01/01 2006/07/31
01 2006 2 2006/01/01 2006/07/31
02 2006 3 2006/01/01 2006/07/31
02 2006 4 2006/01/01 2006/07/31
01 2006 8 2006/08/01 2006/12/31
01 2006 9 2006/08/01 2006/12/31
02 2006 10 2006/08/01 2006/12/31
02 2006 11 2006/08/01 2006/12/31B:
Shop_cd year_A month_A value
01 2006 1 1
01 2006 2 2
01 2006 3 1
01 2006 4 1
01 2006 5 1
01 2006 6 1
01 2006 7 1然后我想得到下面的表格
Shop_cd year_A month_A value value_c
01 2006 1 1 8
01 2006 2 2 8
02 2006 3 0 0
02 2006 4 0 0
01 2006 8 0 0
01 2006 9 0 0
02 2006 10 0 0
02 2006 11 0 0sql问应该怎写,谢谢
from A,
(
select Shop_cd,isnull(value,0) as value,isnull(count(*),0) as value_c from
A,B where A.Shop_cd=B.Shop_cd and A.year_A=B.year_A and A.month_A=B.month_A
)C
where C.Shop_cd=A.Shop_cd
order by A.month_A
A.Shop_cd,A.year_A,A.month_A,isnull(B.value,0) as value
from
A
left join
B
on
A.Shop_cd=B.Shop_cd and A.year_A=b.year_A and A.month_A=B.month_A
from A,
(
select Shop_cd,isnull(value,0) as value,isnull(count(B.*),0) as value_c from
A,B where A.Shop_cd=B.Shop_cd and A.year_A=B.year_A and A.month_A=B.month_A
)C
where C.Shop_cd=A.Shop_cd
order by A.month_A
from A,
(
select Shop_cd,isnull(B.value,0) as value,isnull(count(B.*),0) as value_c from
A,B where A.Shop_cd=B.Shop_cd and A.year_A=B.year_A and A.month_A=B.month_A
)C
where C.Shop_cd=A.Shop_cd
order by A.month_A
value_c = (select sum(value) from B where Shop_cd = a.Shop_cd)
FROM A as a LEFT JOIN B as b
ON a.Shop_cd = b.Shop_cd and a.year_A = b.year_A and a.month_A = b.month_A
SELECT a.Shop_cd,a.year_A,a.month_A,b.value,
value_c = (select sum(value) from B
where Shop_cd = a.Shop_cd and year_A = a.year_A and month_A between month(a.Fromdate) and month(a.Todate))
FROM A as a LEFT JOIN B as b
ON a.Shop_cd = b.Shop_cd and a.year_A = b.year_A and a.month_A = b.month_A