如何比较两行以及哪些列和数据已更改 [英] How to compare two rows and which columns and data got changed

问题描述

大家好，



我需要一些帮助，我需要使用我的审核日志来比较哪个列已更改及其旧数据和新数据。所以我应该有Changed_Column，Changed_From，Changed_To

 CREATE TABLE #Sample_Ext_Upl_Hdr_HIST（
 [recid] [varchar]（50）NULL，
 [upld_type] [varchar]（50）NULL，
 [batch_id] [varchar]（50）NULL，
 [season_id] [varchar]（50）NULL，
 [T0] [varchar]（50 ）NULL，
 [BU_Team] [varchar]（50）NULL，
 [Dev_Team] [varchar]（50）NULL，
 [备注] [nvarchar]（200）NULL，
 [created_by] [varchar]（50）NULL，
 [create_dt] [datetime] NULL，
 [updated_by] [varchar]（50）NULL，
 [update_dt] [datetime] NULL，
 [Audit_Type] [varchar]（50）NULL，
 [Audit_Dt] [datetime] NULL 
）
 GO 
 INSERT INTO #Sample_Ext_Upl_Hdr_HIST VALUES（N' 6fd78922-f63e-450e-8b39-7b945915e4a2'，N'Sample Style Header'，N'SMP-81520182'，N'2019-SS'，N'ADI'，N'BAS'，N'TEAM 35'，N'仅测试'，N'chimcham'，CAST（N'2018-08-15T20：38：47.490'AS DateTime），N'chimcham'，CAST（N'2018-08-29T17：31：17.613'AS DateTime）， N UPDATE-OLD'，CAST（N'2018-08-30T01：02：32.203'AS DateTime））
 INSERT INTO #Sample_Ext_Upl_Hdr_HIST VALUES（N'6fd78922-f63e-450e-8b39-7b945915e4a2'，N'Sample Style标题'，N'SMP-81520182'，N'2018-SS'，N'ADI'，N'BAS'，N'TEAM 35'，N'Testing Only'，N'chimcham'，CAST（N'2018- 08-15T20：38：47.490'AS DateTime），N'chimcham'，CAST（N'2018-08-30T01：02：01.980'AS DateTime），N'UPDATE-NEW'，CAST（N'2018-08- 30T01：02：32.203'AS DateTime））

输出应该是：

 upld_type batch_id season_id T0 BU_Team changed_column changed_from changed_to 
样本样式标题SMP-81520182 2018-SS ADI BAS season_id 2019-SS 2018-SS 

请帮助我如何...



我尝试过：



这是我到目前为止所尝试的...（只检查了2列......）但我需要实现列：

 Changed_Column，Changed_From，更改d_To 

  SELECT  
 T1.RECID，
 T1.BATCH_ID，
  CASE  
  WHEN  T1.SEASON_ID = T2.SEASON_ID 
 那么 T1.SEASON_ID 
  ELSE  '  OLD：' + CAST（T1.SEASON_ID  AS   VARCHAR （ 20 ））+ ' ，NEW：' + CAST（T2.SEASON_ID  AS   VARCHAR （ 20 ））
  END   AS  SEASON_ID ，
  CASE  
  WHEN  T1.BU_TEAM = T2.BU_TEAM 
 那么 T1.BU_TEAM 
  ELSE   '  OLD：' + CAST（T1.BU_TEAM  AS   VARCHAR （ 20 ））+ ' ，NEW：' + CAST（T2.BU_TEAM  AS   VARCHAR （ 20 ））
  END   AS  BU_TEAM 
  FROM  #SAMPLE_EXT_UPL_HDR_HIST T1 
  INNER   JOIN  #SAMPLE_EXT_UPL_HDR_HIST T2 
  ON  T1.RECID = T2.RECID 
  AND  T1.BATCH_ID = T2.BATCH_ID 
  AND  T1.AUDIT_TYPE = < span class =code-string>'  UPDATE-OLD' 
  AND  T2.AUDIT_TYPE = '  UPDATE-NEW' 
  WHERE  
（
 T1.SEASON_ID<> T2.SEASON_ID 
  OR  T1.BU_TEAM<> T2.BU_TEAM 
）

解决方案

  SELECT   DISTINCT  
 [upld_type]，
 [batch_id]，
 [T0]，
 [BU_Team ]，
（ SELECT   TOP （ 1 ）[season_id]  FROM  #Sample_Ext_Upl_Hdr_HIST  AS  s1 
  where  s。[create_dt] = s1。[create_dt]  AND  s1.AUDIT_TYPE = '   UPDATE-OLD' 
  ORDER   BY  [update_dt]） AS  changed_from，
（ SELECT   TOP （ 1 ）[season_id]  FROM  #Sample_Ext_Upl_Hdr_HIST  AS  s2 
 其中 s。[create_dt] = s2。[create_dt]  AND  s2.AUDIT_TYPE = '  UPDATE-new' 
  ORDER   BY  [update_dt] ） AS  changed_to 
 来自 #Sample_Ext_Upl_Hdr_HIST  AS  s 

