如果 SQL Server 中的先前单元格值和当前单元格值相同，则选择为空 [英] Select empty if previous cell value and current cell value is same in SQL Server

问题描述

我需要根据 SQL Server 存储过程中的用户名从表中删除重复值.我的表格如下图所示:

我希望输出如下:

任何帮助将不胜感激.

解决方案

使用

I have a situation to remove the repeating values from table based on the user name in a SQL Server stored procedure. My table look like shown in this screenshot:

I am expecting the output to be like:

Any help will be appreciated.

解决方案

Use LAG Function which support for SQL 2012+ as below, I have use ID as primary key extra in your table

DECLARE @tblTest AS Table
(
    ID INT,
    [Date] DateTime,
    [User] VARCHAR(50),
    SourceIp VARCHAR(50),
    Destination VARCHAR(50),
    Port INT,
    Duration VARCHAR(50)
)

INSERT INTO @tblTest VALUES(1,'12/27/2017 17:22','Jackson','192.168.1.1','192.168.2.1',1500,'0:38:30')
INSERT INTO @tblTest VALUES(2,'12/27/2017 23:19','Jackson','192.168.1.20','192.168.2.1',1500,'0:38:30')
INSERT INTO @tblTest VALUES(3,'12/27/2017 16:39','Manu','192.168.1.10','192.168.2.2',256,'1:00:36')
INSERT INTO @tblTest VALUES(4,'12/27/2017 21:22','Soma','192.168.5.20','192.168.2.3',888,'1:01:22')
INSERT INTO @tblTest VALUES(5,'12/27/2017 21:22','Soma','192.168.5.20','192.168.2.4',4120,'1:01:22')
INSERT INTO @tblTest VALUES(6,'12/27/2017 21:22','Soma','192.168.5.20','192.168.2.5',22,'1:01:22')
INSERT INTO @tblTest VALUES(7,'12/27/2017 8:58','Vick','192.168.1.27','192.168.2.50',22,'0:05:51')
INSERT INTO @tblTest VALUES(8,'12/27/2017 15:48','Vick','192.168.1.27','192.168.2.50',22,'0:05:51')

SELECT 
    CASE WHEN [Date] = lag([Date])  OVER (ORDER BY ID) THEN NULL ELSE [Date] END [Date] ,
    CASE WHEN [User] = lag([User])  OVER (ORDER BY ID) THEN '' ELSE [User] END [User],
    CASE WHEN SourceIp = lag(SourceIp)  OVER (ORDER BY ID) THEN '' ELSE SourceIp END SourceIp,
    CASE WHEN Destination = lag(Destination)  OVER (ORDER BY ID) THEN '' ELSE Destination END Destination,
    CASE WHEN Port = lag(Port)  OVER (ORDER BY ID) THEN NULL ELSE Port END Port,
    CASE WHEN Duration = lag(Duration)  OVER (ORDER BY ID) THEN '' ELSE Duration END Duration
FROM @tblTest       

Output:

这篇关于如果 SQL Server 中的先前单元格值和当前单元格值相同，则选择为空的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！