时间戳

首页 > 解决方案 > sql 获取全面计数

sql - sql 获取全面计数

问题描述

我有一张桌子，上面有回合和以前的回合

如果我给回合=13

- 逻辑 -

前 13 个是 7 => 计数 1

前 7 个是 5 => 计数 2

前 5 个是 1 => 计数 3

我应该在 sql server 中计为 3

============================

| Round  | Previous round  |

============================

|  1     | Null            |
|  2     | Null            |
|  3     | 2               |
|  4     | Null            |   
|  5     |    1            |
|  6     |    4            |   
|  7     |    5            |    
|  13    |    7            |

============================

标签： sqlsql-servertsql

解决方案

此代码将为您提供正确的输出。现在测试一下，这个让我大吃一惊，但值得。我添加了一些额外的，比如 26 用于测试目的。

DECLARE @round int=13

DECLARE @table table(currentround int,previousround int)
insert into @table values
(1,NULL),
(2,NULL),
(3,2),
(4,NULL),
(5,1),
(7,5),
(13,7),
(20,13),
(26,3),
(17,8),
(25,20)


DECLARE @Id int
declare @count int=0
select @Id = @round

while(select count(*)
      from @table c
      where c.currentround <= @Id) > 0

      Begin


select @count =( select @count+1-count(*) From @table curr
left join @table prev
on curr.previousround=prev.currentround
where curr.currentround=@Id and curr.previousround  is null)

select @Id = (Select  prev.currentround 
From @table curr
left join @table prev
on curr.previousround=prev.currentround
where curr.currentround=@Id )

END
select(@count)

推荐阅读