如何把几条语句并起来更新到原表中

[moshangdanqing]

id customer date money type
00023351 010006 2009-09-27 20:10 58.00 特价
00023372 030031 2009-09-27 21:33 69.00 特价
00023447 040102 2009-09-28 21:12 69.00 特价
00023448 020173 2009-09-28 21:12 69.00 特价
00023449 040017 2009-09-28 21:13 69.00 特价
00023464 030132 2009-09-28 21:37 81.00 正常
00023465 020145 2009-09-28 21:40 60.00 正常
00023466 030087 2009-09-28 21:45 72.00 正常
00023467 020102 2009-09-28 21:46 51.00 正常
00023469 040051 2009-09-28 21:51 60.00 正常
00023472 010299 2009-09-28 22:00 60.00 正常
00023496 010128 2009-09-29 19:11 72.00 特价
00023499 040239 2009-09-29 19:16 51.00 特价
00023500 030108 2009-09-29 19:16 60.00 特价
00023501 030089 2009-09-29 19:16 72.00 特价
00023502 030068 2009-09-29 19:16 81.00 特价
00023503 020013 2009-09-29 19:16 60.00 特价
00023504 010247 2009-09-29 19:16 162.00 特价
00023505 020255 2009-09-29 19:16 51.00 特价
00023506 020132 2009-09-29 19:16 60.00 特价
00023507 010229 2009-09-29 19:16 81.00 特价
00023518 020179 2009-09-29 20:03 81.00 特价
00023521 020183 2009-09-29 20:42 81.00 特价
00023526 010149 2009-09-29 20:58 77.00 特价
00023607 010078 2009-09-30 19:46 81.00 特价
00023608 020051 2009-09-30 19:46 77.00 特价
00023609 010158 2009-09-30 19:46 60.00 特价
00023610 040107 2009-09-30 19:46 81.00 特价
00023611 010044 2009-09-30 19:46 180.00 特价
00023612 030182 2009-09-30 19:46 120.00 特价
00023613 030068 2009-09-30 19:46 51.00 特价
00023614 040020 2009-09-30 19:46 72.00 特价
00023615 030073 2009-09-30 19:46 81.00 特价
00023616 020029 2009-09-30 19:46 60.00 特价
00023617 030052 2009-09-30 19:46 72.00 特价
00023618 020240 2009-09-30 19:46 60.00 特价
00023619 010170 2009-09-30 19:46 81.00 特价
00023620 020076 2009-09-30 19:46 81.00 特价
00023621 010233 2009-09-30 19:46 72.00 特价
00023622 040194 2009-09-30 19:47 60.00 特价
00023627 020089 2009-09-30 19:55 81.00 特价
00023630 030062 2009-09-30 20:09 77.00 特价
00023631 020098 2009-09-30 20:15 81.00 特价
00023632 020092 2009-09-30 20:20 81.00 特价
00023637 010099 2009-09-30 21:02 81.00 特价
00023642 010171 2009-09-30 21:09 79.00 特价
00023645 010225 2009-09-30 21:20 60.00 特价
00023738 010077 2009-10-01 19:52 77.00 特价
00023739 030267 2009-10-01 19:52 72.00 特价
00023740 010167 2009-10-01 19:52 81.00 特价
00023741 030191 2009-10-01 19:52 81.00 特价
00023742 020054 2009-10-01 19:52 60.00 特价
00023743 020198 2009-10-01 19:52 60.00 特价
00023744 010150 2009-10-01 19:52 77.00 特价
00023745 020020 2009-10-01 19:52 72.00 特价
00023746 010080 2009-10-01 19:52 60.00 特价
00023747 040224 2009-10-01 19:52 51.00 特价
00023748 020045 2009-10-01 19:52 81.00 特价
00023758 040087 2009-10-01 20:29 60.00 特价
00023762 040127 2009-10-01 20:44 60.00 特价
00023778 010170 2009-10-01 21:38 60.00 特价
00023782 010063 2009-10-01 21:41 -60.00 退货
00023783 020130 2009-10-01 21:42 60.00 特价
00023872 040238 2009-10-02 20:36 72.00 特价
00023873 030189 2009-10-02 20:36 60.00 特价
00023874 010099 2009-10-02 20:36 72.00 特价
00023875 030102 2009-10-02 20:36 77.00 特价
00023876 030175 2009-10-02 20:36 144.00 特价
00023877 010027 2009-10-02 20:36 72.00 特价
00023878 020030 2009-10-02 20:36 81.00 特价
00023879 010193 2009-10-02 20:36 81.00 特价
00023880 040265 2009-10-02 20:36 60.00 特价
00023881 030108 2009-10-02 20:36 72.00 特价
00023882 010130 2009-10-02 20:36 81.00 特价
00023883 010048 2009-10-02 20:36 120.00 特价
00023884 010233 2009-10-02 20:36 77.00 特价
00023885 020010 2009-10-02 20:36 72.00 特价
00023962 040004 2009-10-03 19:41 72.00 特价
00023963 040157 2009-10-03 19:41 72.00 特价
00023964 020286 2009-10-03 19:41 81.00 特价
00023965 030222 2009-10-03 19:41 72.00 特价
00023966 010223 2009-10-03 19:41 60.00 特价
00023967 010094 2009-10-03 19:41 81.00 特价
00023968 040045 2009-10-03 19:41 60.00 特价
00023977 040118 2009-10-03 20:58 60.00 特价
00023978 010070 2009-10-03 20:58 72.00 特价
00023979 020054 2009-10-03 20:58 60.00 特价
00024052 040008 2009-10-04 20:10 72.00 特价
00024053 010183 2009-10-04 20:10 60.00 特价
00024054 040005 2009-10-04 20:10 81.00 特价
00024055 020253 2009-10-04 20:10 81.00 特价
00024056 030060 2009-10-04 20:10 81.00 特价
00024057 030231 2009-10-04 20:10 180.00 特价
00024058 040010 2009-10-04 20:10 51.00 特价
00024059 030110 2009-10-04 20:11 -60.00 退货
00024060 040176 2009-10-04 20:11 60.00 特价
00024075 030022 2009-10-04 21:45 60.00 特价
这是表中数据。我想得到R：最近一次customer中 money>0离2010-9-27的天数 F：custom大于0的次数减去customer小于0的次数 M：customer对应money总和。对于R：通过select *,datediff('2010-9-27 19:30:36',max(date)) as R from rfm2
where date<="2010-09-30 19:30:36" and money>0 group by customer能够实现。
对于F: select customer,count(1) as sum1 from rfm2 c where money>0 group by customer,这个能够得到>0的次数，把money<0可得到小于0的次数。怎么实现大于0减去小于0的次数呢。对于M：select customer,sum(money) as M from rfm2 group by customer;这个能实现。想问下如何能够得到R和让R、F、M一同更新于表中

[firer2006]

select customer,R,sum1 -sum2,M from (select customer,datediff('2010-9-27 19:30:36',max(date)) as R from rfm2 where date<="2010-09-30 19:30:36" and money>0 group by customer)a, ( select customer,count(1) as sum1 from rfm2 c where money>0 group by customer)b, ( select customer,count(1) as sum2 from rfm2 c where money<0 group by customer)c, (select customer,sum(money) as M from rfm2 group by customer)d where a.customer=b.customer and a.customer=c.customer and a.customer=d.customer

[moshangdanqing]

引用 1 楼 firer2006 的回复:

select customer,R,sum1 -sum2,M from (select customer,datediff('2010-9-27 19:30:36',max(date)) as R from rfm2 where date<="2010-09-30 19:30:36" and money>0 group by customer)a, ( select customer,count(1) as sum1 from rfm2 c where money>0 group by customer)b, ( select customer,count(1) as sum2 from rfm2 c where money<0 group by customer)c, (select customer,sum(money) as M from rfm2 group by customer)d where a.customer=b.customer and a.customer=c.customer and a.customer=d.customer

运行报错，显示 Column 'customer' in field list is ambiguous

[benpao002]

把select customer,R,sum1 -sum2,M 这句改为b.customer看看