Oracle SELECT：每个团队每年的事件计数

Question

正如标题中所述，我想选择每个团队和每年的赛事数量。下面的 select 语句工作正常，但没有给出我想要的内容。

SELECT
 Team.team_id,
 TO_CHAR(Event.START_DATE_TIME, 'yyyy') AS year,
 count(event_id)    AS events
FROM
 Team
LEFT OUTER JOIN
 Event ON Event.team_id = Team.team_id
GROUP BY
 TO_CHAR(Event.START_DATE_TIME, 'yyyy'),
 team_id
ORDER BY
 year  ASC,
 team_id  ASC
;

有了这个，如果我们有：

Team 1 : 1 event in 2006
Team 2 : 1 event in 2007

我们获得：

ID | Year | Events
------------------
1  | 2006 | 1
2  | 2007 | 1

我想获得：

ID | Year | Events
-------------------
1  | 2006 | 1
2  | 2006 | 0
1  | 2007 | 0
2  | 2007 | 1

我不知道如何修改我的请求来这样做。

Answer 1

所以它有效！ :)
这是我的最后一个询问：

SELECT
 Team.name,
 Years.year,
 count(Event.event_id)
FROM
    Team
JOIN
    (
        SELECT DISTINCT EXTRACT(YEAR FROM start_date_time) AS year
        FROM Event
    ) Years ON 1 = 1
LEFT OUTER JOIN
    Event ON    Event.team_id = Team.team_id
                AND EXTRACT(YEAR FROM Event.start_date_time) = Years.year
WHERE
    event_type = 'C'
GROUP BY
    Team.name,
    Years.year
ORDER BY
    Year ASC,
    name ASC
;

谢谢！

Answer 2

我这里没有 Oracle 来测试，但一般来说这应该有效：

SELECT
 Team.team_id,
 Years.year,
 COALESCE(count(Event.event_id),0) events
FROM
 Team
   JOIN (SELECT DISTINCT EXTRACT(YEAR FROM start_date_time) year FROM Event) Years ON 1=1
   LEFT OUTER JOIN Event ON Event.team_id = Team.team_id AND EXTRACT(YEAR FROM Event.start_date_time) = Years.year
GROUP BY
 Years.year,
 team_id
ORDER BY
 year  ASC,
 team_id  ASC
;

您将获得每年的结果，其中您有活动中的活动。如果这还不够，您可以将 Years 子项替换为包含所有年份的表格。

Answer 3

使用：

   SELECT x.team_id,
          x.year,
          COALESCE(COUNT(e.event_id), 0) AS events
     FROM (SELECT :start_year + LEVEL - 1 AS year,
                  t.team_id
             FROM DUAL, TEAM t
       CONNECT BY :start_year + LEVEL - 1 <= :end_year) x
LEFT JOIN EVENT e ON EXTRACT(YEAR FROM e.start_date_time) = x.year
                 AND e.team_id = x.team_id

这将生成一个年份列表，但您必须设置 :start_year 和 :end_year BIND 变量。

以前：

   SELECT x.team_id,
          x.year,
          COALESCE(COUNT(e.event_id), 0) AS events
     FROM (SELECT 2006 AS year,
                  a.team_id
             FROM TEAM a
           UNION ALL
           SELECT 2007,
                  b.team_id
             FROM TEAM b) x
LEFT JOIN EVENT e ON EXTRACT(YEAR FROM e.start_date_time) = x.year
                 AND e.team_id = x.team_id

TO_CHAR 可以获取年份，但将其作为 CHAR(4) 返回，因此您需要使用 TO_NUMBER(TO_CHAR(date_col, 'yyyy')) 来获取数字。所以我使用 EXTRACT 代替......