根据更改的列筛选结果集SQL

Question

使用以下示例代码，我试图删除未根据标量@CompareFields

更改列的行

create table #ResultSet_fields(
claimId int,
adjustmentVersion int,
ServiceDateFrom date,
ServiceDateTo date,
ProcedureCode varchar(10),
PlaceOfService varchar(3)
)
declare @CompareFields varchar(max)
select @CompareFields = 'ProcedureCode,PlaceOfService'
insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1010,1,'5/5/2015','5/5/2015',92213,11
insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1010,2,'5/5/2015','5/5/2015',92213,11
insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1010,3,'5/5/2015','5/5/2015',92214,11
insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1011,1,'5/5/2015','5/5/2015',5555,11
insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1012,1,'5/7/2015','5/7/2015',66666,11
insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1012,2,'5/7/2015','5/7/2015',66666,13
insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1013,1,'5/7/2015','5/7/2015',99999,11
insert into #ResultSet_fields (ClaimId,adjustmentVersion,ServiceDateFrom,ServiceDateTo,ProcedureCode,PlaceOfService) select 1014,1,'5/9/2015','5/9/2015',99999,11

我只想要在调整版本之间有'ProcedureCode'和'PlaceOfService'列变化的行。

交货。由于具有ClaimId '1010'的行有一个'ProcedureCode'更改，并且具有ClaimId '1012'的行有一个'PlaceOfService'更改，我只希望这些行保留在结果集中。

我考虑过使用EXCEPT子句或UPDATE()触发器，但我在形成语法时遇到了麻烦。

谁能告诉我如何完成这件事的正确方向?

Answer 1

您可以使用LAG获取前一行的值进行比较。

SELECT
ClaimId,
adjustmentVersion,
ServiceDateFrom,
ServiceDateTo,
ProcedureCode,
PlaceOfService
FROM (
SELECT *,
ServiceDateFrom_prev = LAG(rs.ServiceDateFrom) OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion),
ServiceDateTo_prev   = LAG(rs.ServiceDateTo  ) OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion),
ProcedureCode_prev   = LAG(rs.ProcedureCode  ) OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion),
PlaceOfService_prev  = LAG(rs.PlaceOfService ) OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion)
FROM #ResultSet_fields rs
) rs
WHERE (
@CompareFields LIKE '%ServiceDateFrom%' AND ServiceDateFrom <> ServiceDateFrom_prev
OR @CompareFields LIKE '%ServiceDateTo%'   AND ServiceDateTo   <> ServiceDateTo_prev
OR @CompareFields LIKE '%ProcedureCode%'   AND ProcedureCode   <> ProcedureCode_prev
OR @CompareFields LIKE '%PlaceOfService%'  AND ProcedureCode   <> PlaceOfService_prev
);

如果你想使用索引，或者你有很多列要比较，你可以使用动态SQL

DECLARE @lagCols nvarchar(max), @whereFilters nvarchar(max);
SELECT
@lagCols = STRING_AGG(CAST(
'    ' + QUOTENAME(c.name + '_chg') + ' = LAG(rs.' + QUOTENAME(c.name) + ') OVER (PARTITION BY rs.ClaimId ORDER BY rs.adjustmentVersion)
'      AS nvarchar(max)), ',')
,@whereFilters = STRING_AGG(CAST(
QUOTENAME(c.name + '_chg') + ' <> ' + QUOTENAME(c.name)
AS nvarchar(max)), ' OR
')
FROM STRING_SPLIT(@CompareFields, ',') s
JOIN tempdb.sys.columns c ON c.name = TRIM(s.value)  -- make sure to get the right database
WHERE c.object_id = OBJECT_ID('tempdb..#ResultSet_fields');
DECLARE @sql nvarchar(max) = '
SELECT
ClaimId,
adjustmentVersion,
ServiceDateFrom,
ServiceDateTo,
ProcedureCode,
PlaceOfService
FROM (
SELECT *,
' + @lagCols + '
FROM #ResultSet_fields rs
) rs
WHERE (
' + @whereFilters + '
);
';
PRINT @sql; -- for testing
EXEC sp_executesql @sql;