现有两个数据表,JFQK,MAINAD
JFQK :company reday nopay jiang
mainad: company, day1 jiang1,jiang2,jiang3
如何实现这样的功能,查找jfqk表中 nopay=0 的company,再对查找到每一个company值在mainad中找,如果day1的年月=reday的年月,则把符全这个条件的所有mainad中的 jiang1+jiang2+jiang3 加起来赋值给jiang
如下例:
jfqk:
company reday nopay jiang
a 2003-1-1 0 0mainad:
company day1 jiang1 jiang2 jiang3
a 2003-1-5 10 20 0
a 2003-1-8 8 14 0
a 2003-1-5 0 -5 0
a 2003-2-5 10 20 0
最终要实现:
jfqk:
company reday nopay jiang
a 2003-1-1 0 47 (10+20+8+14-5=47)
JFQK :company reday nopay jiang
mainad: company, day1 jiang1,jiang2,jiang3
如何实现这样的功能,查找jfqk表中 nopay=0 的company,再对查找到每一个company值在mainad中找,如果day1的年月=reday的年月,则把符全这个条件的所有mainad中的 jiang1+jiang2+jiang3 加起来赋值给jiang
如下例:
jfqk:
company reday nopay jiang
a 2003-1-1 0 0mainad:
company day1 jiang1 jiang2 jiang3
a 2003-1-5 10 20 0
a 2003-1-8 8 14 0
a 2003-1-5 0 -5 0
a 2003-2-5 10 20 0
最终要实现:
jfqk:
company reday nopay jiang
a 2003-1-1 0 47 (10+20+8+14-5=47)
from jfqk a left join mainad b on b.company=a.company and convert(varcahr(7, a.ready, 120)=convert(varchar(7), b.day1, 120)
where a.nopay=0 and convert(varcahr(7, a.ready, 120)='2003-01'
group by a.company, a.reday, a.nopay
我测试时说a附近有语法错误
是否应该为 2003-1 更为合适
seledt jfqk.company, FormatDateTime('yyyy, mmmm', reday), nopy, b.jiang from jfqk ,(select company, FormatDateTime('yyyy, mmmm', day1) as ny, count(jiang1+jiang2+jiang3) as jiang from mainad group by year(day1),Month(day1)) as b
where FormatDateTime('yyyy, mmmm', b.ny) 左连接 FormatDateTime('yyyy, mmmm', jfqk.reday)
现在可以了,但这只是查找,如何真正把这些数据写进jfqk表中?
set jiang=(select [jiang]=sum(jiang1+jiang2+jiang3)
from jfqk a left join mainad b on b.company=a.company and convert(varcahr(7, a.ready, 120)=convert(varchar(7), b.day1, 120)
where a.nopay=0 and convert(varcahr(7, a.ready, 120)='2003-01'
and a.company=jfqk.company and a.reday=jfqk.reday and a.nopay=jfqk.nopay )/*
我没有测试的,
如果要写得更简单,
应该把A表替换成jfqk表。
大概形式就是上面的写法。
*/