从行中获取最大值，并在一个sql查询中插入

Question

我有以下列在我的表feedTable

FirstName
LastName
FeedDate

我有以下行在我的表

XYZ, XYZ, '2021-10-15'
ABC, ABC, '2021-10-16'
ANA, ANA, '2021-10-15'
ALO, ALO, '2021-10-16'
PAIO, PAIO, '2021-10-16'

现在我想插入一个记录的名字，姓氏和最大日期从现有的记录。应该在同一个查询中。我不想使用两个操作，我需要获得最大日期，然后插入。执行此操作的最佳方法是什么?

Insert into feedtable (FirstName, LastName, FeedDate) values ('Rahul','B',?)

Here代替'?'我想放置feeddate的最大日期。这怎么可能?

请帮

Answer 1

请在输入姓名和日期为现有表记录的最大日期的情况下尝试此操作。

INSERT INTO feedTable (FirstName, LastName, FeedDate)
SELECT 'Rahul' FirstName
, 'B' LastName
, MAX(FeedDate) FeedDate
FROM feedTable

请从url https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=1a3ffbc0c701843f7eabf23e40c1d49c查看

Answer 2

解决方案#1(适用于SQL Server)

CREATE TABLE feedTable (
FirstName varchar(50),
LastName varchar(50),
FeedDate  date);
--Date format MM/dd/yyyy
INSERT INTO feedTable VALUES( 'Shohag', 'Mia', '3/31/2021')
INSERT INTO feedTable VALUES( 'Raju', 'Ahmed', '4/6/2021')
INSERT INTO feedTable VALUES( 'Robin', 'Mia', '4/10/2021')
INSERT INTO feedTable VALUES( 'Emon', 'Paki', '12/8/2021')
INSERT INTO feedTable VALUES( 'Saiful', 'Haque', '3/1/2021')
--for checking current max value
SELECT * FROM feedTable ORDER BY FeedDate
--Inserting into main table after selecting max data
INSERT INTO  feedTable 
SELECT FirstName, LastName,FeedDate
FROM feedTable
Where FeedDate =  (SELECT MAX(FeedDate) FROM feedTable)
--for checking value after inserting
SELECT * FROM feedTable ORDER BY FeedDate

或者可以根据您的要求选择第二个选项:

Insert into feedtable (FirstName, LastName, FeedDate) values ('Rahul','B', (select MAX(FeedDate) from feedTable) )

SQL Fiddle Link

解决方案# 2 (MySql)

insert into feedTable select 'Pradeep', '2', MAX(FeedDate) from feedTable

MySql Fiddle Link