T-SQL使用CTE自动增量

Question

我在使用公用表表达式进行简单的自动增量时遇到问题。 （我不需要 ROW_NUMBER() 因为我将使用递增条件）所以这是我的问题的简化版本，但仍然不起作用。当我尝试以下操作时，我收到此错误：“无效的列名称 'n'”：

 WITH NumberSongPair ( n,s ) AS (
    SELECT 0 as n,SongKey as s from Songs where SongKey = 1
    UNION ALL
    SELECT 1 + n as n,SongKey as s 
    from Songs 
    WHERE n < 500 )
SELECT n,s FROM NumberSongPair
OPTION ( MAXRECURSION 500 )

为什么它不能将 'n' 识别为新创建的增量列？只要我在从表中选择列时能够不断增加数字，我什至会很高兴完全摆脱第一个选择。

如果您对我的总体目标感到好奇，那就是对歌曲进行排名...，其条件如下：

 WITH Nbrs ( base, n,ctr ) AS (
    SELECT 0,0,0 UNION ALL
    SELECT 1 + base,'n' = case 
                        when (base + 1)%2=0 then ctr
                        when ctr <=20 then ctr
                        else null end,
                 'ctr' = case
                        when (base + 1)%2=0 then ctr + 1
                        else ctr end
    FROM Nbrs WHERE base < 500 )
SELECT n FROM Nbrs
OPTION ( MAXRECURSION 500 )

编辑...

抱歉，我的解释不是很好。我只是想添加另一个伪自动增量列，该列有时会变为空而不是增量。基本上我正在尝试这样的事情：

declare @songs table (songkey int, dropable bit, points int)

insert @songs values (1, 1, 1); insert @songs values (2, 1, 20);
insert @songs values (3, 1, 3); insert @songs values (3, 0, 11); 
insert @songs values (4, 0, 4); insert @songs values (6, 0, 2); 

select row_number() over(order by points desc) as RankingPosition, songkey, dropable, points 
from @Songs

但是添加以下条件：“可删除”歌曲如果没有进入前 3 名，就会失去排名（为空）。这意味着 SongKey #2 不会下降，但 #4和＃6会。因此，输出将如下所示：

• PositionRank 1 - Songkey 2
• PositionRank 2 - Songkey 3
• PositionRank 3 - Songkey 4
• PositionRank 4 - Songkey 6
• PositionRank null - Songkey 4
• PositionRank null - Songkey 1

排名由分数决定，但有条件。

Answer 1

WITH songs (songkey, dropable, points) AS (
  SELECT 1, 1,  1 UNION ALL
  SELECT 2, 1, 20 UNION ALL
  SELECT 3, 1,  3 UNION ALL
  SELECT 4, 0,  4 UNION ALL
  SELECT 5, 0, 11 UNION ALL
  SELECT 6, 0,  2
),
preliminaryRanking AS (
  SELECT
    rank1 = ROW_NUMBER() OVER (ORDER BY points DESC),
    *
  FROM songs
),
finalRanking AS (
  SELECT
    rank2 = ROW_NUMBER() OVER (
      ORDER BY
        CASE
          WHEN rank1 <= 3 OR dropable = 0 THEN rank1
          ELSE CAST(0x7FFFFFFF AS int)
        END
    ),
    *
  FROM preliminaryRanking
)
SELECT
  PositionRank = CASE WHEN rank1 <= 3 OR dropable = 0 THEN rank2 END,
  songkey, dropable, points
FROM finalRanking
ORDER BY rank1

输出：

PositionRank         songkey     dropable    points
-------------------- ----------- ----------- -----------
1                    2           1           20
2                    5           0           11
3                    4           0           4
NULL                 3           1           3
4                    6           0           2
NULL                 1           1           1

Answer 2

你应该使用 rownumber，我不明白你想要什么 100%，但这是一个如何使用 rownumber 的例子。您当前脚本不起作用的原因是您尚未将 NumberSongPair 和 Songs 加入联合部分。看来你没有什么可以加入他们的。

declare @songs table (songkey int, base int, ctr int)

insert @songs values (1, 1, 1)
insert @songs values (2, 1, 1)
insert @songs values (3, 1, 1)

;WITH wrn AS (
select row_number() over(order by (select 1)) [rn], * from @Songs)
,a as
(
SELECT case 
when (base + 1)%2=0 then ctr
when ctr <=20 then ctr
else null end n,
case when (base + 1)%2=0 then ctr + 1
else ctr end CTR FROM WRN
)
SELECT n FROM a

如果你想让我写一个脚本来做你想做的事，请向我提供一些示例数据、预期结果及其背后的逻辑。