SQL如何按2个不同的日期列对数据进行分组？

Question

我得到一个像这样的表：

    Id     Date1          Date2     Status
    ----------------------------------------------
     1    01/01/2010     null         A
     2    04/04/2010      05/14/10    X
     3    01/01/2010     null         A
     4    01/11/2010      01/01/2010  X
     5    01/02/2010     null         A

和其他几条记录，日期 1 不为空，但仅当状态为 A 时，它才在分组依据中相关，对于日期 2 不为空的记录，无论分组依据的状态如何到此日期2。

所需的结果集如下：

 Date             Number of A Status        Number of Date 2 not null statuses
------------------------------------------------------------------------------
 01//01/2010            2                                     1
 01/02/2010             1                                     0
 05/14/2010             0                                     1 

基本上分组依据必须按日期分组，问题是在某些情况下它针对 Date1 列，而在其他情况下针对 Date2 列。如何才能做到这一点？

Answer 1

您可以按解码或大小写表达式进行分组。只在 Oracle 中尝试过，所以不确定这是否可移植。有了这些数据：

create table t42 as
select 1 id, to_date('01/01/2010') date1, null date2, 'A' status from dual
union select 2, to_date('04/04/2010'), to_date('05/14/2010'), 'X' from dual
union select 3, to_date('01/01/2010'), null, 'A' from dual
union select 4, to_date('01/11/2010'), to_date('01/01/2010'), 'X' from dual
union select 5, to_date('01/02/2010'), null, 'A' from dual
/

select * from t42;

ID                     DATE1                     DATE2                     STATUS
---------------------- ------------------------- ------------------------- ------
1                      01/01/2010                                          A     
2                      04/04/2010                05/14/2010                X     
3                      01/01/2010                                          A     
4                      01/11/2010                01/01/2010                X     
5                      01/02/2010                                          A

您可以执行

select case when date2 is null and status = 'A' then date1
        else date2 end as "Date",
    sum(case when status = 'A' then 1 else 0 end) as "Number of A status",
    sum(case when date2 is null then 0 else 1 end) as "Number of Date 2 null"
from t42
group by case when date2 is null and status = 'A' then date1 else date2 end
order by 1;

以下操作：

Date                      Number of A status     Number of Date 2 null  
------------------------- ---------------------- ---------------------- 
01/01/2010                2                      1                      
01/02/2010                1                      0                      
05/14/2010                0                      1

Answer 2

这是一个典型的 PIVOT 查询：

   SELECT x.date,
          SUM(CASE WHEN 'A' IN (y.status, z.status) THEN 1 ELSE 0 END) AS NumStatusA,
          SUM(CASE 
                WHEN y.date2 IS NOT NULL OR z.date2 IS NOT NULL THEN 1 
                ELSE 0 
              END) AS NumDate2NotNull
     FROM (SELECT a.date1 AS date
             FROM YOUR_TABLE a
           UNION 
           SELECT b.date2 AS date
             FROM YOUR_TABLE b) x
LEFT JOIN YOUR_TABLE y ON y.date1 = x.date
LEFT JOIN YOUR_TABLE z ON z.date1 = x.date
 GROUP BY x.date

但是您需要根据您的数据派生一个包含两列日期的表，以便首先进行联接。

Answer 3

也许是这样的：

SELECT DISTINCT Date1 as Date,
(SELECT COUNT(*) FROM MyTable WHERE DATE1=MyT.Date1 AND Status = 'A') NumberAStatus,
(SELECT COUNT(*) FROM MyTable WHERE DATE1=MyT.Date1 AND Date2 is not null) NotNullDate2

FROM MyTable MyT