Question

我有三个表格:

表1 - 成员

Id   Name
1    Member1
2    Member2

表2 - 活动

Id   Member_id   Activity    Date
 1      1        Activity1   2001-1-1
 2      1        Activity2   2001-2-1
 3      1        Activity3   2001-3-1
 4      2        Test        2010-4-1

表3 - 详细情况

Id  Activity_id Information
1   2       Information

下表显示:

SELECT Member.id, COUNT(*) AS number
FROM Member 
INNER JOIN Activity ON Activity.Member_ID = Member.id
GROUP BY Member.id

我的问题是,我如何从表(1)中找到从事两项活动的所有成员的第二次活动(表2),并从表3中检索与这项活动有关的信息。

预收

Answer 1

罗位职能有助于你出席。

select *
from (
    select 
        *,
        rn = row_number() over (partition by member_id order by id)
    from
        activity
) activityWithRn
inner join detailed on detailed.activity_id = activityWithRn.id
where activityWithRn.rn = 2

我在此将数据按成员分类,并命令进行该项活动,但可能要按日期进行?

Answer 2

与“@mouters”一样,ROW_NUMBER(功能在此确实有用。但我认为,排名应当与“@mouters”的建议相比稍有不同。

我在此试图找到解决办法:

WITH ranked AS (
  SELECT
    *,
    rn = ROW_NUMBER() OVER (PARTITION BY Member_id)
  FROM Activity
)
SELECT
  a.Member_id,
  number      = COUNT(*),
  Activity_id = MAX(d.Activity_id),
  Information = MAX(d.Information)
  Date        = MAX(CASE WHEN d.Id IS NOT NULL THEN a.Date)
FROM ranked a
  LEFT JOIN Detailed d ON a.Id = d.Activity_id AND a.rn = 2
GROUP BY a.Member_id

友情链接