计数sql行,将每一行的值添加到下一行
我有两列,我想将每一行的值添加到下一行。 我有两列4行表是这样的:计数sql行,将每一行的值添加到下一行
date users
2011-01-01 1
2011-02-02 1
2011-03-02 2
2011-04-02 4
,我希望用户显示如下图所示,并添加每一行到它的上一行值:
用户:
1
2
4
8
是否有一个MS SQL Server的功能呢?
感谢
在SQL Server 2 005及以上(您没有指定您的版本),您可以使用两个CTE(公用表表达式)做这样的事情 - 第一个叫做UserSequence
的CTE将数据放入订单并给它一个序号(Sequence
),以及第二递归CTE(公共表表达式)来计算运行总计:
;WITH UserSequence AS
(
SELECT
Date, Users, ROW_NUMBER() OVER(ORDER BY Date) as 'Sequence'
FROM
dbo.YourTable
),
UserValues AS
(
SELECT
u.Users AS 'UserValue', u.Date, u.Sequence
FROM UserSequence u
WHERE Sequence = 1
UNION ALL
SELECT
u.Users + uv.UserValue AS 'UserValue', u.Date, u.Sequence
FROM UserSequence u
INNER JOIN UserValues uv ON u.Sequence = uv.Sequence + 1
)
SELECT
Date, Sequence, UserValue AS 'Users'
FROM
UserValues
ORDER BY
Sequence
这应该给你的输出是这样的:
Date Sequence Users
2011-01-01 00:00:00.000 1 1
2011-02-02 00:00:00.000 2 2
2011-03-02 00:00:00.000 3 4
2011-04-02 00:00:00.000 4 8
这里是你如何能做到这一点:
set @total_users = 0;
select users, @total_users:[email protected]_users + users as total_users from tablename;
这不是有效的SQL。这是什么数据库系统? MySQL数据库: – 2011-02-12 08:46:20
MySQL。测试它,它的工作原理。 – 2011-02-12 08:53:19
在SQL Server中有一个类似的(无证的和不保证的)技术叫做“quirky update” – 2011-02-12 09:01:31
这是ANSI SQL的解决方案:
SELECT users + lag(users) over (order by users) FROM your_table ORDER BY users
作品在PostgreSQL里,甲骨文,DB2和Teradata(SQL服务器确实有窗口函数,但不幸的是不是lag()函数)
With cte_Test(Sequence,Name,Value)
As
(
select ROW_NUMBER() OVER(ORDER BY value) as 'Sequence' ,name,value from test
)
select t1.Name,sum(T2.Value) AS Value from cte_Test t1
cross JOIN cte_Test t2 where t2.Sequence <= t1.Sequence
group BY t1.Name
order by 2
这是可能通过交叉加入。如果您的表格列处于Asc顺序。
select <T1.Text>,sum(T2.Value) AS 'Commulative Value' from <Your Table name>t1
cross JOIN <Your Table name> t2 where t2.value<= t1.value
group BY t1.text
order by value
对不起是的,我应该用代码标记做到这一点。 – m0j1 2011-02-12 08:50:36