1条回答 默认 最新
- benbenli 2021-05-11 21: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
本回答被题主选为最佳回答 , 对您是否有帮助呢?解决 无用评论 打赏 举报
悬赏问题
- ¥15 iOS 自定义输入法-第三方输入法
- ¥15 很想要一个很好的答案或提示
- ¥15 扫描项目中发现AndroidOS.Agent、Android/SmsThief.LI!tr
- ¥15 怀疑手机被监控,请问怎么解决和防止
- ¥15 Qt下使用tcp获取数据的详细操作
- ¥15 idea右下角设置编码是灰色的
- ¥15 全志H618ROM新增分区
- ¥15 在grasshopper里DrawViewportWires更改预览后,禁用电池仍然显示
- ¥15 NAO机器人的录音程序保存问题
- ¥15 C#读写EXCEL文件,不同编译