构建多列数值与特殊值求和SQL查询的技术求助

Got it, let's work through this problem together. First, let's lock down the core rules we can derive from your examples:

• If every column in a row is a special value (like AB, CD, etc.), return that special value (we'll assume all special values in a single row are identical, as your example shows all ABs output AB)
• If there's at least one numeric value in the row, ignore all special values and sum up only the numeric ones

Solution for 3 Columns #

Let's start with the 3-column case since that's what your example uses. The approach uses a CASE statement to handle both scenarios, plus functions to safely convert values to numbers (and ignore non-numeric ones).

Example for SQL Server:

SELECT 
    SLNO,
    C1,
    C2,
    C3,
    CASE
        -- Check if all columns are non-numeric special values
        WHEN TRY_CAST(C1 AS INT) IS NULL 
             AND TRY_CAST(C2 AS INT) IS NULL 
             AND TRY_CAST(C3 AS INT) IS NULL THEN C1
        -- Sum numeric values, replace non-numeric with 0
        ELSE CAST(
            ISNULL(TRY_CAST(C1 AS INT), 0) + 
            ISNULL(TRY_CAST(C2 AS INT), 0) + 
            ISNULL(TRY_CAST(C3 AS INT), 0) AS VARCHAR(50)
        )
    END AS Output
FROM YourTableName;

Example for MySQL:

MySQL doesn't have TRY_CAST, so we'll use regex to check for numeric values instead:

SELECT 
    SLNO,
    C1,
    C2,
    C3,
    CASE
        WHEN C1 NOT REGEXP '^[0-9]+$' 
             AND C2 NOT REGEXP '^[0-9]+$' 
             AND C3 NOT REGEXP '^[0-9]+$' THEN C1
        ELSE CONCAT(
            IF(C1 REGEXP '^[0-9]+$', CAST(C1 AS UNSIGNED), 0) +
            IF(C2 REGEXP '^[0-9]+$', CAST(C2 AS UNSIGNED), 0) +
            IF(C3 REGEXP '^[0-9]+$', CAST(C3 AS UNSIGNED), 0)
        )
    END AS Output
FROM YourTableName;

General Solution for N Columns #

If you have more than 3 columns, writing out each column manually gets tedious. Here's a scalable approach using unpivoting (works in SQL Server; adjust for other dialects):

WITH UnpivotedData AS (
    SELECT 
        SLNO,
        ColumnValue,
        -- Flag if value is numeric
        CASE WHEN TRY_CAST(ColumnValue AS INT) IS NOT NULL THEN 1 ELSE 0 END AS IsNumeric
    FROM YourTableName
    -- List all your columns here (C1, C2, C3, ..., Cn)
    UNPIVOT (ColumnValue FOR ColumnName IN (C1, C2, C3)) AS Up
),
AggregatedData AS (
    SELECT 
        SLNO,
        SUM(CASE WHEN IsNumeric = 1 THEN TRY_CAST(ColumnValue AS INT) ELSE 0 END) AS NumericTotal,
        COUNT(CASE WHEN IsNumeric = 0 THEN 1 END) AS NonNumericCount,
        MAX(CASE WHEN IsNumeric = 0 THEN ColumnValue END) AS SpecialValue,
        COUNT(*) AS TotalColumns
    FROM UnpivotedData
    GROUP BY SLNO
)
SELECT 
    yt.SLNO,
    yt.C1,
    yt.C2,
    yt.C3, -- Add all original columns you want to display
    CASE
        WHEN NonNumericCount = TotalColumns THEN SpecialValue
        ELSE CAST(NumericTotal AS VARCHAR(50))
    END AS Output
FROM AggregatedData ag
JOIN YourTableName yt ON ag.SLNO = yt.SLNO;

Key Notes #

• Strict Numeric Checks: If your special values might include partial numbers (like '12AB'), use stricter validation. For SQL Server, TRY_CAST(ColumnValue AS INT) IS NOT NULL is more reliable than ISNUMERIC (which can return true for values like '+12' or '12.0').
• Mixed Special Values: Your example doesn't cover rows with different special values (e.g., AB + CD). If this can happen, you'll need to define what the output should be (e.g., return 'MIXED' or pick one) and adjust the CASE logic accordingly.
• Output Type: We cast the sum to VARCHAR so the same column can hold both numeric totals and special string values.

内容的提问来源于stack exchange，提问作者Raghuveer Devraj Shetty