كيفية البحث عن اختلافات الصفوف في SQL Server

To compare rows in a table and find mismatches in SQL Server 2008, you can use the LAG function to compare each row with the previous one. Based on the screenshot you provided, it seems you want to compare the Part and Cost columns to find mismatches. Here’s a query that pulls the last three rows with all columns and adds a column specifying the type of mismatch:

sqlCopy code
WITH LaggedRows AS (
    SELECT
        Part,
        Cost,
        LAG(Part) OVER (ORDER BY ID) AS PrevPart,
        LAG(Cost) OVER (ORDER BY ID) AS PrevCost,
        ID
    FROM
        YourTableName
),
Mismatches AS (
    SELECT
        ID,
        Part,
        Cost,
        CASE
            WHEN Part <> PrevPart THEN 'Part'
            WHEN Cost <> PrevCost THEN 'Cost'
            ELSE NULL
        END AS MismatchType
    FROM
        LaggedRows
    WHERE
        ID > (SELECT MAX(ID) - 3 FROM YourTableName)
)
SELECT
    ID,
    Part,
    Cost,
    MismatchType
FROM
    Mismatches
WHERE
    MismatchType IS NOT NULL;

Replace YourTableName with the actual name of your table. This query uses a common table expression (CTE) to first calculate the previous row’s Part and Cost values for each row. Then, it uses another CTE to identify rows where there are mismatches in the Part or Cost columns compared to the previous row. Finally, it selects the last three rows with mismatches along with the type of mismatch.

Note: This query assumes that the ID column is used to order the rows. Adjust the ORDER BY clause in the LAG function if a different ordering is required.

للمزيد من المعلومات، يمكنك استخدام الاستعلام التالي الذي يقوم بعرض جميع الصفوف مع الصف السابق لها ونوع الاختلاف (إذا كانت هناك اختلافات) بالإضافة إلى الصفوف الثلاثة الأخيرة:

sqlCopy code
WITH LaggedRows AS (
    SELECT
        ID,
        Part,
        Cost,
        LAG(Part) OVER (ORDER BY ID) AS PrevPart,
        LAG(Cost) OVER (ORDER BY ID) AS PrevCost
    FROM
        YourTableName
),
AllRows AS (
    SELECT
        ID,
        Part,
        Cost,
        'Part' AS MismatchType
    FROM
        LaggedRows
    WHERE
        Part <> PrevPart
    UNION ALL
    SELECT
        ID,
        Part,
        Cost,
        'Cost' AS MismatchType
    FROM
        LaggedRows
    WHERE
        Cost <> PrevCost
),
RankedRows AS (
    SELECT
        *,
        ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNum
    FROM
        AllRows
)
SELECT
    ID,
    Part,
    Cost,
    MismatchType
FROM
    RankedRows
WHERE
    RowNum <= 3
ORDER BY
    ID DESC;

هذا الاستعلام يستخدم CTE لحساب الصف السابق لكل صف ومن ثم يستخدم CTE آخر لتحديد الصفوف التي تحتوي على اختلاف في القيمة مقارنة بالصف السابق. ثم يتم استخدام CTE آخر لترتيب الصفوف واختيار الصفوف الثلاثة الأخيرة التي تحتوي على اختلاف.