原贴:http://topic.csdn.net/u/20100326/10/cdac97d6-55e4-4b66-8143-cfc819ed0a9c.html?seed=881429327
数据:
date itemcode qty 2010-03-01 A 10 2010-03-12 A -3 2010-02-12 A -5 2010-03-14 A -8 2010-03-15 A 10 2010-03-01 B 12 2010-03-02 B 13 ---结果 date itemcode qty 2010-03-01 A 5 2010-03-01 B 12 2010-03-02 B 13 A 3月1号 加上 2月12 但是不能加3月12的 A 3月15号 加上 2个3月 12 小于0 去掉 应该加上2月12的,但是已经满足了3月1号 B 03月01号之前没有负数,不变 B 03月02号之前没有负数,不变 |
-------------------------------------------------------------------------- -- Author : htl258(Tony) -- Date : 2010-03-26 16:24:15 -- Version:Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86) -- Mar 29 2009 10:27:29 -- Copyright (c) 1988-2008 Microsoft Corporation -- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2) -------------------------------------------------------------------------- --> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL DROP TABLE [tb] GO CREATE TABLE [tb]([date] DATETIME,[itemcode] NVARCHAR(10),[qty] INT) INSERT [tb] SELECT N'2010-03-01','A',10 UNION ALL SELECT N'2010-03-12','A',-3 UNION ALL SELECT N'2010-02-12','A',-5 UNION ALL SELECT N'2010-03-14','A',-8 UNION ALL SELECT N'2010-03-15','A',10 UNION ALL SELECT N'2010-03-01','B',12 UNION ALL SELECT N'2010-03-02','B',13 GO --SELECT * FROM [tb] /* date itemcode qty ---------- -------- ----------- 2010-02-12 A -5 2010-03-01 A 10 2010-03-12 A -3 2010-03-14 A -8 2010-03-15 A 10 2010-03-01 B 12 2010-03-02 B 13 */ -->SQL查询如下: --1. select date, itemcode, sum(qty) qty from( select itemcode,qty, date = (select min(date) from tb b where a.itemcode = b.itemcode and b.date >= a.date and b.qty > 0 ) from tb a ) t group by date, itemcode having sum(qty) > 0 --2. select date, itemcode, sum(qty) qty from( select itemcode,qty, date = (select top 1 date from tb b where a.itemcode = b.itemcode and b.date >= a.date and b.qty > 0 order by date) from tb a ) t group by date, itemcode having sum(qty) > 0
/* date itemcode qty ----------------------- ---------- ----------- 2010-03-01 00:00:00.000 A 5 2010-03-01 00:00:00.000 B 12 2010-03-02 00:00:00.000 B 13
(3 行受影响) */
|
SQL另类分组累加
最新推荐文章于 2023-08-21 20:52:03 发布