SQL Server 2012：如何基于逗号前首值获取ValueUnit最大值行

Got it, let's tackle this problem head-on. You need to pull the row from your #finaltable where the first value in the comma-separated ValueUnit field is the largest for each partid. Here's a straightforward, reliable way to do it:

Core Approach #

We'll use window functions (available in SQL Server 2012) to rank rows based on the numeric value of the first segment in ValueUnit, then pick the top-ranked row for each partid. The key here is to treat the first segment as a number (not a string) to ensure accurate comparisons.

Full SQL Code (Including Test Data) #

-- Create your temp table and test data
create table #finaltable ( partid int, ValueUnit nvarchar(50) )
insert into #finaltable(partid,ValueUnit) values 
(2532,'1.71, 2.375, 3.135'), 
(2532,'1.89, 2.625, 3.465')

-- Query to get the desired result
SELECT partid, ValueUnit
FROM (
    SELECT 
        partid,
        ValueUnit,
        -- Assign rank: highest first value gets rank 1 for each partid
        ROW_NUMBER() OVER (
            PARTITION BY partid 
            ORDER BY CAST(
                LEFT(ValueUnit, CHARINDEX(',', ValueUnit) - 1)
            AS DECIMAL(10,3)) DESC
        ) AS row_rank
    FROM #finaltable
) ranked_rows
WHERE row_rank = 1

-- Clean up the temp table
DROP TABLE #finaltable

Breakdown of Key Logic #

• CHARINDEX(',', ValueUnit): Finds the position of the first comma in ValueUnit, so we know where to split the string.
• LEFT(ValueUnit, ...): Extracts the substring before the first comma (the value we need to compare).
• CAST(...) AS DECIMAL(10,3): Converts the extracted string to a numeric type—critical because string comparison would fail for values like "1.89" vs "1.71" (string logic would see "1.7" as larger than "1.8" initially).
• ROW_NUMBER() OVER (...): Groups rows by partid, sorts each group by the extracted numeric value in descending order, and assigns a rank. The row with the largest first value gets rank 1.
• WHERE row_rank = 1: Filters to keep only the top-ranked row for each partid.

Expected Result #

For your test data, this query will return exactly what you need:

partid | ValueUnit
-------|-----------------------
2532   | 1.89, 2.625, 3.465

Edge Case Handling (Optional) #

If some rows in ValueUnit don't have a comma (e.g., a single value like "5.2"), adjust the extraction logic to avoid errors:

CAST(
    LEFT(ValueUnit, ISNULL(NULLIF(CHARINDEX(',', ValueUnit), 0) - 1, LEN(ValueUnit)))
AS DECIMAL(10,3))

This checks if there's no comma, then uses the entire ValueUnit string as the numeric value.

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