在Server中使用递归CTE获取所有ids的数据

Question

我试图在sql中使用递归CTE来获取表中每个唯一id的数据。我正在阅读本教程，但无法让我的代码工作。

我的逻辑是

for each unique id in a table = currentId
select * from table
where 
date between
max available date of that currentId
and 
5 days before that max available date of that currentId

我试过这样做

WITH cte_org AS (
    SELECT * FROM shoes 
    UNION ALL
    SELECT * 
    FROM cte_org 
    WHERE  
    CAST(startTime AS date) 
    BETWEEN  DATEADD(DAY,-30, (select max(CAST(startTime as date)) from shoes where shoeId = cte_org.shoeId )  )
    AND (select max(CAST(startTime as date)) from shoes where shoeId = cte_org.shoeId )
)

 SELECT * FROM cte_org

我得到了

在递归公共表表达式“cte_org”的递归部分不允许将SQL 467分组为、拥有或聚合函数。

有什么帮助吗？

编辑

预期结果

for shoe id 1
time 14/1/2020 - color black
time 13/1/2020 - color yellow
time 12/1/2020 - color pink
time 11/1/2020 - color orange
time 10/1/2020 - color green

for shoe id 2
time 14/1/2020 - color white
time 13/1/2020 - color green
time 12/1/2020 - color black
time 11/1/2020 - color blue
time 10/1/2020 - color brown

for shoe id 3....etc

编辑2

id   time          color
1    14/1/2020     pink
2    14/1/2020     black
3    14/1/2020     orange

Answer 1

    WITH cte_org AS (
        SELECT * FROM shoes 
        UNION ALL
        SELECT * 
        FROM cte_org 
        WHERE  
        CAST(startTime AS date) 
        BETWEEN  DATEADD(DAY,-30, (select max(CAST(startTime as date)) from shoes where shoeId = cte_org.shoeId )  )
        AND (Select top 1 CAST(startTime as date) 
 from shoes where shoeId = cte_org.shoeId 
Order by startTime desc)
    )

 SELECT * FROM cte_org

或

    WITH cte_org AS (
        SELECT * FROM shoes 
        UNION ALL
        SELECT * 
        FROM cte_org 
        WHERE  
        CAST(startTime AS date) 
        BETWEEN  DATEADD(DAY,-30, (select max(CAST(startTime as date)) from shoes where shoeId = cte_org.shoeId )  )
        AND (Select Max(CAST(startTime as date)) OVER ( Partition by shoeId) AS max_date
 from shoes where shoeId = cte_org.shoeId )
    )

 SELECT * FROM cte_org

请尝试以上查询，如果有任何语法错误，请尝试更正，但这是正确的逻辑。