SQL用于基于同一表上的另一个查询的过滤器记录

I have a table with following columns

我有一个包含以下列的表

TrialId
Points
TrialDateTime
UserId

This represents trials done by a user & the points he scored in the trial. A particular user may do 10 trials so he may have 10 records in the table.

这表示用户进行的试验以及他在试验中得分。特定用户可以进行10次试验,因此他可能在表中有10条记录。

I want to get all records where points are greated than 50, but only if that trial represents the 4th or more trial of that particular user. The number of a particular trial is not represented in the table. However, if a userid has 5 trials, then the datetime field can be used to figure out which is the first/second etc.

我想得到所有记录,其中积分大于50,但只有当该试验代表该特定用户的第四次或更多次试验时。表中未列出特定试验的编号。但是,如果用户标识有5个试验,则可以使用日期时间字段来确定哪个是第一个/第二个等。

So basically I want to get all trials where the score is greater 50 & the same userid has 3 other other trials in the table which has a lesser datetime.

所以基本上我想得到所有得分大于50的试验,同样的用户ID在表中有3个其他试验,其日期时间较短。

Is there a way to do this via SQL?

有没有办法通过SQL做到这一点?

I know that I can get

我知道我能得到

select * from tbl where points > 50;

will get me all records where points > 50, but how do I filter it further?

将获得所有记录,其中点> 50,但我如何进一步过滤?

If I had the datetime of a particular record in a variable called X, I could do

如果我在一个名为X的变量中有特定记录的日期时间,我可以这样做

select count(TrialId) from tbl where TrialDateTime > 'somedatetime' and userid = 'someuid'.

to check if there are more than 3 older records for the same userid. But is there a way to combine both of this using SQL? What other options do I have?

检查同一用户标识是否有超过3个旧记录。但有没有办法使用SQL结合这两者?我还有其他选择吗?

Sample records

1. T1, 20, 2013-05-09 14:10:27.000, U1
2. T2, 40, 2013-05-09 14:20:27.000, U1
3. T3, 45, 2013-05-09 14:30:27.000, U1
4. T4, 60, 2013-05-09 14:40:27.000, U1
5. T5, 20, 2013-05-09 12:11:27.000, U2
6. T6, 30, 2013-05-09 12:12:27.000, U2
7. T7, 60, 2013-05-09 12:13:27.000, U2
8. T8, 40, 2013-05-09 12:54:27.000, U2

Here Record 4 will be selected. However, Record 7 will not be selected though both have scores above 50.

这里将选择记录4。但是,虽然两者的分数都高于50,但不会选择Record 7。

Using SQL Server 2008 R2.

使用SQL Server 2008 R2。

2 个解决方案

#1

Maybe something like this:

也许是这样的:

;WITH CTE
AS
(
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY t.UserId 
                          ORDER BY t.TrialDateTime) AS NbrOfTrails,
        t.*
    FROM 
        tbl AS t
)
SELECT
    *
FROM
    CTE
WHERE
    CTE.NbrOfTrails > 3
    AND CTE.Points > 50

References:

Using Common Table Expressions

使用公用表表达式

ROW_NUMBER (Transact-SQL)

#2

I belive you can do the following:

我相信你可以做到以下几点:

SELECT
        *
    FROM
        tbl as outertable
    WHERE
        points > 50 AND
        (
            SELECT
                    COUNT(*) AS Number
                FROM
                    tbl as innertable
                WHERE
                    innertable.UserID = outertable.UserID AND
                    innertable.TrialDateTime < outertable.TrialDateTime
        ) > 2

#1