gpt4 book ai didi

sql - 检索第二个日期值

转载 作者:行者123 更新时间:2023-12-05 00:01:26 25 4
gpt4 key购买 nike

我正在尝试根据日期获取第二个值。假设,一个用户有三个带日期的条目,第二个日期也应该用该值检索。所以我的样本输入是这样的:

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)));

不幸的是,我从表中得到第一个 ID 为 1001 的结果如下:

UserId Amount
1001 20000

查询中有没有跳过或做错了什么?期待一些有值(value)的建议,以使其发挥作用。

脚本:

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 SQLOracle 完成。

最佳答案

一个简单的方法是使用窗口函数并选择第二条记录。

鉴于您的上述设置:

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_2012&fiddle=7035366e57188a3508e7348f0fe0ce8b

这适用于 SQL Server 和 Oracle,但不幸的是不适用于 MySQL 5.x(因为它直到 8 才引入窗口函数)。 PostgreS 拥有窗口函数已有一段时间了。我不确定其他哪些类型的 SQL 具有它们,但相同的功能可以在标准 SQL 中重复。

关于sql - 检索第二个日期值,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/55713858/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com