只保留记录的最新修改日期和时间，不重复，以更新表数据

Question

我有下面的SQL查询，它跟踪商品价格、价格变化的日期和时间，并将值保存在名为dbo.NewPriceLabel的表中。

我试图让它只保留基于修改日期和时间的最新修改，并删除相同主键的旧值。pk是PLU。

这是我目前的查询：

USE [ItemTable]
GO
/****** Object:  Table [dbo].[NewPriceLabel]    Script Date: 05/07/2016 03:46:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[NewPriceLabel](
    [PLU] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Description] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Sizes] [varchar](7) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [OldPrice] [real] NULL,
    [NewPrice] [real] NULL,
    [Datechange] [datetime] NOT NULL CONSTRAINT [NewPriceLabel_Datechange]  DEFAULT (getdate()),
    CONSTRAINT [PK_NewPriceLabel] PRIMARY KEY CLUSTERED (
        [PLU] ASC,
        [Datechange] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = ON) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_PADDING OFF

Answer 1

当插入具有相同PLU的新行时，将删除旧记录的触发器：

CREATE TRIGGER TRG_AFTER_INS_UPD
ON yourTable
AFTER INSERT, UPDATE
AS
    DELETE y
    FROM yourTable y
    INNER JOIN inserted i 
        ON i.PLU = y.PLU AND i.Datechange > y.datechange

Answer 2

由于您的要求是每个PLU只维护一行，我建议您从主键中删除Datechange，并执行一次性删除以删除现有的重复项：

--one-time delete
WITH dups AS (
    SELECT ROW_NUMBER() OVER(PARTITION BY PLU ORDER BY Datechange DESC) AS RowNum 
    )
DELETE FROM dups
WHERE RowNum > 1;
--remove Datechange from primary key
ALTER TABLE dbo.NewPriceLabel
    DROP CONSTRAINT PK_NewPriceLabel;
ALTER TABLE dbo.NewPriceLabel
    ADD CONSTRAINT PK_NewPriceLabel PRIMARY KEY (PLU);

如果传入数据包含Datechange值，并且您希望仅在新的Datechange比当前值新时更新现有行，请考虑MERGE，下面是一个示例。

CREATE PROC dbo.MergeNewPriceLabel
     @PLU varchar(25)
    ,@Description varchar(40)
    ,@Sizes varchar(7) 
    ,@OldPrice real 
    ,@NewPrice real 
    ,@Datechange datetime
AS
SET NOCOUNT ON;
--source PLU with a Datechange older than current one are ignored
MERGE dbo.NewPriceLabel AS target
USING (
    SELECT 
          @PLU AS PLU
        , @Description AS Description
        , @Sizes AS Sizes
        , @OldPrice AS OldPrice
        , @NewPrice AS NewPrice
        , @Datechange AS Datechange
    ) AS source ON
    source.PLU = target.PLU
WHEN NOT MATCHED BY target THEN
    INSERT (
          PLU
        , Description
        , Sizes
        , OldPrice
        , NewPrice
        , Datechange)
    VALUES (
          @PLU
        , @Description
        , @Sizes
        , @OldPrice
        , @NewPrice
        , @Datechange
    )
WHEN MATCHED AND source.Datechange >= target.Datechange THEN
    UPDATE SET
          PLU = source.PLU
        , Description = source.Description
        , Sizes = source.Sizes
        , OldPrice = source.OldPrice
        , NewPrice = source.NewPrice
        , Datechange = source.Datechange;
GO

如果源数据中没有Datechange，并且只想在该行不存在的情况下插入该行，并在存在时更新：

CREATE PROC dbo.MergeNewPriceLabel
     @PLU varchar(25)
    ,@Description varchar(40)
    ,@Sizes varchar(7) 
    ,@OldPrice real 
    ,@NewPrice real 
AS
SET NOCOUNT ON;
--assume source PLU data is newer than current row and update Datechanged with current timestamp
MERGE dbo.NewPriceLabel AS target
USING (
    SELECT 
          @PLU AS PLU
        , @Description AS Description
        , @Sizes AS Sizes
        , @OldPrice AS OldPrice
        , @NewPrice AS NewPrice
    ) AS source ON
    source.PLU = target.PLU
WHEN NOT MATCHED BY target THEN
    INSERT (
          PLU
        , Description
        , Sizes
        , OldPrice
        , NewPrice
        )
    VALUES (
          @PLU
        , @Description
        , @Sizes
        , @OldPrice
        , @NewPrice
    )
WHEN MATCHED THEN
    UPDATE SET
          PLU = source.PLU
        , Description = source.Description
        , Sizes = source.Sizes
        , OldPrice = source.OldPrice
        , NewPrice = source.NewPrice
        , Datechange = SYSDATETIME();

此外，我建议您避免使用real和float类型来存储货币值，因为这些数据类型是近似的，无法存储所有十进制值。请改用decimal、numeric、money或smallmoney。