SQL 中一组 100 列的每行最大值

发布于 2024-10-30 08:01:21 字数 878 浏览 1 评论 0原文

虽然我看到过这个问题的几个答案，但它们都倾向于依赖于最大的函数，该函数不断返回

“Msg 195，Level 15，State 10，Line 1” 'GREATEST' 不是公认的内置函数名称。”

我想知道一种构建查询的方法，该查询为每一行返回工件 id，以及该行中的最大值及其所在列。任何欢迎提出建议。我已经查看了此板上和其他板上的相当多的示例，但不断遇到各种错误，

为了查看的乐趣而缩短：

SELECT artifactID, GREATEST(V0,V1,V2,V3,V4,V5,V6...V98,V99) AS col_value,        
    CASE GREATEST(V0,V1,V2,V3,V4,V5,V6...V98,V99)         
        WHEN V0 THEN '0'          
        WHEN V1 THEN '1'          
        WHEN V2 THEN '2'          
        WHEN V3 THEN '3'
        ...     
              WHEN V99 THEN '99'
        END AS col_name 
    FROM dbo.Theta

我希望查询返回的示例：

ArtifactID  GreatestColumnValue  ColumnValueCameFrom
00001            .330                   59
00002            .89                    89
...             
01000            .44                     7

谢谢！

原文

While I have seen several answers to this question all of them tend to rely on on the greatest function which keeps coming back with a

"Msg 195, Level 15, State 10, Line 1
'GREATEST' is not a recognized built-in function name."

I would like to know a way to build a query that for each row returns the artifact id, and the greatest value in the row along with the column it was in. Any suggestions would be welcome. I've looked over quite a few example on this board and others but keep runnign across various errors.

Attempted and Failed Code, shortened for veiwing pleasure:

SELECT artifactID, GREATEST(V0,V1,V2,V3,V4,V5,V6...V98,V99) AS col_value,        
    CASE GREATEST(V0,V1,V2,V3,V4,V5,V6...V98,V99)         
        WHEN V0 THEN '0'          
        WHEN V1 THEN '1'          
        WHEN V2 THEN '2'          
        WHEN V3 THEN '3'
        ...     
              WHEN V99 THEN '99'
        END AS col_name 
    FROM dbo.Theta

Example of what I would like query to return:

ArtifactID  GreatestColumnValue  ColumnValueCameFrom
00001            .330                   59
00002            .89                    89
...             
01000            .44                     7

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

め可乐爱微笑 2024-11-06 08:01:21

SELECT t.artifactID, c.col_value, c.col_name
FROM dbo.Theta t
OUTER APPLY
   (SELECT TOP 1 *
    FROM (
        SELECT '0', V0 UNION ALL
        SELECT '1', V1 UNION ALL
        --...
        SELECT '99', V99) X(col_name,col_value)
    ORDER BY col_value desc) C

SELECT t.artifactID, c.col_value, c.col_name
FROM dbo.Theta t
OUTER APPLY
   (SELECT TOP 1 *
    FROM (
        SELECT '0', V0 UNION ALL
        SELECT '1', V1 UNION ALL
        --...
        SELECT '99', V99) X(col_name,col_value)
    ORDER BY col_value desc) C

回复收藏 0 原文

~没有更多了~