时间戳

首页 > 解决方案 > 根据更改的列 SQL 过滤结果集

sql - 根据更改的列 SQL 过滤结果集

问题描述

使用以下示例代码，我正在尝试根据标量 @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() 触发器，但在形成语法时遇到了麻烦。

有人可以为我指出如何实现这一目标的正确方向吗？

标签： sqlsql-servertsql

解决方案

您可以使用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;

推荐阅读