判断3个或更多连续记录丢失的最佳方法 [英] Best way to tell 3 or more consecutive records missing

问题描述

我正在实施一个成就系统.我尝试创建的徽章"之一将确定:

I'm implementing a achievement system. One of the "badges" I'm trying to create will determine:

1. 如果用户参加了至少一个编码挑战
2. 然后就没有参加连续3次编码挑战
3. 然后再次开始参加.

徽章简称为我会回来的"；-)

The badge is simply called "I'll be back" ;-)

表格

users
==================
id    fullname
1     Gary Green


challenge
==================================
id  name         start_date
1   challenge1   01-AUG-2010
2   challenge2   03-AUG-2010
3   challenge3   06-SEP-2010
4   challenge4   07-SEP-2010
5   challenge5   30-OCT-2010
6   challenge6   05-NOV-2010


entries
====================================================
id   challengeid    userid    type       code
1     1             1         1          -
2     2             1         1          -
3     6             1         1          -
4     6             1         2          -

条目表中的类型"是指条目类型是基于非正则表达式的条目还是基于正则表达式的条目.用户可以同时提交正则表达式和非正则表达式条目，因此，挑战6的上述条目有效.

The "type" in the entries table refers to if the entry type is either a non-regex based entry or regex based one. A user can submit both a regex and non-regex entry, therefore the above entry for challenge 6 is valid.

示例输出

这是我想要的查询的样式输出(在这种情况下，应该授予徽章):

This is the style output of the query I would like (in this case the badge should be awarded):

(for userid 1)
Challenge 1 --> Joined in
Challenge 2 --> Joined in
Challenge 3 --> NULL
Challenge 4 --> NULL
Challenge 5 --> NULL
Challenge 6 --> Joined in

如何?

这是我的问题

1. 在查询中执行此操作的最佳方法是什么?
2. 是否可以在MySQL中使用一个函数来选择此范围而无需借助某些PHP?

---

到目前为止的查询

我正在做一个LEFT OUTER JOIN来连接挑战表和条目表(LEFT OUTER以确保保留用户尚未加入的挑战)，然后按挑战start_date排序以查看用户是否未加入连续挑战3个或更多.

I'm doing a LEFT OUTER JOIN to join the challenge table and entries table (LEFT OUTER to make sure to preserve the challenges the user has not joined in), then sort by challenge start_date to see if the user has not joined in for 3 or more consecutive challenges.

SELECT challenge.id AS challenge_id, entries.id AS entry_id
FROM challenge_entries entries
LEFT OUTER JOIN challenge_details challenge
 ON entries.challengeid = challenge.id
WHERE entries.userid = <user_id>
ORDER BY challenge.start_date
GROUP BY entries.challengeid

---

重要编辑:要使此徽章有意义，标准将需要是3个或更多连续挑战，这些挑战夹在加入的挑战之间，例如上面的示例输出.否则，任何首次参加挑战赛的人都会自动获得徽章.必须将用户视为已经远离挑战已有一段时间(> = 3)

---

important edit: for this badge to make sense the criteria will need to be 3 or more consecutive challenges sandwiched between challenges that were joined in i.e. like the example output above. Otherwise anyone who joins in a challenge for the first time will automatically receive the badge. The user has to be seen to have been "away" from participating in challenges for a while (>=3)

推荐答案

我认为您需要使用其他表来开始...

I think you need to use the other table to start from...

SELECT challenge.id AS challenge_id, entries.id AS entry_id FROM challenge_details challenge
LEFT JOIN challenge_entries entries ON entries.challengeid = challenge.id and entries.userid = <user_id>
ORDER BY challenge.start_date

SELECT challenge.id AS challenge_id, entries.id AS entry_id FROM challenge_details challenge
LEFT JOIN challenge_entries entries ON entries.challengeid = challenge.id and entries.userid = <user_id>
ORDER BY challenge.start_date

可以根据需要添加分组依据...

adding a group by can be done as you want...

这篇关于判断3个或更多连续记录丢失的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！