Crystal Reports 12中没有数据的显示组 [英] Display group with no data in Crystal Reports 12

问题描述

我正在尝试根据年龄对数据进行分组.我使用以下数据库选择:

I am trying to group my data based on age. I use the following database select:

select * from (
select 0 range_start, 11 range_end, '0-10 days' date_description from dual union
select 11, 21, '11-20 days' from dual union  
select 21, 31, '21-30 days' from dual union  
select 31, 99999, '31+ days' from dual) date_helper
left outer join table
on table.date <= date_helper.range_start*-1 + sysdate 
and table.date > date_helper.range_end*-1 + sysdate 

然后我根据 date_description 列创建一个组.我试图让它显示所有组，即使没有属于该组的记录.如果没有记录，我希望它的值为 0，并且仍然打印组.

I then make a group based on the date_description column. I am trying to make it display all groups, even when there are no records, that fall within that group. If there are no records, I want it to have a value of 0, and still print the group.

推荐答案

扩展对 PowerUser 答案的评论，如果您使用的 Crystal 版本允许您输入自己的 SQL(而不必使用 Crystal 的数据库专家)，您可以设置一个充当辅助表的子查询 - 类似于:

Expanding on a comment on PowerUser's answer, if you're using a version of Crystal that allows you to enter your own SQL (instead of having to use Crystal's Database Expert), you can set up a subquery that acts as a helper table - something like:

select * from (
select 0 range_start, 11 range_end, '0-10 days' date_description from dual union
select 11, 21, '11-20 days' from dual union  
select 21, 31, '21-30 days' from dual union  
select 31, 99999, '31+ days' from dual) date_helper
left outer join 
(select sysdate-5 mydate from dual union all 
 select sysdate - 25 from dual) mytable
on mytable.mydate <= date_helper.range_start*-1 + sysdate 
and mytable.mydate > date_helper.range_end*-1 + sysdate 

(Oracle 语法 - 查询的精确语法会因您使用的 SQL 方言而异.)

(Oracle syntax - the precise syntax of the query will vary depending on which dialect of SQL you are using.)

从 SQLServer 更改为 Oracle 语法.

Changed from SQLServer to Oracle syntax.

进一步添加了一些简单的示例数据.

FURTHER Added some simple sample data.

这篇关于Crystal Reports 12中没有数据的显示组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！