如何找到表

  • Z时代
  • 2024-01-10
  • 分类：问答

前6列下面是我在叫TEMP2表中的列，如何找到表

[ID],[Week1], [Week2],[Week3],[Week4],[Week5], 
[Week6],[Week7],[Week8],[Week9], [Week10], 
[Week11],[Week12],[Week13], [Week14],[Week15], 
[Week16],[Week17], [Week18],[Week19],[Week20], 
[Week21], [Week22],[Week23],[Week24],[Week25], [Week26]

我想找到的每一行并显示顶层6条。

下面是我尝试使用相同的查询，并没有获取预期的结果。我在这里做错了什么？

SELECT 
    id, [1], [2], [3],[4], [5], [6] 
    FROM 
    (SELECT 
     id, Val, rn 
     FROM 
     (SELECT id, Val, Col, 
      ROW_NUMBER()OVER (PARTITION BY id ORDER BY Val) 
      AS rn 
       FROM 
       TEMP2 
    UNPIVOT 
    (Val FOR Col IN ([Week1], 
    [Week2],[Week3],[Week4],[Week5],[Week6],[Week7],[Week8],[Week9], 
    [Week10],[Week11],[Week12],[Week13],[Week14],[Week15],[Week16],[Week17], 
    [Week18],[Week19],[Week20],[Week21],[Week22],[Week23],[Week24],[Week25], 
    [Week26]))AS unpvt) AS t 
     WHERE 
     t.rn <= 6) AS src 
      PIVOT 
      (MAX(Val) FOR rn IN ([1], [2], [3],[4], [5], [6])) AS pvt

我使用SQL Server 2008的

回答：

正如上面的意见建议Damien_The_Unbeliever，看来你只需要ORDER BY Val DESC在PARTITION BY声明，先返回值最大。

这为我工作在我设置了一些测试数据：

SELECT 
    id, [1], [2], [3], [4], [5], [6] 
    FROM 
    (SELECT 
     id, Val, rn 
     FROM 
     (SELECT id, Val, Col, 
      ROW_NUMBER()OVER (PARTITION BY id ORDER BY Val DESC) 
      AS rn 
       FROM 
       TEMP2 
    UNPIVOT 
    (Val FOR Col IN ([Week1], 
    [Week2],[Week3],[Week4],[Week5],[Week6],[Week7],[Week8],[Week9], 
    [Week10],[Week11],[Week12],[Week13],[Week14],[Week15],[Week16],[Week17], 
    [Week18],[Week19],[Week20],[Week21],[Week22],[Week23],[Week24],[Week25], 
    [Week26]))AS unpvt) AS t 
     WHERE 
     t.rn <= 6) AS src 
      PIVOT 
      (MAX(Val) FOR rn IN ([1], [2], [3], [4], [5], [6])) AS pvt

以上是 如何找到表 的全部内容， 来源链接： utcz.com/qa/265094.html

回到顶部