退货数据限制7天

Question

我有一个用来存储事件的表

CREATE TABLE EVENT(
  ID INTEGER NOT NULL,
  SOURCE VARCHAR2(50 ),
  TYPE VARCHAR2(50 ),
  EVENT_DATE DATE,
  DESCRIPTION VARCHAR2(100 )
)
/

我使用此查询来获取所有类型事件的结果

dbRequest.setPreparedStatement("SELECT "
            + " COUNT(*) \"ALL\","
            + " COUNT(CASE WHEN TYPE = 'Critical' THEN 1 END) \"CRITICAL\", "
            + " COUNT(CASE WHEN TYPE = 'Info' THEN 1 END) \"INFO\", "
            + " COUNT(CASE WHEN TYPE = 'Warning' THEN 1 END) \"WARN\", "
            + " COUNT(CASE WHEN TYPE = 'Error' THEN 1 END) \"ERROR\" "
            + " FROM "
            + " EVENT");

如何修改查询以获取最近7天的行？此外，由于某些原因，查询不能处理低资本数据。

Answer 1

dbRequest.setPreparedStatement("SELECT "
            + " COUNT(*) \"ALL\","
            + " COUNT(CASE WHEN TYPE = 'Critical' THEN 1 END) \"CRITICAL\", "
            + " COUNT(CASE WHEN TYPE = 'Info' THEN 1 END) \"INFO\", "
            + " COUNT(CASE WHEN TYPE = 'Warning' THEN 1 END) \"WARN\", "
            + " COUNT(CASE WHEN TYPE = 'Error' THEN 1 END) \"ERROR\" "
            + " FROM "
            + " EVENT"
            + " WHERE EVENT_DATE >= TRUNC(SYSDATE)-7");