sql(ms access)：对于给定的记录，如何从给定的字段集中查询前5个值？

q9rjltbz 于 2021-08-09 发布在 Java

关注(0)|答案(1)|浏览(408)

我有一个包含几十个字段的表，其中18个是具有不同整数值的字段。我想为给定记录的五个具有最大值的字段提取字段名和相应的值。我将使用下表作为示例，其中我只想查询给定记录的两个最大值。

Name, FigureA, FigureB, FigureC
John, 40,      73,      81
Luke, 35,      21,      65

我想为约翰归还以下物品：

FigureB, 73
FigureC, 81

我已经走了这么远：

sSQL = "Select t.* " & _
            "From (Select 'A' as [FigureA], FigureA " & _
                    "From Table) as t " & _
            "Union All " & _
           "Select t.* " & _
            "From (Select 'B' as [FigureB], FigureB " & _
                    "From Table) as t " & _
            "Union All " & _
           "Select t.* " & _
            "From (Select 'C' as [FigureC], FigureC " & _
                    "From Table) as t"

sql excel ms-access vba

来源：https://stackoverflow.com/questions/61892729/sql-ms-access-for-a-given-record-how-to-query-top-5-values-from-a-given-set

1条答案

按热度按时间

bakd9h0s1#

在ms-access中，这可能是最简单的 union all :

select top (5) *
from (select t.*
      from (select top (1) "field1" as colname, max(field1) as max_val
            from t
           ) as t
      union all
      select t.*
      from (select top (1) "field2" as colname, max(field2)
            from t
           ) as t
      union all
      . . . 
     ) as t
order by max_val desc;

某些版本的ms access不支持 union all 在 from 条款。该组件可能需要一个视图。

赞(0）回复(0）举报 2021-08-09

我来回答

sql(ms access)：对于给定的记录，如何从给定的字段集中查询前5个值？

1条答案

相关问题

热门标签

最新问答