使用 Null 值选择和计数

Question

我有以下选择:

SELECT
    COALESCE (opened.ano, closed.ano) AS ano,
    COALESCE (opened.mes, closed.mes) AS mes,
    COALESCE (opened.cnt, 0) AS opened_cases,
    COALESCE (closed.cnt, 0) AS closed_cases
FROM
    (
        SELECT
            YEAR (OPEN_DATE) AS ano,
            MONTH (OPEN_DATE) AS mes,
            COUNT (*) AS cnt
        FROM
            TABLE1,
            TABLE2
        WHERE
            TABLE1.USERNAME = TABLE2.USERNAME
        AND TABLE2.GROUP = 'SUPPORT'
        GROUP BY
            YEAR (OPEN_DATE),
            MONTH (OPEN_DATE)
    ) opened
FULL OUTER JOIN (
    SELECT
        YEAR (CLOSE_DATE) AS ano,
        MONTH (CLOSE_DATE) AS mes,
        COUNT (*) AS cnt
    FROM
        TABLE1,
        TABLE2
    WHERE
        TABLE1.USERNAME = TABLE2.USERNAME
    AND TABLE2.GROUP = 'SUPPORT'
    GROUP BY
        YEAR (CLOSE_DATE),
        MONTH (CLOSE_DATE)
) closed ON opened.ano = closed.ano
AND opened.mes = closed.mes
ORDER BY
    COALESCE (opened.ano, closed.ano) ASC,
    COALESCE (opened.mes, closed.mes) ASC;

结果是:

情况:

具有空值的第一行丢失，因为选择中没有空条件。

谢谢

Answer 1

select
  coalesce(opened.ano, closed.ano) as ano,
  coalesce(opened.mes, closed.mes) as mes,
  coalesce(opened.cnt, 0) as opened_cases,
  coalesce(closed.cnt, 0) as closed_cases
from
(
  select 
    year(open_time) as ano, 
    month(open_time) as mes,
    count(*) as cnt
  from table1
  where groupdesc = 'SUPPORT'
  group by year(open_time), month(open_time)
) opened
full outer join
(
  select 
    year(close_time) as ano, 
    month(close_time) as mes,
    count(*) as cnt
  from table1
  where groupdesc = 'SUPPORT'
  group by year(close_time), month(close_time)
) closed
  on opened.ano = closed.ano and opened.mes = closed.mes
where closed.mes is not null
order by coalesce(opened.ano, closed.ano) desc, coalesce(opened.mes, closed.mes)    desc;

这是添加了 WHERE 子句的 SQL。您的评论似乎表明您要查找的内容包含在此结果集中。有 10 个 Opened_cases 和 8 个 closed_cases。