单表存储多支股票数据 统计个股存在记录的月份数SQL查询问题

问题原因 #

• 语法错误：聚合函数MIN()/MAX()的参数不能直接嵌套独立的SELECT子句，你的写法不符合SQL语法规范
• 逻辑偏差：TIMESTAMPDIFF(MONTH, 最小日期, 最大日期)计算的是两个日期的月份间隔值，不是实际存在行情的月份数量，如果某支股票存在月份断更，计算结果会和实际值不符
• 安全问题：直接把$value['wkn']拼接到SQL语句中，存在SQL注入风险，不符合PDO预处理的使用规范

正确实现方案 #

方案1：统计股票实际存在行情的去重月份数（最符合需求） #

如果要统计的是实际有行情记录的月份总数（断更的月份不算），用以下写法：

单支股票查询SQL

SELECT 
  wkn,
  name,
  COUNT(DISTINCT DATE_FORMAT(`date`, '%Y%m')) as month_count
FROM kurse1 
WHERE wkn = ?
GROUP BY wkn, name

对应正确的PHP预处理代码

$kurse1_monate = $this->_db->prepare("SELECT wkn, name, COUNT(DISTINCT DATE_FORMAT(`date`, '%Y%m')) as month_count FROM kurse1 WHERE wkn = ? GROUP BY wkn, name");
// 绑定参数避免SQL注入
$kurse1_monate->execute([$value['wkn']]);
$this->_kurse1_monate = $kurse1_monate->fetchAll(PDO::FETCH_ASSOC);

查询结果里month_count就是该股票有行情的月份总数，你可以直接拼接成「XXX有X个月的行情记录」的格式输出。

方案2：统计最早行情到最新行情的月份跨度 #

如果你确实需要的是从第一笔行情到最后一笔行情的完整月份跨度（中间断更的月份也算），用以下写法：

SELECT 
  wkn,
  name,
  TIMESTAMPDIFF(MONTH, MIN(`date`), MAX(`date`)) + 1 as month_span
FROM kurse1 
WHERE wkn = ?
GROUP BY wkn, name

注：加1是因为TIMESTAMPDIFF计算的是两个日期的月份差，比如2021-03到2021-05差2个月，实际跨度是3个自然月，需要补1。

优化方案：一次性查询所有股票的月份统计 #

不需要遍历foreach单支查询，直接一条SQL就能拿到所有股票的统计结果，效率更高：

SELECT 
  wkn,
  name,
  COUNT(DISTINCT DATE_FORMAT(`date`, '%Y%m')) as month_count
FROM kurse1 
GROUP BY wkn, name

对应的PHP代码：

$kurse1_monate = $this->_db->query("SELECT wkn, name, COUNT(DISTINCT DATE_FORMAT(`date`, '%Y%m')) as month_count FROM kurse1 GROUP BY wkn, name");
$this->_kurse1_monate = $kurse1_monate->fetchAll(PDO::FETCH_ASSOC);

内容的提问来源于stack exchange，提问作者user16574704