I have table like this:
CREATE TABLE [dbo].[TestToDelete](
[id] [int] NULL,
[Email] [nvarchar](50) NULL,
[RawEmail] [nvarchar](50) NULL,
[Status] [tinyint] NULL,
[ValidationDate] [datetime] NULL
) ON [PRIMARY]
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (1, N'a@a.ru', N'aaa@a.ru', 11, CAST(N'2017-02-07 14:00:30.300' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (2, N'a@a.ru', N'aaa@a.ru', 11, CAST(N'2017-02-07 14:00:52.347' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (3, N'a@a.ru', N'aaa@a.ru', 11, CAST(N'2017-02-07 14:00:58.117' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (4, N'a@a.ru', N'aaa@a.ru', 22, CAST(N'2017-02-07 14:01:08.360' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (5, N'b@b.ru', N'bbb@b.ru', 11, CAST(N'2017-02-07 14:01:21.783' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (6, N'b@b.ru', N'bbb@b.ru', 11, CAST(N'2017-02-07 14:01:29.310' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (7, N'b@b.ru', N'bbb@b.ru', 22, CAST(N'2017-02-07 14:01:37.050' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (8, NULL, N'bbb@b.ru', 0, CAST(N'2017-02-07 14:02:10.643' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (9, NULL, N'aaa@a.ru', 0, CAST(N'2017-02-07 14:02:22.160' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (10, N'anew@a.ru', N'aaa@a.ru', 11, CAST(N'2017-02-07 15:30:01.637' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (11, N'anew@a.ru', N'aaa@a.ru', 11, CAST(N'2017-02-07 15:30:06.657' AS DateTime))
INSERT [dbo].[TestToDelete] ([id], [Email], [RawEmail], [Status], [ValidationDate]) VALUES (12, N'anew@a.ru', N'aaa@a.ru', 11, CAST(N'2017-02-07 15:30:12.160' AS DateTime))
我需要选择出现3次或更多且日期最近的记录(Email
、RawEmail
和Status
字段)。在这个表格中,它是:
'anew@a.ru | aaa@a.ru | 11'
而不是
'a@a.ru | aaa@a.ru | 11'
因为anew@a.ru
的日期更近。
执行此选择的查询:
select * from
(
select email, rawEmail, Status,
ROW_NUMBER() OVER(PARTITION BY rawEmail ORDER BY vdate DESC) num
from
(select max([ValidationDate]) vdate, email, rawEmail, Status
from TestToDelete where status in (11, 22)
group by rawEmail, email, status
having count(*) > 2
) tmp
)final where num = 1
有没有可能使用更少的子查询完成这个任务(而不是像现在这样使用3个)?
更新: 当出现3个或更多时,期望输出:
anew@a.ru | aaa@a.ru | 11
当有2个或更多出现时,期望的输出为:
anew@a.ru | aaa@a.ru | 11
b@b.ru | bbb@b.ru | 11