2条回答 默认 最新
- benbenli 2021-05-11 13:07关注
第一题定义了一个函数循环检查前一天类型是否为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
本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 1无用