菜鸟求助Access的汇总问题

vinson01

想用查询汇总，但是教程看了半个多月了，还是搞不懂（这智商{:soso_e140:}），现在求助大侠们给支个招

数据


想实现的效果:
(汇总计算向下3、5期)

amazeyeli

[code=sql]USE tempdb
IF OBJECT_ID('TEST') IS NOT NULL
  DROP TABLE TEST;      -->如果表TEST不为空,删除表
GO
CREATE TABLE TEST       -->创建测试表,字段及类型如下
(
  id    int,
  num   int
);
GO
INSERT INTO TEST VALUES -->向表中插入数据
(1,100),
(2,200),
(3,300),
(4,400),
(5,500);
GO
SELECT * FROM TEST      -->表的结构如下

/*
id        num
1        100
2        200
3        300
4        400
5        500
*/

-------------------开始查询------------------

SELECT id,SUM(num1+num2+num3) AS num_sum
FROM
(
  SELECT A.id,A.num AS num1,B.num AS num2,C.num AS num3
  FROM TEST AS A
  JOIN TEST AS B
  ON A.id = B.id - 1
  JOIN TEST AS C
  ON A.id = C.id - 2
) AS T
GROUP BY id

-------------------运行结果------------------

/*
id          num_sum
----------- -----------
1           600
2           900
3           1200

(3 行受影响)

*/
[/code]

amazeyeli

[code=sql]SELECT id,SUM(ISNULL(num1,0)+ISNULL(num2,0)+ISNULL(num3,0)) AS num_sum
FROM
(
  SELECT A.id,A.num AS num1,B.num AS num2,C.num AS num3
  FROM TEST AS A
  LEFT JOIN TEST AS B
  ON A.id = B.id - 1
  LEFT JOIN TEST AS C
  ON A.id = C.id - 2
) AS T
GROUP BY id

-------------------运行结果------------------

/*
id          num_sum
----------- -----------
1           600
2           900
3           1200
4           900
5           500

(5 行受影响)

*/[/code]

vinson01

大侠谢谢回复，但真不好意思，折腾了一天也没搞明白在Access2010中怎么用{:soso_e127:}，{:soso_e150:}帮忙再讲的详细些吧！

amazeyeli

语法不完全一样的, 思路你弄懂了没

vinson01

{:soso_e153:}没有懂。还有简单点的办法吗?

amazeyeli

你看的教程是什么样的?
图形化操作?
不讲SQL语句的?
如果是的话,把那本教程扔了吧
SQL是一种语言
语言是什么,就是你说出来,别人就能懂,你写出来,机器就能懂

vinson01

图形化操作，没讲sql，你看还有解决的办法么？
{:soso_e101:}

amazeyeli

[code=sql]SELECT id,IIF(num1 IS NULL,0,num1)+IIF(num2 IS NULL,0,num2)+IIF(num3 IS NULL,0,num3) AS num_sum
FROM
(
  SELECT A.id,A.num AS num1,B.num AS num2,C.num AS num3
  FROM
((TEST AS A
  LEFT JOIN TEST AS B
  ON A.id = B.id - 1)
  LEFT JOIN TEST AS C
  ON A.id = C.id - 2)
) AS T
[/code]

vinson01

{:soso_e179:}谢谢，辛苦大侠了，正在研究中{:soso_e134:}