access表R, 结构(日期 站点 R2020),字段都为文本,
日期 站点 R2020
20000101 长沙 0001
20000102 长沙 0121
20000103 长沙 0131
20000104 长沙 0351
20000101 湘潭 0021
20000102 湘潭 0231
20000103 湘潭 0121
... ... ...要求出一个站点每年每月的和,如下:
年份 1月 2月 ... 年我用:
SQLstr:='Select x.*,(1月+2月+3月+4月+5月+6月+7月+8月+9月+10月+11月+12月)/12 as 年'
+' from(select left(A,4) as F0,'
+' sum(iif(right(A,2)=''01'',B,0)) as 1月,'
+' sum(iif(right(A,2)=''02'',B,0)) as 2月,'
+' sum(iif(right(A,2)=''03'',B,0)) as 3月,'
+' sum(iif(right(A,2)=''04'',B,0)) as 4月,'
+' sum(iif(right(A,2)=''05'',B,0)) as 5月,'
+' sum(iif(right(A,2)=''06'',B,0)) as 6月,'
+' sum(iif(right(A,2)=''07'',B,0)) as 7月,'
+' sum(iif(right(A,2)=''08'',B,0)) as 8月,'
+' sum(iif(right(A,2)=''09'',B,0)) as 9月,'
+' sum(iif(right(A,2)=''10'',B,0)) as 10月,'
+' sum(iif(right(A,2)=''11'',B,0)) as 11月,'
+' sum(iif(right(A,2)=''12'',B,0)) as 12月'
+' from(select left(日期,6) as A,sum(VAL(R2020)) as B from R group by left(A,6)) t group by left(A,4)) as x';
执行时说left(日期,6) 不包含合计部分。晕,请指点。
日期 站点 R2020
20000101 长沙 0001
20000102 长沙 0121
20000103 长沙 0131
20000104 长沙 0351
20000101 湘潭 0021
20000102 湘潭 0231
20000103 湘潭 0121
... ... ...要求出一个站点每年每月的和,如下:
年份 1月 2月 ... 年我用:
SQLstr:='Select x.*,(1月+2月+3月+4月+5月+6月+7月+8月+9月+10月+11月+12月)/12 as 年'
+' from(select left(A,4) as F0,'
+' sum(iif(right(A,2)=''01'',B,0)) as 1月,'
+' sum(iif(right(A,2)=''02'',B,0)) as 2月,'
+' sum(iif(right(A,2)=''03'',B,0)) as 3月,'
+' sum(iif(right(A,2)=''04'',B,0)) as 4月,'
+' sum(iif(right(A,2)=''05'',B,0)) as 5月,'
+' sum(iif(right(A,2)=''06'',B,0)) as 6月,'
+' sum(iif(right(A,2)=''07'',B,0)) as 7月,'
+' sum(iif(right(A,2)=''08'',B,0)) as 8月,'
+' sum(iif(right(A,2)=''09'',B,0)) as 9月,'
+' sum(iif(right(A,2)=''10'',B,0)) as 10月,'
+' sum(iif(right(A,2)=''11'',B,0)) as 11月,'
+' sum(iif(right(A,2)=''12'',B,0)) as 12月'
+' from(select left(日期,6) as A,sum(VAL(R2020)) as B from R group by left(A,6)) t group by left(A,4)) as x';
执行时说left(日期,6) 不包含合计部分。晕,请指点。
解决方案 »
- 怎么让这个按键起作用?
- delphi开发的客户端怎么无法连接9730端口?
- 如何读取并分离这些信息
- 如何获取 adoquery 的查询进度?
- 如何使FORM1中的ProgressBar进度持续三秒后进入到FORM2?
- 利用 adoquery 连接到 vfp 数据库,运行“selec * into dbf tbname from table” 语句没反应
- Delphi程序员的工资状况?
- 急急!!!给高分!!
- 付费求d7里窗体里如何嵌入WIN IE浏览器并打开指定网站
- 爆炸性内部新闻, 微软欲收购Borland公司,双方谈判已经进入最后阶段
- 使用CoolBar和Toolbar菜单融合的问题
- delphi获取指定窗体句柄
改成:
left(日期,6)
(1月+2月+3月+...
改成:
(x.1月+x.2月+x.3月+...
+' from(select left(A,4) as 年份,'
+' sum(iif(right(A,2)=''01'',B,0)) as 1月,'
+' sum(iif(right(A,2)=''02'',B,0)) as 2月,'
+' sum(iif(right(A,2)=''03'',B,0)) as 3月,'
+' sum(iif(right(A,2)=''04'',B,0)) as 4月,'
+' sum(iif(right(A,2)=''05'',B,0)) as 5月,'
+' sum(iif(right(A,2)=''06'',B,0)) as 6月,'
+' sum(iif(right(A,2)=''07'',B,0)) as 7月,'
+' sum(iif(right(A,2)=''08'',B,0)) as 8月,'
+' sum(iif(right(A,2)=''09'',B,0)) as 9月,'
+' sum(iif(right(A,2)=''10'',B,0)) as 10月,'
+' sum(iif(right(A,2)=''11'',B,0)) as 11月,'
+' sum(iif(right(A,2)=''12'',B,0)) as 12月'
+' from(select left(日期,6) as A,sum(VAL(R2020)) as B from R'
+' where 站点='+QuotedStr(ZHAN)+' and 日期 between '+ QuotedStr(startdate)+' and '+QuotedStr(enddate)
+' group by left(日期,6)) t group by left(A,4)) as x';在 (1月+2月+3月+4月+5月+6月+7月+8月+9月+10月+11月+12月)/12 as 年合计 出错。难得不支持中文?