现在有2个表,一个Product,一个是Data
数据结构是:
表Product
Product Name | Type
AAA
BBB
CCC
...
表Data
Product Name |Month | Count
AAA Jan 120
AAA Feb 100
BBB Jan 100
CCC Mar 200
...
现在要求获得Product表的全部Product Name作为第一列(已经可以用循环代码实现),但是之后的字段要求求年和1-12月的综合,如果用sql双表查询,会出现Product Name重复显示,是否能够实现如下的循环?
Product Name |Year|Jan|Feb|Mar......
AAA |220(1-12月总和)|120(1月总和)|100(2月总和) ......
BBB |100(1-12月总和)|100(1月总和)|0(2月总和) ......
CCC .....
DDD .....
......
我之前单个求和是用这个代码,但是product name太多了,几十个上百个,以后也会增加
sql="select sum(count) as year ,sum(iif(retMonth='January',count,0)) as Jan ,sum(iif(retMonth='February',count,0)) as Feb ,sum(iif(retMonth='March',count,0)) as Mar ,sum(iif(retMonth='April',count,0)) as Apr ,sum(iif(retMonth='May',count,0)) as May ,sum(iif(retMonth='June',count,0)) as Jun ,sum(iif(retMonth='July',count,0)) as Jul ,sum(iif(retMonth='August',count,0)) as Aug ,sum(iif(retMonth='September',count,0)) as Sep ,sum(iif(retMonth='October',count,0)) as Oct ,sum(iif(retMonth='November',count,0)) as Nov ,sum(iif(retMonth='December',count,0)) as Dec from Data where productname='AAA'"
谢谢,不知道表达的清楚不清楚
数据结构是:
表Product
Product Name | Type
AAA
BBB
CCC
...
表Data
Product Name |Month | Count
AAA Jan 120
AAA Feb 100
BBB Jan 100
CCC Mar 200
...
现在要求获得Product表的全部Product Name作为第一列(已经可以用循环代码实现),但是之后的字段要求求年和1-12月的综合,如果用sql双表查询,会出现Product Name重复显示,是否能够实现如下的循环?
Product Name |Year|Jan|Feb|Mar......
AAA |220(1-12月总和)|120(1月总和)|100(2月总和) ......
BBB |100(1-12月总和)|100(1月总和)|0(2月总和) ......
CCC .....
DDD .....
......
我之前单个求和是用这个代码,但是product name太多了,几十个上百个,以后也会增加
sql="select sum(count) as year ,sum(iif(retMonth='January',count,0)) as Jan ,sum(iif(retMonth='February',count,0)) as Feb ,sum(iif(retMonth='March',count,0)) as Mar ,sum(iif(retMonth='April',count,0)) as Apr ,sum(iif(retMonth='May',count,0)) as May ,sum(iif(retMonth='June',count,0)) as Jun ,sum(iif(retMonth='July',count,0)) as Jul ,sum(iif(retMonth='August',count,0)) as Aug ,sum(iif(retMonth='September',count,0)) as Sep ,sum(iif(retMonth='October',count,0)) as Oct ,sum(iif(retMonth='November',count,0)) as Nov ,sum(iif(retMonth='December',count,0)) as Dec from Data where productname='AAA'"
谢谢,不知道表达的清楚不清楚