with t
as(
select
a.id,
(select sum([数量]) from test b where b.id<=a.ID) as [累加值]
from
test a
)
update test
set [累加值]=t.[累加值] from t where test.ID=t.ID 运行上述语句提示“在关键字 'with' 附近有语法错误。”请各位帮忙看看到时是哪里有问题
as(
select
a.id,
(select sum([数量]) from test b where b.id<=a.ID) as [累加值]
from
test a
)
update test
set [累加值]=t.[累加值] from t where test.ID=t.ID 运行上述语句提示“在关键字 'with' 附近有语法错误。”请各位帮忙看看到时是哪里有问题
解决方案 »
- 存储过程语法!!!!!!!!!!!!!
- sql 2000 sp4 应该装哪个版本..
- 请问:Transact-sql语句问题?
- 安装sqlserver是出现安装路径最大允许长度为31个字符,如何解却?
- 为什么我在windows XP SP1中不能装sql server2000标准版?出现问题如下:
- 表里面有二条空白纪录行,请问如何删除
- 请问那有SQLserver2000的中文帮助下载。(因本人用的是7.0)
- 征求数据同步的技术解决方案,谢谢?
- 同样的程序,为何对小表成立,对大表不管用(sqlserver2000)?
- 数据库服务器选择哪一款?
- 在SQL的查询分析器中如何使用“SCAN……ENDSCAN”循环语句?
- 求助SQL2008问题
as(
select
a.id,
(select sum([数量]) from test b where b.id<=a.ID) as [累加值]
from
test a
)
update test
set [累加值]=t.[累加值] from test,t where test.ID=t.ID
2000不支持with
WITH t AS ( SELECT a.id ,
( SELECT SUM([数量])
FROM test b
WHERE b.id <= a.ID
) AS [累加值]
FROM test a
)
UPDATE test
SET [累加值] = t.[累加值]
FROM t
WHERE test.ID = t.ID
GO---->生成表test
--
--if object_id('test') is not null
-- drop table test
--Go
--Create table test([id] smallint,[数量] smallint,[累加值] smallint)
--Insert into test
--Select 1,1,0
--Union all Select 2,2,0
--Union all Select 3,3,0
--Union all Select 4,4,0
--Union all Select 5,5,0;with t
as(
select
a.id,
累加值,
(select sum([数量]) from test b where b.id<=a.ID) as [Sum_累加值]
from
test a
)
UPDATE t SET 累加值=Sum_累加值
SELECT * FROM test/*
id 数量 累加值
------ ------ ------
1 1 1
2 2 3
3 3 6
4 4 10
5 5 15
*/
2000不支持with 我的是2000的,with只支持2005吗?
建议使用表变量或者临时表来处理类似问题。
as 写法)
drop table test
Go
Create table test([id] smallint,[数量] smallint,[累加值] smallint)
Insert into test
Select 1,1,0
Union all Select 2,2,0
Union all Select 3,3,0
Union all Select 4,4,0
Union all Select 5,5,0 select ID,数量,累加值=(select sum(数量) as 累加值 from test where ID <=a.ID)
from test a
order by idID 数量 累加值
------ ------ -----------
1 1 1
2 2 3
3 3 6
4 4 10
5 5 15(所影响的行数为 5 行)
AS
(
SELECT *
FROM Sales
)
select *
FROM T
上面这个而不会报错
;WITH T
AS
(
SELECT *
FROM Sales
)会报错;