下面的查询对我来说非常好。虽然日期和值是正确的，但由于顺序不同，输出结果会显示不同的结果。这里的顺序是按日期，然后按姓名。

SELECT z1.name, DATE_FORMAT(c.dt,'%Y-%m-%d'), z1.nc
FROM 

(
SELECT z.name, MAX(z.nc) AS 'mx'
FROM (
SELECT DATE(whn) AS 'dt', name, confirmed - LAG(confirmed,1) OVER(PARTITION BY name ORDER BY DATE(whn) ASC) AS 'nc'
FROM covid ) z
WHERE z.nc >= 1000
GROUP BY z.name
) z1

INNER JOIN

(
SELECT DATE(whn) AS 'dt', name, confirmed - LAG(confirmed,1) OVER(PARTITION BY name ORDER BY DATE(whn) ASC) AS 'nc'
FROM covid
) c

ON c.nc = z1.mx
AND c.name = z1.name

ORDER BY 2 ASC

这个 date 外部查询中的值与where行不对应 MAX(confirmed-lag) 它只是该组中的一个随机日期值。请查看此博客文章中标题为“按问题列出的唯一完整组”的部分：https://www.percona.com/blog/2019/05/13/solve-query-failures-regarding-only_full_group_by-sql-mode/ 更多信息。
我用了 ROW_NUMBER() 函数获取与最大新事例对应的整行。然而，我的最终结果并不是按照答案的顺序排列的，而且也没有关于如何排列的规范，所以我仍然没有得到令人满意的快乐表情。

您需要自联接以获取最大计数发生的日期：

WITH CTE1 as
(SELECT name,DATE_FORMAT(whn, "%Y-%m-%d") as date,
   confirmed - LAG(confirmed, 1) OVER (PARTITION BY name ORDER BY DATE(whn)) as increase
 FROM covid
ORDER BY whn),
CTE2 AS
(SELECT name, MAX(increase) as max_increase
FROM CTE1
WHERE increase >999
GROUP BY name
ORDER BY date)
SELECT c1.name,c1.date,c2.max_increase as peakNewCases
FROM CTE1 as c1
JOIN CTE2 as c2
ON c1.name=c2.name AND c1.increase=c2.max_increase

WITH CTE1 as
(SELECT name, DATE_FORMAT(whn,'%Y-%m-%d') as date_form, confirmed - LAG(confirmed,1) OVER(PARTITION BY name ORDER BY whn) AS newcases
 FROM covid
ORDER BY name,whn) 

SELECT name, date_form, newcases FROM
(
SELECT name, date_form, newcases, ROW_NUMBER() OVER (PARTITION BY name ORDER BY newcases DESC) as rank
FROM CTE1
WHERE newcases > 999
) cte2
WHERE rank =1