合并“待定数据更改"进入视图 [英] Merge "pending data changes" into a view

问题描述

我有一个挂起更改的表格，我正在尝试将更改应用于视图，以便我可以查看最新数据.

I have a table of pending changes, and I am trying to apply the changes to a view so I can see up-to-date data.

首先介绍一些背景知识:我有一个网站，允许对 Project Web Access (PWA) 中的数据进行简单更改.PWA 的一个问题是您无法直接更改其数据库中的数据，因此我使用 SharePoint 的 CSOM 签出、修改、签入、发布和保存对项目和任务的更改.这需要 10-20 秒，这对于网站上的用户体验来说太慢了.

First some background: I have a website that allows simple changes to data in Project Web Access (PWA). An issue with PWA is that you can't change the data directly in its database, so I use SharePoint's CSOM to checkout, modify, check-in, publish and save changes to projects and tasks. This takes 10-20 seconds which is too slow for the user experience on the website.

我通过将修改后的字段写入待处理的更改表来解决这个问题，即行的标识符、更改的字段和新值.我目前在 c# .Net (RBAR) 中合并这些更改以显示网站.

I've worked around this by writing modified fields to a pending changes table, i.e. the identifier for the row, the field changed and the new value. I currently merge these changes in c# .Net (RBAR) for the website to display.

我想在 SQL Server 中合并这些数据，最好不要逐行合并.

I would like to merge this data in SQL server, and preferably not row by row.

我在这里测试

WITH
SourceDataView (id, name, age, joined) AS 
(
    SELECT 1, 'John' , 45, Cast('2018-01-15' as DateTime) UNION 
    SELECT 2, 'Paul', 33, Cast('2018-02-08' as DateTime) UNION 
    SELECT 3, 'George', 39, Cast('2018-03-29' as DateTime)
), 
PendingChanges (id, foreignId, fieldName, fieldValue) AS 
(
    SELECT 1, 2, 'name', 'Peter' UNION 
    SELECT 2, 1, 'age', '34' UNION 
    SELECT 3, 3, 'joined', '2018-02-22' UNION 
    SELECT 4, 1, 'joined', '2018-01-10' UNION 
    SELECT 5, 3, 'joined', '2017-12-30'
)

-- Just show the joined data
SELECT * FROM 
    SourceDataView s
        LEFT JOIN
    PendingChanges p
        on s.id = p.foreignId
ORDER BY p.id;

-- This is what I am expecting
WITH
DesiredResultsView (id, name, age, joined) AS (
    SELECT 1, 'John' , 34, Cast('2018-01-10' as DateTime) UNION 
    SELECT 2, 'Peter', 33, Cast('2018-02-08' as DateTime) UNION 
    SELECT 3, 'George', 39, Cast('2017-12-30' as DateTime)
) SELECT * from DesiredResultsView

有几点需要注意，如果数据更改了两次，则最后一次编辑获胜.另外，数据类型需要正确转换.

A couple of caveats, if the data is changed twice the last edit wins. Also, the data types need to properly converted.

推荐答案

而不是尝试使用 SourceDataView 格式的数据 使用 PendingChanges 格式:

Rather than trying to work with the data in the format of the SourceDataView Work with the format of PendingChanges:

要先对源进行 UNPIVOT，然后才能将更改与简单的 UNION 合并.然后对每一行的更改进行编号，并仅保留每个字段的最新版本.最后将数据转回所需的形状并重新应用原始数据类型.

To do this first UNPIVOT the source, then you will be able to merge the changes with a simple UNION. Then number the changes for each row and only keep the latest version of each field. Finally PIVOT the data back into the required shape and reapply the original data types.

WITH
SourceDataView (id, name, age, joined) AS 
(
    SELECT 1, 'John' , 45, Cast('2018-01-15' as DateTime) UNION 
    SELECT 2, 'Paul', 33, Cast('2018-02-08' as DateTime) UNION 
    SELECT 3, 'George', 39, Cast('2018-03-29' as DateTime)
), 
PendingChanges (id, foreignId, fieldName, fieldValue) AS 
(
    SELECT 1, 2, 'name', 'Peter' UNION 
    SELECT 2, 1, 'age', '34' UNION 
    SELECT 3, 3, 'joined', '2018-02-22' UNION 
    SELECT 4, 1, 'joined', '2018-01-10' UNION 
    SELECT 5, 3, 'joined', '2017-12-30'
),

SourceUnpivot (id, foreignId, fieldName, fieldValue) AS 
(
    SELECT 0 as id, id as foreignId, fieldName, fieldValue
    FROM 
       (SELECT id
            , cast(name as sql_variant) name
            , cast(age as sql_variant) age
            , cast(joined as sql_variant) joined
       FROM SourceDataView) p
    UNPIVOT
       (fieldValue FOR fieldName IN 
          (name, age, joined)
    ) AS _SourceUnpivot
),
MergeChanges as
(
    SELECT * FROM SourceUnpivot
    UNION SELECT * from PendingChanges
),
NumberedChanges as 
(
    SELECT *, 
        ROW_NUMBER() OVER(PARTITION BY foreignID, fieldName ORDER BY id DESC) as ChangeNumber
    from MergeChanges
),
MergePivot as 
(
    SELECT id,
        Cast(name as nvarchar(max)) as name, 
        Cast(age as int) as age, 
        Cast(joined as DateTime) as joined
    FROM (
        SELECT foreignId as id, fieldName, fieldValue
        from NumberedChanges
        where ChangeNumber = 1
    ) as LastChangeOnly
    PIVOT (
        MAX(fieldValue)
        FOR fieldName in (name, age, joined)
    )
    as _MergePivot
)

SELECT * from MergePivot ORDER BY id;

WITH
DesiredResultsView (id, name, age, joined) AS (
    SELECT 1, 'John' , 34, Cast('2018-01-10' as DateTime) UNION 
    SELECT 2, 'Peter', 33, Cast('2018-02-08' as DateTime) UNION 
    SELECT 3, 'George', 39, Cast('2017-12-30' as DateTime)
) SELECT * from DesiredResultsView

这篇关于合并“待定数据更改"进入视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！