需要有关Sql查询的帮助 - 如何获取最近删除的记录

Please help me I am new to SQL

请帮帮我,我是SQL新手

Could you please let me know how to get the recently deleted records from the below Table. Here I need to make a query only on rows which has more than 1 rows for the same Number column

您能否告诉我如何从下表中获取最近删除的记录。在这里,我只需要对同一个Number列有超过1行的行进行查询

I need Result like this :-

我需要这样的结果: -

I need Result like this :-

我需要这样的结果: -

ID     FName      LName        Number            CreateDate
2      BBBBB       B            111111       06-26-2016 01:18:000
3      CCCCC       C            333333       06-25-2016 06:10:000
4      DDDDD       D            444444       06-25-2016 06:10:000
5      EEEEE       E            555555       06-25-2016 23:10:000
7      FFFFF       F            777777       06-26-2016 00:01:000
8      GGGGG       G            888888       06-26-2016 16:01:000
9      HHHHH       H            999999       06-26-2016 23:01:000


Create Table Users1
(
ID int,
FName varchar (50),
LName Varchar (50),
Number varchar(10),
CreateDate Datetime
)    

INSERT INTO Users1 Values (1,'AAAA','A','11111','06-25-2016 00:10:765')
INSERT INTO Users1 Values (2,'AAAA','A','11111','06-26-2016 01:18:000')
INSERT INTO Users1 Values (3,'CCCC','C','33333','06-25-2016 06:10:000')
INSERT INTO Users1 Values (4,'DDDD','D','44444','06-25-2016 06:10:000')
INSERT INTO Users1 Values (5,'EEEE','E','55555','06-25-2016 23:10:000')
INSERT INTO Users1 Values (6,'CCCC','C','33333','06-25-2016 00:01:000')
INSERT INTO Users1 Values (7,'FFFF','F','77777','06-26-2016 00:01:000')
INSERT INTO Users1 Values (8,'GGGG','G','88888','06-26-2016 16:01:000')
INSERT INTO Users1 Values (9,'HHHH','H','99999','06-26-2016 23:01:000')

2 个解决方案

#1

With Users1 As (
SELECT * FROM (
    VALUES
        (1, 'AAAA', 'A', '11111', '06-25-2016 00:10:765'),
        (2, 'AAAA', 'A', '11111', '06-26-2016 01:18:000'),
        (3, 'CCCC', 'C', '33333', '06-25-2016 06:10:000'),
        (4, 'DDDD', 'D', '44444', '06-25-2016 06:10:000'),
        (5, 'EEEE', 'E', '55555', '06-25-2016 23:10:000'),
        (6, 'CCCC', 'C', '33333', '06-25-2016 00:01:000'),
        (7, 'FFFF', 'F', '77777', '06-26-2016 00:01:000'),
        (8, 'GGGG', 'G', '88888', '06-26-2016 16:01:000'),
        (9, 'HHHH', 'H', '99999', '06-26-2016 23:01:000')
    ) V (ID, FName, LName, Number, CreateDate)
), Users1WithVersionNumber As (
    Select *
      , row_number() Over (Partition By Number Order By CreateDate DESC) As VersionNumber
    From Users1
)
Select FName, LName, Number, CreateDate
From Users1WithVersionNumber
Where VersionNumber = 1               --< Take the latest version only
  AND Number In (
    Select Number
    From Users1WithVersionNumber
    Where VersionNumber = 2           --< There is at least one other version
  )

#2

    SELECT CreateDate,FName,LName,Number FROM Users1 

WHERE CreateDate in ( SELECT MAX(CreateDate) from Users1 group by Number)

在哪里创建CreateDate(从Users1组中选择MAX MAX(Cre​​ateDate)按编号)