Блог

Я пытаюсь получить второе значение на основе даты. Предположим, у пользователя есть три записи с датой, и вторая дата также должна быть получена вместе со значением. Итак, мой пример ввода выглядит примерно так:

 UserId  Date                      Amount 
1001    2019-10-10 00:00:00.000   10000
1001    2018-01-01 00:00:00.000   20000
1001    2017-10-02 00:00:00.000   6000
1002    2017-10-10 00:00:00.000   1000
1002    2016-08-02 00:00:00.000   600
1003    2015-06-10 00:00:00.000   200
  

Ожидаемый результат:

 UserId  Date                      Amount 
1001    2018-01-01 00:00:00.000   20000
1002    2016-08-02 00:00:00.000   600
1003    2015-06-10 00:00:00.000   200
  

Я надеюсь, что приведенные выше примеры достаточно информативны, чтобы понять, и попробовал следующие действия, чтобы заставить его работать:

 SELECT DISTINCT m.UserId, m.Amount FROM UserAmount m WHERE m.DatePosted =
(SELECT MAX(k.DatePosted) FROM UserAmount k WHERE 
k.DatePosted < (SELECT MAX(p.DatePosted) FROM UserAmount p));

SELECT DISTINCT m.UserId, m.Amount FROM UserAmount m WHERE m.UserId IN (SELECT q.UserId FROM DetailsUser q) AND m.DatePosted =
(SELECT MAX(k.DatePosted) FROM UserAmount k WHERE k.UserId IN (SELECT r.UserId FROM DetailsUser r) AND 
k.DatePosted < (SELECT MAX(p.DatePosted) FROM UserAmount p WHERE p.UserId IN (SELECT s.UserId FROM DetailsUser s)));
  

К сожалению, я получаю результат для первого идентификатора, скажем, 1001 из таблицы следующим образом:

 UserId Amount
1001   20000
  

Что-нибудь пропущено или сделано неправильно в запросе? Ожидал бы каких-нибудь ценных предложений, чтобы заставить его работать.

Сценарий:

 USE [DbName]
GO
/****** Object:  Table [dbo].[UserAmount]    Script Date: 04/16/2019 23:42:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserAmount](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [nvarchar](20) NULL,
    [DatePosted] [datetime] NULL,
    [Amount] [float] NULL,
 CONSTRAINT [PK_UserAmount] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[UserAmount] ON
INSERT [dbo].[UserAmount] ([Id], [UserId], [DatePosted], [Amount]) VALUES (1, N'1001', CAST(0x0000AAE200000000 AS DateTime), 10000)
INSERT [dbo].[UserAmount] ([Id], [UserId], [DatePosted], [Amount]) VALUES (2, N'1001', CAST(0x0000A85B00000000 AS DateTime), 20000)
INSERT [dbo].[UserAmount] ([Id], [UserId], [DatePosted], [Amount]) VALUES (3, N'1001', CAST(0x0000A80000000000 AS DateTime), 6000)
INSERT [dbo].[UserAmount] ([Id], [UserId], [DatePosted], [Amount]) VALUES (4, N'1002', CAST(0x0000A80800000000 AS DateTime), 1000)
INSERT [dbo].[UserAmount] ([Id], [UserId], [DatePosted], [Amount]) VALUES (5, N'1002', CAST(0x0000A65600000000 AS DateTime), 600)
INSERT [dbo].[UserAmount] ([Id], [UserId], [DatePosted], [Amount]) VALUES (6, N'1003', CAST(0x0000A4B300000000 AS DateTime), 200)
SET IDENTITY_INSERT [dbo].[UserAmount] OFF
/****** Object:  Table [dbo].[DetailsUser]    Script Date: 04/16/2019 23:42:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DetailsUser](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [UserId] [nvarchar](20) NULL,
 CONSTRAINT [PK_DetailsUser] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[DetailsUser] ON
INSERT [dbo].[DetailsUser] ([Id], [UserId]) VALUES (1, N'1001')
INSERT [dbo].[DetailsUser] ([Id], [UserId]) VALUES (2, N'1002')
INSERT [dbo].[DetailsUser] ([Id], [UserId]) VALUES (3, N'1003')
SET IDENTITY_INSERT [dbo].[DetailsUser] OFF
  

N.B: Пример запроса может быть выполнен с использованием любого из языков запросов — MS SQL или Oracle .

Простым способом было бы использовать оконные функции и выбрать вторую запись.

Учитывая вашу вышеуказанную настройку:

 SELECT s1.UserID, s1.Amount, s1.DatePosted
FROM (
    SELECT du.UserID, ua.Amount, ua.DatePosted
        , ROW_NUMBER() OVER ( PARTITION BY ua.UserID ORDER BY ua.DatePosted DESC ) AS rn
        , COUNT(*) OVER ( PARTITION BY ua.UserID) AS theCount
    FROM DetailsUser du
    LEFT OUTER JOIN UserAmount ua ON du.userID = ua.UserID
) s1
WHERE s1.rn = 2 OR s1.theCount <=1
  

https://dbfiddle.uk/?rdbms=sqlserver_2012amp;fiddle=7035366e57188a3508e7348f0fe0ce8b

Это будет работать на SQL Server и Oracle, но, к сожалению, не на MySQL 5.x (поскольку он не вводил оконные функции до 8). У PostgreS уже некоторое время есть оконные функции. Я не уверен, какие другие варианты SQL имеют их, но та же функциональность может быть дублирована в стандартном SQL.

Вы также можете сделать это с помощью apply :

 select du.*
from DetailsUser du outer apply
     (select du2.date
      from DetailsUser du2
      where du2.userid = du.userid
      offset 1 fetch first 1 row only
     )
where du2.date is null or du2.date = du.date;