所以我所做的是操纵审核触发器，为我的历史记录创建新表格。如果有更新，则对列进行硬编码。

Hi Guys,

I need some help, I need to compare using my audit log on which column has changed and its old and new data. So I should have Changed_Column, Changed_From, Changed_To

CREATE TABLE #Sample_Ext_Upl_Hdr_HIST (
	[recid] [varchar](50) NULL,
	[upld_type] [varchar](50) NULL,
	[batch_id] [varchar](50) NULL,
	[season_id] [varchar](50) NULL,
	[T0] [varchar](50) NULL,
	[BU_Team] [varchar](50) NULL,
	[Dev_Team] [varchar](50) NULL,
	[remarks] [nvarchar](200) NULL,
	[created_by] [varchar](50) NULL,
	[create_dt] [datetime] NULL,
	[updated_by] [varchar](50) NULL,
	[update_dt] [datetime] NULL,
	[Audit_Type] [varchar](50) NULL,
	[Audit_Dt] [datetime] NULL
)
GO
INSERT INTO #Sample_Ext_Upl_Hdr_HIST  VALUES (N'6fd78922-f63e-450e-8b39-7b945915e4a2', N'Sample Style Header', N'SMP-81520182', N'2019-SS', N'ADI',  N'BAS', N'TEAM 35', N'Testing Only', N'chimcham', CAST(N'2018-08-15T20:38:47.490' AS DateTime), N'chimcham', CAST(N'2018-08-29T17:31:17.613' AS DateTime), N'UPDATE-OLD', CAST(N'2018-08-30T01:02:32.203' AS DateTime))
INSERT INTO #Sample_Ext_Upl_Hdr_HIST  VALUES (N'6fd78922-f63e-450e-8b39-7b945915e4a2', N'Sample Style Header', N'SMP-81520182', N'2018-SS', N'ADI',  N'BAS', N'TEAM 35', N'Testing Only', N'chimcham', CAST(N'2018-08-15T20:38:47.490' AS DateTime), N'chimcham', CAST(N'2018-08-30T01:02:01.980' AS DateTime), N'UPDATE-NEW', CAST(N'2018-08-30T01:02:32.203' AS DateTime))

Output Should be:

upld_type batch_id	season_id  T0	BU_Team	changed_column	changed_from	changed_to
Sample Style Header	SMP-81520182	2018-SS	ADI	BAS	season_id	2019-SS	2018-SS

Please help me how to..

What I have tried:

This is what I have tried so far.. (only 2 columns checking yet..) But I need to achieve the columns :

Changed_Column, Changed_From, Changed_To

SELECT 
   T1.RECID,
   T1.BATCH_ID,
   CASE 
     WHEN T1.SEASON_ID = T2.SEASON_ID 
     THEN T1.SEASON_ID 
     ELSE 'OLD:' + CAST(T1.SEASON_ID AS VARCHAR(20)) + ', NEW:' + CAST(T2.SEASON_ID AS VARCHAR(20)) 
     END AS SEASON_ID,
   CASE 
     WHEN T1.BU_TEAM = T2.BU_TEAM 
     THEN T1.BU_TEAM
     ELSE 'OLD:' + CAST(T1.BU_TEAM AS VARCHAR(20)) + ', NEW:' + CAST(T2.BU_TEAM AS VARCHAR(20)) 
     END AS BU_TEAM
FROM #SAMPLE_EXT_UPL_HDR_HIST T1
INNER JOIN #SAMPLE_EXT_UPL_HDR_HIST T2
   ON T1.RECID=T2.RECID 
   AND T1.BATCH_ID=T2.BATCH_ID 
   AND T1.AUDIT_TYPE='UPDATE-OLD' 
   AND T2.AUDIT_TYPE='UPDATE-NEW'
WHERE   
(
   T1.SEASON_ID <> T2.SEASON_ID 
   OR T1.BU_TEAM <> T2.BU_TEAM 
)

解决方案

SELECT DISTINCT 
  [upld_type],
  [batch_id],
  [T0],
  [BU_Team],
  (SELECT TOP(1) [season_id] FROM #Sample_Ext_Upl_Hdr_HIST AS s1 
      where s.[create_dt]=s1.[create_dt] AND s1.AUDIT_TYPE='UPDATE-OLD'
             ORDER BY [update_dt]) AS changed_from,
  (SELECT TOP(1) [season_id] FROM #Sample_Ext_Upl_Hdr_HIST AS s2
      where s.[create_dt]=s2.[create_dt] AND s2.AUDIT_TYPE='UPDATE-new'
             ORDER BY [update_dt]) AS changed_to	 
 from #Sample_Ext_Upl_Hdr_HIST  AS s

So what I did was manipulate the Audit Trigger, created new table for my history.. and hard coded the columns if there's an update.

这篇关于如何比较两行以及哪些列和数据已更改的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！