我在java项目中有一个sql查询。它必须在 oracle 和 h2 中执行，具体取决于客户端及其 DBMS 选择。它必须返回值，其中 sent_date 是月份减一（上个月注册表）SELECT * FROM TABLE WHERE SENT_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1)
AND LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1)) ORDER BY ELAPSED_TIME DESC问题是，LAST_DAY 函数是 Oracle 特定函数，H2 中不存在，并且 H2 日期管理有时在 Oracle 中不起作用，但我需要它完全兼容。我们怎样才能做到这一点？
2 回答
小唯快跑啊
TA贡献1863条经验 获得超2个赞
它可能会更高兴：
SELECT * FROM TABLE
WHERE SENT_DATE BETWEEN ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1) AND TRUNC(SYSDATE,'mm') - 1
ORDER BY ELAPSED_TIME DESC
表明它们评估为相同的值：
SELECT ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1) as orig_start,
LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1)) as orig_end,
TRUNC(SYSDATE,'mm') - 1 as new_end
FROM dual;
ORIG_START ORIG_END NEW_END
------------------- ------------------- -------------------
2019-08-01 00:00:00 2019-08-31 00:00:00 2019-08-31 00:00:00
但请记住，这between是包容性的，因此，如果您sent_date有非午夜时间，则会排除 2019-08-31 00:00:01 到 2019-08-31 23:59:59 之间的任何内容。
这样做更安全：
SELECT * FROM TABLE
WHERE SENT_DATE >= ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1)
AND SENT_DATE < TRUNC(SYSDATE,'mm')
ORDER BY ELAPSED_TIME DESC
其中将包括 2019-08-01 00:00:00 或之后的所有内容，以及直到（但不包括）2019-09-01 00:00:00 的所有内容 - 这样就不会出现缺失的一天。
或者，如果它不能识别，add_months也许：
SELECT * FROM TABLE
WHERE SENT_DATE >= TRUNC(SYSDATE,'mm') - INTERVAL '1' MONTH
AND SENT_DATE < TRUNC(SYSDATE,'mm')
ORDER BY ELAPSED_TIME DESC
（我无法测试其中任何一个是否在 H2 中实际工作...... *8-）
慕妹3146593
TA贡献1820条经验 获得超9个赞
LAST_DAY(ADD_MONTHS(TRUNC(SYSDATE,'mm'),-1))
可以简化为
TRUNC(SYSDATE,'mm') - 1
没有LAST_DAY功能，但 H2 也不支持带有 'mm' 参数的非标准TRUNC。
所以你也需要避免它。这样的查询将与两个数据库兼容（但是，您需要最新版本的 H2，例如 1.4.199）：
SELECT * FROM tableName WHERE SENT_DATE BETWEEN
CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1) - INTERVAL '1' MONTH
AND
CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE)
ORDER BY ELAPSED_TIME DESC
请注意，这个查询不是标准的，我故意使用Oracle风格的日期算法来简化查询，它是H2支持的。ADD_MONTH如果您愿意，也可以在此处使用非标准。
如果您只需要使用标准语法来兼容更多数据库，则可以避免使用 Oracle/H2 风格的算术：
SELECT * FROM tableName WHERE SENT_DATE BETWEEN
CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1) * INTERVAL '1' DAY - INTERVAL '1' MONTH
AND
CURRENT_DATE - EXTRACT(DAY FROM CURRENT_DATE) * INTERVAL '1' DAY
ORDER BY ELAPSED_TIME DESC
另请注意，Oracle与其他数据库不同，没有正常的DATE数据类型，其DATE类型可能有时间（H2可以在Oracle兼容模式下模拟这种偏差）。因此，只有当您确保日期都在 时，所有这些带有“BETWEEN”的查询（包括最初的仅 Oracle 查询）才能正常工作00:00:00。如果您的日期还包含时间部分，您需要使用几个比较运算符：
SELECT * FROM tableName WHERE SENT_DATE >=
CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1) - INTERVAL '1' MONTH
AND SENT_DATE <
CURRENT_DATE - (EXTRACT(DAY FROM CURRENT_DATE) - 1)
ORDER BY ELAPSED_TIME DESC
（这里也使用了 Oracle/H2 算法，其他数据库的乘法可能INTERVAL '1' DAY是必要的。）
添加回答
举报
0/150
提交
取消