SQL 搜索 获取最大值日期的奇怪行为

$sql_a = "SELECT DATE_FORMAT(`timestamp`, '%a %d %M') AS timestamp, `generation` FROM solar_generation WHERE `generation` = (SELECT MAX(`generation`) FROM solar_generation) AND MONTH(timestamp)= 7 AND YEAR(timestamp) = 2023";

Fri 07 July 43.0

SELECT DATE_FORMAT(`timestamp`, '%a %d %M') AS timestamp, `generation` FROM solar_generation WHERE `generation` = (SELECT MAX(`generation`) FROM solar_generation) AND MONTH(timestamp)= 8 AND YEAR(timestamp) = 2023;

MySQL returned an empty result set (i.e. zero rows)

SELECT MAX(`generation`) FROM solar_generation WHERE MONTH(timestamp)= 8 AND YEAR(timestamp) = 2023;

SELECT DATE_FORMAT(`timestamp`, '%a %d %M') AS timestamp,
      `generation` 
FROM solar_generation
WHERE `generation` = (SELECT MAX(`generation`) 
                      FROM solar_generation where MONTH(timestamp)= 8 
                      AND YEAR(timestamp) = 2023
                     );

with max_val_per_month as (
  select `timestamp`,
          `generation` ,
         row_number() over(partition by MONTH(timestamp),YEAR(timestamp) order by generation desc ) max_gen
  from solar_generation
  ) select  DATE_FORMAT(`timestamp`, '%a %d %M') AS timestamp,
            `generation` 
    from max_val_per_month
    where max_gen = 1 
    and  MONTH(timestamp) = 8
    and year(timestamp) = 2023 

Sql相关问答推荐

如何根据SQL中的列条件获取下一个时间戳？

如何在T—SQL中找到值更改之前的日期？

从2个表中查找每条记录的唯一最接近的日期匹配

如何在Snowflake SQL存储过程中传递LIMIT和OFFSET的参数？

在Golang中管理数据库事务的简洁方法

如何使用WSO2将空值传递给我的SQL Server存储过程？

PostgreSQL使用SQL子查询在时间间隔内 Select 数据

从列中提取子字符串的ORDER BY CASE语句

导出部分条形码字符串GS1-128

如何计算给定日期前三个月的值以及月初数据？

如何在多列上编写具有不同条件的查询？

如何创建snowflake表(动态查找数据类型)并从阶段加载(AWS S3)？

如何修复初学者 SQL INNER JOIN 查询错误

MySQL中的递归查询邻接表深度优先？

Oracle PL/SQL长期运行问题

try 将多行折叠为单个结果

获取 SQL Server 中每一行的两个-之间的文本

忽略与给定列匹配的行的 LAG 函数

CURRENT_ROW 窗口框架上的 SQL 滞后

如何找到特定时间间隔内的最大和最小日期？