真实需求中的SQL技巧分享： CROSS JOIN（笛卡尔积）扩表，显式日期转换函数（如 TO_DATE、DATE_PART）和字符串截取 + 数值运算来处理不同日期格式方式对比

永洪报表脚本个人费用明细表名个人费用明细页面有日期筛选控件来动态传参动态参数是 ?{yh_date}YYYY-MM注意日期格式不一致动态参数用来筛选年份字段nianfYYYYMM (202509)注意日期筛选的结果不是查询当前选中的月而是今年以来至当前选中的月的累计值是一个时间区间不是仅查询一个月的数据。页面有部门筛选控件来动态传参动态参数是 ?{yh_company} 动态参数用来筛选部门字段bum员工工号yuanggh这一列保留一个中文字符的缩进涉及金额的计算取值 kaohje 单位元保留2位小数金额可能为负数。没有值的保持为NULL不要取0。数据频率月度WITH params AS ( SELECT CAST(SUBSTRING(?{yh_date}, 1, 4) AS INT) AS cur_year, CAST(SUBSTRING(?{yh_date}, 6, 2) AS INT) AS cur_month ), filtered AS ( SELECT f.bum, f.yuanggh, f.yuangxm, f.kaohje, f.kemdl, f.kemmc FROM financialdb.financialetl.dw_gk_fymxb_yhsc f CROSS JOIN params p WHERE f.nianf BETWEEN p.cur_year * 100 1 AND p.cur_year * 100 p.cur_month AND (COALESCE(?{yh_company}, ) OR f.bum ?{yh_company}) ), emp AS ( SELECT bum, yuanggh, yuangxm, SUM(CASE WHEN kemdl 差旅费 AND kemmc 出差杂费 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS zc_zf, SUM(CASE WHEN kemdl 差旅费 AND kemmc 机票 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS zc_jp, SUM(CASE WHEN kemdl 差旅费 AND kemmc 境外差旅 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS zc_jw, SUM(CASE WHEN kemdl 差旅费 AND kemmc 其他交通费 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS zc_qtjt, SUM(CASE WHEN kemdl 差旅费 AND kemmc 预订服务费 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS zc_ydfw, SUM(CASE WHEN kemdl 差旅费 AND kemmc 住宿费 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS zc_zs, SUM(CASE WHEN kemdl 差旅费 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS zc_total, SUM(CASE WHEN kemdl 业务招待费 AND kemmc 业务招待费 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS yw_zf, SUM(CASE WHEN kemdl 业务招待费 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS yw_total, SUM(CASE WHEN kemdl 差旅费 or kemdl 业务招待费 THEN CAST(kaohje AS NUMERIC) ELSE 0 END) AS grand FROM filtered GROUP BY bum, yuanggh, yuangxm ) SELECT bum AS dept_name, yuanggh, yuangxm, CASE WHEN zc_zf 0 THEN NULL ELSE ROUND(zc_zf, 2) END AS 出差杂费, CASE WHEN zc_jp 0 THEN NULL ELSE ROUND(zc_jp, 2) END AS 机票, CASE WHEN zc_jw 0 THEN NULL ELSE ROUND(zc_jw, 2) END AS 境外差旅, CASE WHEN zc_qtjt 0 THEN NULL ELSE ROUND(zc_qtjt, 2) END AS 其他交通费, CASE WHEN zc_ydfw 0 THEN NULL ELSE ROUND(zc_ydfw, 2) END AS 预订服务费, CASE WHEN zc_zs 0 THEN NULL ELSE ROUND(zc_zs, 2) END AS 住宿费, CASE WHEN zc_total 0 THEN NULL ELSE ROUND(zc_total, 2) END AS 差旅费汇总, CASE WHEN yw_zf 0 THEN NULL ELSE ROUND(yw_zf, 2) END AS 业务招待费, CASE WHEN yw_total 0 THEN NULL ELSE ROUND(yw_total, 2) END AS 业务招待费汇总, CASE WHEN grand 0 THEN NULL ELSE ROUND(grand, 2) END AS 总计 FROM emp ORDER BY bum, yuanggh;为什么要用 CROSS JOIN 而不是直接筛选年月的日期使用了CROSS JOIN params p将主表f与参数表p做笛卡尔积然后通过WHERE条件进行过滤。这样做的主要目的是为了在WHERE子句中能够直接引用p.cur_year和p.cur_month这两个参数从而构建动态的年月范围条件。 为什么不直接筛选其实您完全可以在WHERE中直接使用参数不需要CROSS JOIN。例如sqlWHERE f.nianf BETWEEN CAST(SUBSTRING(?{yh_date}, 1, 4) AS INT) * 100 1 AND CAST(SUBSTRING(?{yh_date}, 1, 4) AS INT) * 100 CAST(SUBSTRING(?{yh_date}, 6, 2) AS INT)但这种写法会多次重复计算SUBSTRING和CAST代码可读性差且参数值在每行都会被重新计算虽然数据库优化器可能会缓存但通常不推荐。✅ 使用CROSS JOIN的优势代码简洁清晰参数只解析一次放在 CTE 中后面直接引用列名易于维护。性能影响CROSS JOIN会产生笛卡尔积主表行数 × 1但由于参数表只有一行实际和直接引用参数在逻辑上等效。数据库优化器通常能识别这种模式将其转化为常量过滤不会产生额外的性能开销许多优化器会做谓词下推将p.cur_year视为常量。⚠️ 潜在风险如果参数表有多行比如多个参数CROSS JOIN会导致结果膨胀但此处只有一行风险可忽略。不过有些数据库如 PostgreSQL对CROSS JOIN的优化可能不如直接使用常量高效尤其是在复杂查询中。但大多数情况下这种写法是安全的。 替代写法更直接如果您希望避免CROSS JOIN可以将参数值直接嵌入WHERE中或者使用子查询sqlWHERE f.nianf BETWEEN (SELECT cur_year * 100 1 FROM params) AND (SELECT cur_year * 100 cur_month FROM params)但子查询也会增加复杂度。 结论当前写法是合理且常见的它通过CROSS JOIN将参数引入查询使代码更模块化。如果您担心性能可以检查执行计划确认优化器是否将p.cur_year作为常量处理。如果仍有顾虑可改为直接在WHERE中计算但那样会牺牲可读性。详细解释下为什么用CROSS JOIN 更好好的我来详细解释这段 SQL 代码的含义和作用特别是为什么使用CROSS JOIN。这段代码在做什么整体目标是从费用明细表中筛选出“当年 1 月到指定月份”且“属于某个部门”的所有原始记录按员工汇总。第一步解析参数paramsCTEsqlparams AS ( SELECT CAST(SUBSTRING(?{yh_date}, 1, 4) AS INT) AS cur_year, CAST(SUBSTRING(?{yh_date}, 6, 2) AS INT) AS cur_month )?{yh_date}是一个参数格式如2026-01年-月。SUBSTRING(..., 1, 4)取出前 4 位2026转成整数2026命名为cur_year。SUBSTRING(..., 6, 2)取出第 6-7 位01转成整数1命名为cur_month。结果params这个临时表只有一行包含两列cur_year 2026cur_month 1。第二步过滤数据filteredCTEsqlfiltered AS ( SELECT f.bum, f.yuanggh, f.yuangxm, f.kaohje, f.kemdl, f.kemmc FROM financialdb.financialetl.dw_gk_fymxb_yhsc f CROSS JOIN params p WHERE f.nianf BETWEEN p.cur_year * 100 1 AND p.cur_year * 100 p.cur_month AND (COALESCE(?{yh_company}, ) OR f.bum ?{yh_company}) )1.FROM ... CROSS JOIN params pCROSS JOIN是笛卡尔积会把左表f的每一行和右表params的每一行组合在一起。因为params只有1 行所以f的每一行都会和这一行组合结果行数不变但每一行都额外带上了p.cur_year和p.cur_month两个字段。效果相当于给原表的每一行“贴上了”年份和月份参数方便后面用这些参数来筛选。2.WHERE条件f.nianf BETWEEN p.cur_year * 100 1 AND p.cur_year * 100 p.cur_monthnianf是表中的字段存储格式为YYYYMM如202601。p.cur_year * 100 1→2026*100 1 202601p.cur_year * 100 p.cur_month→2026*100 1 202601所以条件等价于f.nianf 202601即只取 2026 年 1 月的数据。如果参数是2026-03则范围是202601到202603即 1~3 月的数据。AND (COALESCE(?{yh_company}, ) OR f.bum ?{yh_company})?{yh_company}是另一个参数用于部门筛选。如果该参数为空或空字符串则条件恒为真不限制部门。否则只取bum等于该参数值的记录。3.SELECT列表只取原始表中的几个字段bum,yuanggh,yuangxm,kaohje,kemdl,kemmc这些都是后续汇总需要用的。最终filtered返回什么返回的是一张临时表里面包含了所有满足日期范围当年 1 月到参数月份且满足部门筛选条件的原始明细记录。每一行都包含部门、工号、姓名、考核金额、科目大类、科目名称。这些记录还没有被汇总是“原始数据”。为什么要用CROSS JOIN为什么不直接写❌ 如果不用CROSS JOIN你需要这样写sqlWHERE f.nianf BETWEEN CAST(SUBSTRING(?{yh_date}, 1, 4) AS INT) * 100 1 AND CAST(SUBSTRING(?{yh_date}, 1, 4) AS INT) * 100 CAST(SUBSTRING(?{yh_date}, 6, 2) AS INT)同样的一段计算写了三遍又长又容易错。每次都要重新解析参数代码可读性极差。✅ 使用CROSS JOIN的好处代码简洁参数只解析一次放在 CTE 里后面直接引用列名维护方便。逻辑清晰一眼就能看出cur_year和cur_month是从参数来的。性能无影响CROSS JOIN只产生 1 倍的笛卡尔积因为右表只有一行数据库优化器会把它当作常量来处理不会因为笛卡尔积导致行数爆炸。便于扩展如果以后需要多个参数只需在params里加列即可不用改WHERE里的复杂表达式。总结paramsCTE → 把日期参数拆成年和月。filteredCTE → 用CROSS JOIN把参数附到每一行然后根据这些参数筛选出符合条件的原始明细记录。返回的是未汇总的明细数据供后续分组聚合使用。这种写法是 SQL 中处理参数化查询的常见模式既清晰又高效。日期格式转换使用转换函数 和 数字运算 两种方式 比较好的我来详细对比一下显式日期转换函数如TO_DATE、DATE_PART和字符串截取 数值运算这两种处理日期参数的方法。方法一字符串截取 数值运算代码中使用的sqlparams AS ( SELECT CAST(SUBSTRING(?{yh_date}, 1, 4) AS INT) AS cur_year, CAST(SUBSTRING(?{yh_date}, 6, 2) AS INT) AS cur_month )步骤操作说明1SUBSTRING(2026-01, 1, 4)取前4位 →20262CAST(... AS INT)转为数字 →20263SUBSTRING(2026-01, 6, 2)取第6-7位 →014CAST(... AS INT)转为数字 →15p.cur_year * 100 p.cur_month合成范围 →202601方法二显式日期转换函数sqlparams AS ( SELECT EXTRACT(YEAR FROM TO_DATE(?{yh_date}, YYYY-MM)) AS cur_year, EXTRACT(MONTH FROM TO_DATE(?{yh_date}, YYYY-MM)) AS cur_month )步骤操作说明1TO_DATE(2026-01, YYYY-MM)将字符串解析为日期类型 →2026-01-012EXTRACT(YEAR FROM ...)提取年份 →20263EXTRACT(MONTH FROM ...)提取月份 →1 多维度对比对比维度方法一字符串截取 数值运算方法二显式日期转换函数性能✅更快纯字符串处理无日期解析开销❌ 相对较慢需解析日期类型可读性❌ 需要理解SUBSTRINGCAST稍显复杂✅更直观见名知意一眼看出是日期处理可移植性⚠️ 依赖SUBSTRING和CAST几乎所有数据库都支持⚠️ 不同数据库函数不同PostgreSQL 用DATE_PARTMySQL 用YEAR()/MONTH()Oracle 用EXTRACT灵活性✅ 字符串位置固定即可不受日期格式影响✅ 可适配多种日期格式通过TO_DATE的格式掩码返回类型✅ 直接返回整数可直接参与算术运算⚠️ 返回DOUBLE PRECISION或DECIMAL可能需要额外CAST出错风险⚠️ 如果?{yh_date}格式变化如2026/01会解析错误✅ 格式掩码可预先声明若格式不匹配会明确报错 为什么代码中选择了方法一1.性能更优方法一只是字符串截取 类型转换不需要调用日期解析引擎。在数据量大、查询频次高时方法一的性能优势会更明显。2.与nianf字段类型匹配表里的nianf字段存储的是数值类型如202601。方法一直接生成整数范围202601~202603可以直接比较无需额外转换。方法二需要额外计算TO_NUMBER(TO_CHAR(date, YYYYMM))多了一次转换。3.避免时区和日期格式的坑TO_DATE依赖数据库的日期格式设置不同环境可能解析结果不同如01/02是 1月2日还是2月1日。方法一绕开了日期解析只要字符串位置固定结果就确定。4.跨数据库兼容性虽然方法二看起来更标准但不同数据库的日期函数差异很大。方法一的SUBSTRINGCAST在几乎所有数据库中都有相同的行为。 总结与建议场景推荐方法理由日期字段在表中是数值如nianf 202601✅方法一数值运算无需转换直接比较性能最佳日期字段在表中是日期类型方法二日期函数直接提取年份月份无需多余转换需要灵活支持多种日期格式方法二日期函数TO_DATE的格式掩码可适配多种输入格式追求代码可读性方法二日期函数更符合 SQL 标准写法易于理解追求极致性能✅方法一数值运算避免了日期解析的开销需要跨数据库平台兼容✅方法一数值运算不依赖特定数据库的日期函数 您当前场景的最佳实践您的表里nianf是数值类型YYYYMM而且您的报表要统计当年1月到参数月份的累计数据因此使用方法一字符串截取 数值运算是最合适的选择。如果将来需要更复杂的日期逻辑如按季度、按周统计可能就需要结合日期函数使用了。目前这个场景方法一已经足够且高效。