sql问题，求大神给demo

Question

 

Answer 1

第一题定义了一个函数循环检查前一天类型是否为B并累计工期。

CREATE TABLE Project
(
	Type Char(1),
	Duration Int,
	Sequence Date
);

INSERT INTO Project
VALUES  ('A', 10, '2021-01-01'),
		('A', 10, '2021-01-02'),
		('B', 10, '2021-01-03'),
		('A', 10, '2021-01-04'),
		('B', 10, '2021-01-05'),
		('B', 10, '2021-01-06'),
		('B', 10, '2021-01-07'),
		('A', 10, '2021-01-08'),
		('B', 10, '2021-01-09'),
		('A', 10, '2021-01-10');


IF OBJECT_ID (N'dbo.udfRollupPreviousDuration', N'FN') IS NOT NULL  
    DROP FUNCTION udfRollupPreviousDuration;  
GO  
CREATE FUNCTION dbo.udfRollupPreviousDuration(@Sequence Date)  
RETURNS int
AS   
BEGIN
	DECLARE @PreviousDay DATE;
    DECLARE @Type Char(1);  
	DECLARE @Duration Int;
	DECLARE @TotalDuration Int

	SET @TotalDuration = 0
	SET @PreviousDay = DATEADD(DAY, -1, @Sequence);

	WHILE 1 = 1
	BEGIN
		--PRINT CONCAT('@PreviousDay=', @PreviousDay);
		SELECT	@Type = Type,
				@Duration = Duration
		FROM	Project   
		WHERE	Sequence = @PreviousDay

		IF @Type IS NULL OR @Type <> 'B' 
			BREAK;  
	 
		--PRINT CONCAT('@Duration=', STR(@Duration));
		SET @TotalDuration = @TotalDuration + @Duration;
		SET @PreviousDay = DATEADD(DAY, -1, @PreviousDay);
	END

	RETURN @TotalDuration;
END; 

SELECT	Sequence,
		Duration + dbo.udfRollupPreviousDuration(Sequence) AS TotalDuration
FROM	Project
ORDER BY Sequence
;


// Results:
2021-01-01	10
2021-01-02	10
2021-01-03	10
2021-01-04	20
2021-01-05	10
2021-01-06	20
2021-01-07	30
2021-01-08	40
2021-01-09	10
2021-01-10	20

第二题比较容易，分别用子铲鲟求和再相减。

SELECT	(SELECT SUM(Amt) FROM Receivable) - (SELET SUM(Amt) FROM Payable) AS Balance