需求:mysql4.0 想用B表中的字段的集计值(sum)=A表中的相应值,mysql4.0好像不支持子查询
mysql5.0中代码如下:
UPDATE ad_blog_spot_summary a
SET a.payment = (SELECT sum(b.payment)
FROM ad_blog_spot_disptype_summary b
WHERE spotid = 251
AND a.date = b.date)
WHERE spotid = 251
AND TO_DAYS(DATE_FORMAT('20091128','%Y%m%d')) <= TO_DAYS(a.date)
AND TO_DAYS(DATE_FORMAT('20091228','%Y%m%d')) >= TO_DAYS(a.date)mysql4.0 如何求sum的值赋给另一个表啊
mysql5.0中代码如下:
UPDATE ad_blog_spot_summary a
SET a.payment = (SELECT sum(b.payment)
FROM ad_blog_spot_disptype_summary b
WHERE spotid = 251
AND a.date = b.date)
WHERE spotid = 251
AND TO_DAYS(DATE_FORMAT('20091128','%Y%m%d')) <= TO_DAYS(a.date)
AND TO_DAYS(DATE_FORMAT('20091228','%Y%m%d')) >= TO_DAYS(a.date)mysql4.0 如何求sum的值赋给另一个表啊
解决方案 »
- CRecordset::Open函数的第二个参数问题
- MySQL WorkBench执行SQL脚本时,报错MySQL server has gone away
- SET @SQL=CONCAT语句里关于调用表名参数的问题
- 请教各位两个表之间的模糊查询?
- 两张表
- 为什么这2段sql查询的结果不一样,很奇怪的问题,请大家帮忙分析下
- 要把排序信息输入到一个表的字段中或者一个数组中,怎么办呀??
- 用mysql开放了一套*公开源代码的聊天软件,我们共同进步!
- 要学数据库那方面的知识,最好选什么书籍学啊??
- MySql 4.0新功能大讨论!!!
- MYSQL的查询结果可不可以按最近修改排序
- Redhat9下安装MYSQL时出现的问题
试下这个:
UPDATE ad_blog_spot_summary a
inner join
(SELECT `date`,sum(b.payment) as new_payment
FROM ad_blog_spot_disptype_summary b
WHERE spotid = 251 group by `date`) c
SET a.payment = c.new_payment
on a.date = c.date
WHERE a.spotid = 251
AND TO_DAYS(DATE_FORMAT('20091128','%Y%m%d')) <= TO_DAYS(a.date)
AND TO_DAYS(DATE_FORMAT('20091228','%Y%m%d')) >= TO_DAYS(a.date)
on a.date = c.date--------------->on a.date = c.date
SET a.payment = c.new_payment
(SELECT `date`,sum(b.payment) as new_payment
FROM ad_blog_spot_disptype_summary b
WHERE spotid = 251 group by `date`) c 这个的结果存为一个表然后再操作吧