代码之家 › 专栏 › 技术社区 › Julio Guerra

Oracle Select:每个团队和每年的事件计数

oracle9i plsql oracle sql

Julio Guerra · 技术社区 · 14 年前

如标题中所述,我想选择每个团队和每年的事件数。下面的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

我不知道如何修改我的请求。

3 回复 | 直到 14 年前

andrem 14 年前

我这里没有要测试的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
;

你会得到每年的结果,在那里你有一个活动的活动。如果这还不够,您可以用一个填充了所有年份的表来替换年份子表。

OMG Ponies 14 年前

用途:

   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 绑定变量。

先前:

   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 相反…

Julio Guerra 14 年前

所以它起作用了!:) 这是我的最后一个问题:

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
;

谢谢您!