Oracle group by和空的结果集 [英] Oracle group by and the empty resulting set

问题描述

我有两个表：Change和ChangeTicket 。
有1：n的关系。一个改变有cero或许多改变票。
没有变化意味着没有变化票。
changeTicket有一个状态（打开/关闭/ ...）
changeTicket有一个字段，表示多少分钟进行此更改。

变更有一个公司和一个月。每月至多有一次更改。

我必须报告给定的公司和给定月份的分钟总和
a。

我写了下面的SQL语句。

  select nvl（sum （service_req），0）作为SUM_REQ 
从Change_Ticket，更改
其中Change.company_id（+）='0'
和Change.month（+）='07'
和Change.Id（+）= Change_Ticket.Change_Id 
  

这是OK。

如果对于给定的月份和给定的公司，既没有变化也没有票据
，那么我得到一个空值，它被转换为NVL函数的cero usgin。

当我想使用状态对信息进行分组时，会出现问题。
如果我添加grup子句

 选择Change_Ticket.status，nvl（sum（service_req），0）作为SUM_REQ Change_Ticket 
，Change 
其中Change.company_id（+）='0'
和Change.month（+）='07'
和Change.Id（+）= Change_Ticket.Change_Id 
 group by Change_Ticket.status 
  

然后我的结果是空集。
我知道没有任何状态，然后得到的集合是
以某种方式一致，然后返回一个空的结果集合。

我怎样才能避免这个问题。在这种情况下，我需要报告
空状态和 cero 作为金额。

（顺便说一句，我也尝试过把 nvl（Change_Ticket.status，'none'），但没有奏效）

非常感谢

Luis

解决方案

我认为要实现您的目标希望你需要这样做：

 选择Change_Ticket.status，nvl（sum（service_req），0）作为SUM_REQ $ b Change_Ticket $ b，更改
其中Change.company_id（+）='0'
和Change.month（+）='07'
和Change.Id（+）= Change_Ticket。 Change_Id 
组由Change_Ticket.status 
 union all 
选择''as STATUS，0作为SUM_REQ 
从双
不存在（从Change_ticket中选择null）
  

I have the following SQL problem.

Scenario:

I have two tables: Change and ChangeTicket. There is a 1:n relationship. One Change has cero or many changeTickets. No change means no changeTickets. A changeTicket has a status (open/closed/...) A changeTicket has a field representing how many minutes took this change.

A Change has a company and a month. There is at most ONE change in the month.

I have to report for a given company and for a given month the sum of minutes a given change took.

I wrote the following SQL statement.

select  nvl(sum(service_req), 0) as SUM_REQ 
from Change_Ticket, Change
where Change.company_id (+) = '0' 
  and Change.month (+)='07'
  and Change.Id  (+) = Change_Ticket.Change_Id 

This is OK.

If for the given month and the given company there are neither changes nor tickets then I get a null value which is converted to cero usgin the NVL function.

The problem arises when I want to group the information using the status. If I add a grup clause

select  Change_Ticket.status, nvl(sum(service_req), 0) as SUM_REQ 
from Change_Ticket, Change
where Change.company_id (+) = '0' 
  and Change.month (+)='07'
  and Change.Id  (+) = Change_Ticket.Change_Id 
group by Change_Ticket.status

then my result is the empty set. I understand that there is no status and then the resulting set is somehow consistent and then an empty resulting set is returned.

How can I avoid this problem. In this case I need to report an empty status and cero as the the sum.

(BTW, I've also tried putting nvl(Change_Ticket.status, 'none') but didn't work)

Thanks a lot in advance.

Luis

解决方案

I think to achieve what you want you would need to do this:

select  Change_Ticket.status, nvl(sum(service_req), 0) as SUM_REQ 
from Change_Ticket, Change
where Change.company_id (+) = '0' 
  and Change.month (+)='07'
  and Change.Id  (+) = Change_Ticket.Change_Id 
group by Change_Ticket.status
union all
select '' as STATUS, 0 as SUM_REQ
from dual
where not exists (select null from Change_ticket)

这篇关于Oracle group by和空的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！