如何在MS Access中查询单条记录指定字段的Top N值及对应字段名

Hey there! Let's work through this problem to get you the top 5 highest values (and their field names) from those 18 integer fields for a single record. You already started down the right path with UNION ALL—let's refine that approach and also look at a cleaner alternative if your database supports it.

Option 1: Refine Your UNION ALL Approach #

Your current code is unpivoting columns into rows, which is exactly what we need. To target a single record and get the top 5, we just need to add a filter for the specific record, sort the results, and pick the top entries.

For your sample table, here's how to get John's top 2 values:

SELECT TOP 2 FieldName, FieldValue
FROM (
    -- Repeat this block for each of your 18 fields
    SELECT 'FigureA' AS FieldName, FigureA AS FieldValue FROM YourTable WHERE Name = 'John'
    UNION ALL
    SELECT 'FigureB' AS FieldName, FigureB AS FieldValue FROM YourTable WHERE Name = 'John'
    UNION ALL
    SELECT 'FigureC' AS FieldName, FigureC AS FieldValue FROM YourTable WHERE Name = 'John'
) AS UnpivotedData
ORDER BY FieldValue DESC;

For your 18 fields, just copy the SELECT ... UNION ALL line 18 times, swapping out the field names each time. This works across almost all databases (SQL Server, MySQL, Access, etc.) since UNION ALL is widely supported.

Option 2: Use UNPIVOT (For Databases That Support It) #

If you're using SQL Server, Oracle, or another database with the UNPIVOT operator, this will make your code much cleaner and less repetitive. It does the same column-to-row conversion in one step:

SELECT TOP 5 FieldName, FieldValue
FROM YourTable
UNPIVOT (
    -- Map the value column
    FieldValue FOR FieldName IN (
        FigureA, FigureB, FigureC, 
        -- List all 18 of your integer fields here
        FigureD, FigureE, ...
    )
) AS UnpivotedData
-- Filter to your specific record (use a primary key instead of Name if possible!)
WHERE Name = 'John'
ORDER BY FieldValue DESC;

Key Notes to Keep in Mind #

• Handling Ties: If multiple fields have the same value (e.g., two fields tied for 5th place), use TOP 5 WITH TIES (SQL Server) or your database's equivalent to include all tied entries instead of cutting them off.
• Targeting Records: Using a unique identifier (like a primary key column) instead of Name is better to avoid accidentally pulling data from multiple records with the same name.
• Database Compatibility: If you're using MySQL or Access, UNPIVOT isn't available—stick with the UNION ALL approach instead.

That should get you exactly the top 5 field names and values you need for your single record!

内容的提问来源于stack exchange，提问作者Michael Smith