MySQL如何筛选最小与最大日期之间的非极值记录

你的查询之所以返回所有行，是因为BETWEEN操作符的特性：它会包含指定范围的起始值和结束值。你的需求是只保留最小日期（1/1/2018）和最大日期（9/9/2018）之间的记录（不包含这两个端点），所以BETWEEN显然不符合预期。

方案1：用>和<替代BETWEEN #

直接把范围条件改成大于最小日期、小于最大日期，精准排除两端的日期：

SELECT * 
FROM RUBERIC R 
WHERE R.SCHOOLID = 75 
  AND R.TEACHERID = 610 
  AND R.OBSERVED = 'Observed Classroom' 
  AND R.DATE > (SELECT MIN(DATE) 
                FROM RUBERIC 
                WHERE SCHOOLID = 75 
                  AND TEACHERID = 610 
                  AND OBSERVED = 'Observed Classroom')
  AND R.DATE < (SELECT MAX(DATE) 
                FROM RUBERIC 
                WHERE SCHOOLID = 75 
                  AND TEACHERID = 610 
                  AND OBSERVED = 'Observed Classroom');

方案2：先计算日期范围再排除端点 #

用CTE（公共表表达式）先一次性算出目标数据集的最小和最大日期，再在主查询里排除这两个值。这种方式在数据量较大时效率更高，因为只计算一次日期范围：

WITH date_bounds AS (
  SELECT 
    MIN(DATE) AS earliest_date, 
    MAX(DATE) AS latest_date
  FROM RUBERIC 
  WHERE SCHOOLID = 75 
    AND TEACHERID = 610 
    AND OBSERVED = 'Observed Classroom'
)
SELECT R.* 
FROM RUBERIC R
CROSS JOIN date_bounds db
WHERE R.SCHOOLID = 75 
  AND R.TEACHERID = 610 
  AND R.OBSERVED = 'Observed Classroom' 
  AND R.DATE NOT IN (db.earliest_date, db.latest_date);

如果你的DATE字段包含时间部分（比如2018-01-01 09:30:00），记得在计算最小/最大日期时忽略时间，避免出现意外结果。比如在MySQL里可以用DATE(R.DATE)，SQL Server里用CAST(R.DATE AS DATE)，把字段转换成纯日期格式后再计算。

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