如何在iReport中使用SQL查询同一项的多个SUM

问题描述：

我正在使用iReport创建一个JasperReport，因此，我受限于一个SQL查询。

I'm creating a JasperReport using iReport, and as such, I'm limited* to one SQL query.

我有一个'统计'表，带有'name'（VARCHAR），'count'（INTEGER）和'datetime'（DATETIME）列。

I have a table 'statistics', with a 'name' (VARCHAR), 'count'(INTEGER), and 'datetime'(DATETIME) columns.

当'name'为最后一天的test时，得到'count'列的总和很简单，对于上一周和月也是如此（见下文）

It is simple enough to get the sum of the 'count' column when the 'name' was "test" for the last day, and similarly for the last week, and month (see bellow)

工作SQL语句：


SELECT
  SUM(count)as 'today'
FROM
  statistics
WHERE
   name = "test"
  AND $P{oneDayAgo} <= datetime
  AND datetime <= $P{now}

但是，由于我只有一个SQL语句可以使用，我需要以某种方式组合它们。我尝试使用UNION（如下），但这不起作用。

失败的SQL语句：

Failed SQL Statement:


SELECT
     SUM(count)as 'today'
FROM
     statistics
WHERE
     name = "test"
 AND $P{oneDayAgo} <= datetime
 AND datetime <= $P{now}
UNION
SELECT
     SUM(count)as 'thisWeek'
FROM
     statistics
WHERE
     name = "test"
 AND $P{oneWeekAgo} <= datetime
 AND datetime <= $P{now}
UNION
SELECT
     SUM(count)as 'thisMonth'
FROM
     statistics
WHERE
     name = "test"
 AND $P{oneMonthAgo} <= datetime
 AND datetime <= $P{now}

（*）可以添加额外的仅查询图形或交叉表，这两者都不符合我的目的。

(*) one can add additional queries only for graphs or cross-tabs, neither of which serve my purpose.

答

总和（情况 - 条件 - 然后计数否则0结束）

sum(case when -condition- then count else 0 end)

SELECT
  SUM(case when $P{oneDayAgo} <= datetime then count else 0 end) as 'today',
  SUM(case when $P{oneWeekAgo} <= datetime then count else 0 end) as 'thisweek',
  SUM(count) as 'thismonth'
FROM
  statistics
WHERE
   name = "test"
  AND $P{oneMonthAgo} <= datetime
  AND datetime <= $P{now}

请注意，如果您需要平均值，请务必将NULL替换为0。

note that if you need averages, be sure to substitute NULL for 0.

如何在iReport中使用SQL查询同一项的多个SUM

相关推荐