获取匹配条件的下一行数据及收益校验存储过程需求咨询

1. Fetching the Next Row Matching Specified Criteria #

The approach here depends a bit on your database system, but window functions are the cleanest, most efficient option for modern databases. Let’s break down examples for common systems:

For SQL Server, PostgreSQL, or MySQL 8+ (using LEAD() window function) #

If you need to pull the next row relative to a current record (sorted by a column like created_date or id), the LEAD() function lets you access data from the subsequent row directly in your result set:

SELECT
    current_row.id,
    current_row.record_value,
    -- Get the value from the next row in the sorted set
    LEAD(current_row.record_value) OVER (ORDER BY current_row.created_date) AS next_row_value
FROM
    your_table current_row
WHERE
    current_row.your_filter_criteria = 'target_value';

If you only want the next row for a specific existing record, add a nested filter:

SELECT *
FROM (
    SELECT
        *,
        LEAD(id) OVER (ORDER BY created_date) AS next_row_id
    FROM your_table
    WHERE your_filter_criteria = 'target_value'
) AS subquery
WHERE id = @your_current_record_id;

For Older MySQL Versions (pre-8.0) #

Use LIMIT with a date/ID condition to fetch the next row after your target record:

SELECT *
FROM your_table
WHERE created_date > (SELECT created_date FROM your_table WHERE id = @your_current_record_id)
  AND your_filter_criteria = 'target_value'
ORDER BY created_date ASC
LIMIT 1;

2. Optimizing the Total Income Validation Stored Procedure #

Let’s fix those manual entry errors (extra zeros, missing digits, negative amounts) and strengthen the deviation checks. Below is a SQL Server example—adjust syntax for your database as needed:

Key Improvements & Full Implementation #

We’ll add input validation, accurate period income calculation, configurable deviation checks, error logging, and robust error handling:

CREATE OR ALTER PROCEDURE dbo.ValidatePersonTotalIncome
    @PersonID INT,
    @ReportedTotalIncome DECIMAL(18,2),
    @ValidationResult VARCHAR(150) OUTPUT,
    @PeriodIncome DECIMAL(18,2) OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    -- Initialize output variables
    SET @ValidationResult = 'Validation Passed';
    SET @PeriodIncome = 0;

    BEGIN TRY
        -- 1. Basic input validation
        IF @ReportedTotalIncome < 0
        BEGIN
            SET @ValidationResult = 'Error: Total income cannot be negative';
            INSERT INTO dbo.IncomeValidationLogs (PersonID, ReportedAmount, Message, LogDate)
            VALUES (@PersonID, @ReportedTotalIncome, @ValidationResult, GETDATE());
            RETURN;
        END

        -- Check for suspicious round numbers (common extra-zero mistake)
        IF @ReportedTotalIncome % 100 = 0 AND @ReportedTotalIncome > 1000
        BEGIN
            SET @ValidationResult = 'Warning: Unusually large round number - verify no extra zeros were added';
        END

        -- 2. Fetch the latest prior income entry for the person
        DECLARE @LastTotalIncome DECIMAL(18,2);
        SELECT TOP 1 @LastTotalIncome = TotalIncome
        FROM dbo.PersonIncomeRecords
        WHERE PersonID = @PersonID
        ORDER BY EntryDate DESC;

        -- Calculate income since last entry
        IF @LastTotalIncome IS NOT NULL
        BEGIN
            SET @PeriodIncome = @ReportedTotalIncome - @LastTotalIncome;

            -- 3. Check for unexpected deviations
            IF @PeriodIncome < 0
            BEGIN
                SET @ValidationResult = 'Error: Period income is negative - total income cannot be less than previous entry';
            END
            -- Optional: Add percentage deviation check (adjust threshold to match your rules)
            ELSE
            BEGIN
                DECLARE @AvgPriorPeriodIncome DECIMAL(18,2);
                SELECT @AvgPriorPeriodIncome = AVG(TotalIncome - LAG(TotalIncome) OVER (ORDER BY EntryDate))
                FROM dbo.PersonIncomeRecords
                WHERE PersonID = @PersonID
                AND LAG(TotalIncome) OVER (ORDER BY EntryDate) IS NOT NULL;

                IF @AvgPriorPeriodIncome > 0 AND ABS(@PeriodIncome - @AvgPriorPeriodIncome) / @AvgPriorPeriodIncome > 0.20
                BEGIN
                    SET @ValidationResult = 'Warning: Period income deviates by more than 20% from average prior income';
                END
            END
        END
        ELSE
        BEGIN
            -- First entry for the person - no prior data to compare
            SET @ValidationResult = 'Info: First income entry for this person - no period income calculation';
        END

        -- Log all validation attempts for auditing
        INSERT INTO dbo.IncomeValidationLogs (PersonID, ReportedAmount, PeriodIncome, Message, LogDate)
        VALUES (@PersonID, @ReportedTotalIncome, @PeriodIncome, @ValidationResult, GETDATE());

    END TRY
    BEGIN CATCH
        SET @ValidationResult = 'Error: ' + ERROR_MESSAGE();
        INSERT INTO dbo.IncomeValidationLogs (PersonID, ReportedAmount, Message, LogDate)
        VALUES (@PersonID, @ReportedTotalIncome, @ValidationResult, GETDATE());
    END CATCH
END

Quick Adaptation Tips #

• Tweak the deviation threshold (20% in the example) to fit your business rules
• Add more pattern checks (e.g., enforce valid decimal places for your currency)
• For MySQL, replace TRY/CATCH with DECLARE EXIT HANDLER and adjust window function syntax slightly

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