如何从以下模式中提取SQL计数？

Question

假设我有一个桌面会议，如下所示

M_DATE
M_RACE_NO
M_VENUE
M_ATHLETE

主键是日期、比赛编号、地点、运动员。每个日期/比赛编号/地点可以包含多个可能的运动员。假设对于特定的运动员，我希望提取日期、比赛编号、地点以及在该特定日期/比赛编号/地点参加比赛的运动员总数。

假设运动员博尔特参加了两个项目的比赛，总场分别为7和9，我想要的东西如下

2011-10-03, 4, NY, 7
2010-24-02, 5, SY, 9

我知道它看起来像是

SELECT M_DATE, M_RACE_NO, M_VENUE, SUM(...) FROM MEETING WHERE M_ATHLETE='Bolt'

我不知道SUM之后是什么

Answer 1

如果您的RDBMS支持此功能：

SELECT M_DATE, M_RACE_NO, M_VENUE,
   COUNT(*) OVER (PARTITION BY M_DATE, M_RACE_NO, M_VENUE)
FROM MEETING
WHERE M_ATHLETE='Bolt'

如果不是：

select m1.m_date, m1.m_race_no, m1.m_venue, m2.count_races
from meeting m1
join (select m_date, m_race_no, m_venue, count(*) count_races
      from meeting group by m_date, m_race_no, m_venue) m2
  on m1.m_date = m2.m_date
  and m1.m_race_no = m2.m_race_no
  and m1.m_venue = m2.m_venue
where m1.m_athlete = 'Bolt'

Answer 2

SELECT 
    m.m_date
  , m.m_race_no
  , m.m_venue
  , COUNT(*) AS cnt 
FROM 
        meeting AS m
    JOIN
        meeting AS m2
            ON  m2.m_date    = m.m_date
            AND m2.m_race_no = m.m_race_no
            AND m2.m_venue   = m.m_venue
WHERE 
    m.m_athlete = 'Bolt'
GROUP BY
    m.m_date
  , m.m_race_no
  , m.m_venue