T-SQL 通过添加和删除来计数项目
在 SQL Server 2008 中,我有 Table1,我想获得预期输出中所示的输出。 每次发生“计数”操作时,它都会添加/删除所有相关项目并显示总数。 假设所有项目都从 0 计数开始。
非常感谢。
CREATE TABLE Table1([Time] [smalldatetime] NULL,[Action] [nvarchar](10) NULL,[Item] [nchar](50) NULL)
insert into Table1 values('2009-08-22 20:34:02','Count','Apple')
insert into Table1 values('2009-08-22 20:34:03','Count','Banana')
insert into Table1 values('2009-08-22 20:34:04','Count','Mango')
insert into Table1 values('2009-08-22 20:34:05','Count','Black Berry')
insert into Table1 values('2009-08-22 20:34:06','Count','Orange')
insert into Table1 values('2009-08-22 20:34:07','Add','Apple')
insert into Table1 values('2009-08-22 20:34:08','Add','Banana')
insert into Table1 values('2009-08-22 20:34:09','Add','Mango')
insert into Table1 values('2009-08-22 20:34:10','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:11','Add','Banana')
insert into Table1 values('2009-08-22 20:34:12','Add','Mango')
insert into Table1 values('2009-08-22 20:34:13','Add','Mango')
insert into Table1 values('2009-08-22 20:34:14','Add','Mango')
insert into Table1 values('2009-08-22 20:34:15','Remove','Mango')
insert into Table1 values('2009-08-22 20:34:17','Count','Apple')
insert into Table1 values('2009-08-22 20:34:18','Add','Banana')
insert into Table1 values('2009-08-22 20:34:19','Add','Banana')
insert into Table1 values('2009-08-22 20:34:20','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:21','Add','Apple')
insert into Table1 values('2009-08-22 20:34:22','Add','Mango')
insert into Table1 values('2009-08-22 20:34:23','Add','Apple')
insert into Table1 values('2009-08-22 20:34:24','Add','Mango')
insert into Table1 values('2009-08-22 20:34:25','Remove','Apple')
insert into Table1 values('2009-08-22 20:34:26','Count','Mango')
insert into Table1 values('2009-08-22 20:34:27','Add','Apple')
insert into Table1 values('2009-08-22 20:34:28','Add','Banana')
insert into Table1 values('2009-08-22 20:34:29','Remove','Apple')
insert into Table1 values('2009-08-22 20:34:30','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:31','Add','Banana')
insert into Table1 values('2009-08-22 20:34:32','Add','Mango')
insert into Table1 values('2009-08-22 20:34:33','Count','Banana')
insert into Table1 values('2009-08-22 20:34:34','Add','Banana')
insert into Table1 values('2009-08-22 20:34:35','Add','Apple')
insert into Table1 values('2009-08-22 20:34:36','Remove','Mango')
insert into Table1 values('2009-08-22 20:34:37','Add','Apple')
insert into Table1 values('2009-08-22 20:34:38','Add','Mango')
insert into Table1 values('2009-08-22 20:34:39','Count','Apple')
insert into Table1 values('2009-08-22 20:34:40','Remove','Apple')
insert into Table1 values('2009-08-22 20:34:41','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:42','Add','Banana')
insert into Table1 values('2009-08-22 20:34:43','Add','Mango')
insert into Table1 values('2009-08-22 20:34:44','Count','Banana')
insert into Table1 values('2009-08-22 20:34:45','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:46','Remove','Apple')
insert into Table1 values('2009-08-22 20:34:47','Remove','Mango')
insert into Table1 values('2009-08-22 20:34:48','Add','Apple')
insert into Table1 values('2009-08-22 20:34:49','Add','Mango')
insert into Table1 values('2009-08-22 20:34:50','Count','Mango')
In SQL Server 2008, I have Table1 and I want to get the output as shown in expected output.
Every time Action 'Count' occurs, it add/removes all relevant items and show the total.
Assuming all items begin with 0 count.
Many Thanks.
CREATE TABLE Table1([Time] [smalldatetime] NULL,[Action] [nvarchar](10) NULL,[Item] [nchar](50) NULL)
insert into Table1 values('2009-08-22 20:34:02','Count','Apple')
insert into Table1 values('2009-08-22 20:34:03','Count','Banana')
insert into Table1 values('2009-08-22 20:34:04','Count','Mango')
insert into Table1 values('2009-08-22 20:34:05','Count','Black Berry')
insert into Table1 values('2009-08-22 20:34:06','Count','Orange')
insert into Table1 values('2009-08-22 20:34:07','Add','Apple')
insert into Table1 values('2009-08-22 20:34:08','Add','Banana')
insert into Table1 values('2009-08-22 20:34:09','Add','Mango')
insert into Table1 values('2009-08-22 20:34:10','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:11','Add','Banana')
insert into Table1 values('2009-08-22 20:34:12','Add','Mango')
insert into Table1 values('2009-08-22 20:34:13','Add','Mango')
insert into Table1 values('2009-08-22 20:34:14','Add','Mango')
insert into Table1 values('2009-08-22 20:34:15','Remove','Mango')
insert into Table1 values('2009-08-22 20:34:17','Count','Apple')
insert into Table1 values('2009-08-22 20:34:18','Add','Banana')
insert into Table1 values('2009-08-22 20:34:19','Add','Banana')
insert into Table1 values('2009-08-22 20:34:20','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:21','Add','Apple')
insert into Table1 values('2009-08-22 20:34:22','Add','Mango')
insert into Table1 values('2009-08-22 20:34:23','Add','Apple')
insert into Table1 values('2009-08-22 20:34:24','Add','Mango')
insert into Table1 values('2009-08-22 20:34:25','Remove','Apple')
insert into Table1 values('2009-08-22 20:34:26','Count','Mango')
insert into Table1 values('2009-08-22 20:34:27','Add','Apple')
insert into Table1 values('2009-08-22 20:34:28','Add','Banana')
insert into Table1 values('2009-08-22 20:34:29','Remove','Apple')
insert into Table1 values('2009-08-22 20:34:30','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:31','Add','Banana')
insert into Table1 values('2009-08-22 20:34:32','Add','Mango')
insert into Table1 values('2009-08-22 20:34:33','Count','Banana')
insert into Table1 values('2009-08-22 20:34:34','Add','Banana')
insert into Table1 values('2009-08-22 20:34:35','Add','Apple')
insert into Table1 values('2009-08-22 20:34:36','Remove','Mango')
insert into Table1 values('2009-08-22 20:34:37','Add','Apple')
insert into Table1 values('2009-08-22 20:34:38','Add','Mango')
insert into Table1 values('2009-08-22 20:34:39','Count','Apple')
insert into Table1 values('2009-08-22 20:34:40','Remove','Apple')
insert into Table1 values('2009-08-22 20:34:41','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:42','Add','Banana')
insert into Table1 values('2009-08-22 20:34:43','Add','Mango')
insert into Table1 values('2009-08-22 20:34:44','Count','Banana')
insert into Table1 values('2009-08-22 20:34:45','Remove','Banana')
insert into Table1 values('2009-08-22 20:34:46','Remove','Apple')
insert into Table1 values('2009-08-22 20:34:47','Remove','Mango')
insert into Table1 values('2009-08-22 20:34:48','Add','Apple')
insert into Table1 values('2009-08-22 20:34:49','Add','Mango')
insert into Table1 values('2009-08-22 20:34:50','Count','Mango')
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)
这可以通过左外自连接来实现,如下所示
然而,产生的结果与您显示的结果不同,可能是因为您犯了一些计算错误。无论如何尝试类似的方法。
This can be achieved with a left outer self join as shown below
However the results produced are different from what you have shown probably because you made some calculation mistakes. Anyway try similar approach.