请求编写Microsoft SQL Server特定时段每日各分类采购数量统计SQL语句

以下是满足需求的SQL查询语句，针对Microsoft SQL Server环境优化：

SELECT
    CAST(PurchaseDate AS DATE) AS PurchaseDay,
    Classification,
    SUM(Quantity) AS TotalQuantity
FROM
    YourTableName -- 替换为你的实际表名
WHERE
    CAST(PurchaseDate AS TIME) BETWEEN '14:00:00' AND '17:00:00'
GROUP BY
    CAST(PurchaseDate AS DATE),
    Classification
ORDER BY
    PurchaseDay,
    Classification;

关键部分说明： #

• 日期提取：CAST(PurchaseDate AS DATE) 将datetime类型的字段转换为纯日期格式，实现按自然日分组统计
• 时段筛选：CAST(PurchaseDate AS TIME) 提取字段的时间部分，精准匹配14:00至17:00的时间范围（包含14:00:00和17:00:00的记录）
• 聚合计算：SUM(Quantity) 对每个日期+分类组合的采购数量求和
• 分组排序：GROUP BY 确保统计维度正确，ORDER BY 让结果按日期和分类有序排列，提升可读性

可选调整： #

• 如果需要排除17:00整的记录，将WHERE条件修改为：

  CAST(PurchaseDate AS TIME) >= '14:00:00' AND CAST(PurchaseDate AS TIME) < '17:00:00'
  

• 若表数据量较大且PurchaseDate字段有索引，可考虑创建计算列索引优化查询性能：

  ALTER TABLE YourTableName ADD PurchaseTime AS CAST(PurchaseDate AS TIME);
  CREATE NONCLUSTERED INDEX IX_PurchaseTime ON YourTableName(PurchaseTime);
  

内容的提问来源于stack exchange，提问作者Kawaii-Hachii