27,580
社区成员
发帖
与我相关
我的任务
分享
DECLARE @T TABLE(客户代码 varchar(50),日期 int,发生额 numeric(19,6),合计 numeric(19,6))
INSERT INTO @T
SELECT 'A',20170430,100,100 UNION ALL
SELECT 'A',20170530,-100,0 UNION ALL
SELECT 'A',20170630,300,300 UNION ALL
SELECT 'A',20170730,500,800
--SELECT * FROM @T
DECLARE @T TABLE(客户代码 varchar(50),日期 int,发生额 numeric(19,6),合计 numeric(19,6))
INSERT INTO @T
SELECT 'A',20170430,100,100 UNION ALL
SELECT 'A',20170530,-100,0 UNION ALL
SELECT 'A',20170630,300,300 UNION ALL
SELECT 'A',20170730,500,800 union ALL
SELECT 'B',20170730,1000,1000
DECLARE @T TABLE(客户代码 varchar(50),日期 int,发生额 numeric(19,6),合计 numeric(19,6))
INSERT INTO @T
SELECT 'A',20170430,100,100 UNION ALL
SELECT 'A',20170530,-100,0 UNION ALL
SELECT 'A',20170630,300,300 UNION ALL
SELECT 'A',20170730,500,800 union ALL
SELECT 'B',20170730,1000,1000
DECLARE @T TABLE(客户代码 varchar(50),日期 int,发生额 numeric(19,6),合计 numeric(19,6))
INSERT INTO @T
SELECT 'A',20170430,100,100 UNION ALL
SELECT 'A',20170530,-100,0 UNION ALL
SELECT 'A',20170630,300,300 UNION ALL
SELECT 'A',20170730,500,800
;WITH lt0 as (
SELECT 客户代码,max(日期) as 日期 FROM @T
WHERE 合计<=0
GROUP BY 客户代码
)
SELECT * FROM @t a WHERE EXISTS(
SELECT 1 FROM lt0 AS b
WHERE
a.客户代码=b.客户代码
AND a.日期>b.日期
)
/*
客户代码 日期 发生额 合计
A 20170630 300.000000 300.000000
A 20170730 500.000000 800.000000
*/
DECLARE @T TABLE(客户代码 varchar(50),日期 int,发生额 numeric(19,6),合计 numeric(19,6))
INSERT INTO @T
SELECT 'A',20170430,100,100 UNION ALL
SELECT 'A',20170530,-100,0 UNION ALL
SELECT 'A',20170630,300,300 UNION ALL
SELECT 'A',20170730,500,800
;WITH lt0 as (
SELECT 客户代码,max(日期) as 日期 FROM @T
WHERE 发生额<0
GROUP BY 客户代码
)
SELECT * FROM @t a WHERE EXISTS(
SELECT 1 FROM lt0 AS b
WHERE
a.客户代码=b.客户代码
AND a.日期>b.日期
)
/*
客户代码 日期 发生额 合计
A 20170630 300.000000 300.000000
A 20170730 500.000000 800.000000
*/
DECLARE @T TABLE(客户代码 varchar(50),日期 int,发生额 numeric(19,6),合计 numeric(19,6))
INSERT INTO @T
SELECT 'A',20170430,100,100 UNION ALL
SELECT 'A',20170530,-100,0 UNION ALL
SELECT 'A',20170630,300,300 UNION ALL
SELECT 'A',20170730,500,800
DELETE FROM @T
WHERE 日期 <= ( SELECT MAX(日期) AS 日期
FROM @T
WHERE 合计 = 0
)
SELECT * FROM @T
DECLARE @T TABLE(客户代码 varchar(50),日期 int,发生额 numeric(19,6),合计 numeric(19,6))
INSERT INTO @T
SELECT 'A',20170430,100,100 UNION ALL
SELECT 'A',20170530,-100,0 UNION ALL
SELECT 'A',20170630,300,300 UNION ALL
SELECT 'A',20170730,500,800
;WITH cte AS (
SELECT * ,row_number() over(ORDER BY 日期) as rn FROM @T
)
--删除
DELETE FROM cte WHERE rn<=(
SELECT rn FROM cte WHERE 合计=0
)
SELECT * FROM @t