MySQL：如何计算指定列值在同ID多列中的排名

Problem Description #

I've searched extensively on the platform for similar questions but couldn't find any—my apologies if an answer already exists. This is a basic but tricky problem for me. I have the following table:

ID	Mike	Carl	Steve	Josh
1	2$	3$	1$	5$
2	4$	5$	1$	2$

My requirement is: For each ID, calculate the rank of Mike's value (from lowest to highest) among the four values in that ID's row. For example, Mike ranks 2nd in ID 1, and 3rd in ID 2.

Solution 1: Excel/Google Sheets #

Since your data has dollar signs ($) attached to the numbers, we first need to convert those text values to actual numbers, then calculate the rank.

Formula (Excel 365/Google Sheets):

In a new column (say, F2), enter this formula and drag it down:

=RANK.EQ(--SUBSTITUTE(B2,"$",""), --SUBSTITUTE(B2:E2,"$",""), 1)

• SUBSTITUTE(B2,"$","") removes the dollar sign from Mike's value.
• The double hyphen (--) converts the resulting text to a numeric value.
• SUBSTITUTE(B2:E2,"$","") does the same for all values in the row.
• The third argument 1 in RANK.EQ sets the ranking to ascending order (lowest to highest).

For older Excel versions that don't support dynamic arrays, enter the formula as an array formula by pressing Ctrl+Shift+Enter instead of just Enter.

Solution 2: Python with Pandas #

If you're working with larger datasets or prefer code-based solutions, Pandas makes this straightforward:

import pandas as pd

# Create the DataFrame from your sample data
df = pd.DataFrame({
    'ID': [1, 2],
    'Mike': ['2$', '4$'],
    'Carl': ['3$', '5$'],
    'Steve': ['1$', '1$'],
    'Josh': ['5$', '2$']
})

# Clean the dollar signs and convert to numeric values
numeric_values = df.iloc[:, 1:].replace('\$', '', regex=True).astype(float)

# Calculate row-wise rank (ascending, same values get the same minimum rank)
df['Mike_Rank'] = numeric_values.rank(axis=1, method='min', ascending=True)['Mike']

# Print the result
print(df)

Output:

ID Mike Carl Steve Josh  Mike_Rank
0   1   2$   3$    1$   5$        2.0
1   2   4$   5$    1$   2$        3.0

• axis=1 tells Pandas to calculate ranks across each row instead of columns.
• method='min' ensures that duplicate values get the same lowest rank (matches your sample behavior).
• ascending=True sets the ranking from lowest to highest.

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