从数据库中获取随机记录以进行测验游戏 [英] Getting Random Records From Database For Quiz Game

问题描述

我想要您的帮助来创建查询。我是软件事业的新手，我正在尝试为Windows Mobile应用程序开发KBC Type Quiz游戏。对于我想要尝试从数据库中获取随机记录，如果在该记录之后一次出现一条记录将不再出现。我使用此查询SELECT TOP 1 * FROM Quiz ORDER BY NEWID（）但记录重复。我的表结构如下：



ColumnName DataType



Id int

Que varchar（150）

Ans1 varchar（100）

Ans2 varchar（100）

Ans3 varchar（100）

TrueAns varchar（100）



http：// i.stack.imgur.com/0kKPw.png



我尝试创建一个商店程序，也在下面给出

I want help from you to create a query. I am new in software career and i am trying to developing a KBC Type Quiz Game for Windows Mobile Application. For That i want try to fetch random records from database and if one record is appear at one time after that record will not appear again. I use This query "SELECT TOP 1 * FROM Quiz ORDER BY NEWID()" but records are repeat. My Table structure is given below.

ColumnName DataType

Id int
Que varchar(150)
Ans1 varchar(100)
Ans2 varchar(100)
Ans3 varchar(100)
TrueAns varchar(100)

http://i.stack.imgur.com/0kKPw.png

I try to create a Store Procedure also which is given below

DECLARE @counter int, @randno int, @uBound int, @lBound int

            SELECT @uBound = Max(Id) FROM Quiz
            SELECT @lBound = Min(Id) FROM Quiz

            SELECT @randno = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
            SET @Counter = 0

            WHILE @counter = 0
            BEGIN
                IF EXISTS(SELECT Id FROM Quiz WHERE Id = @randno)
                BEGIN
                    SET NOCOUNT OFF
                    SELECT * FROM Quiz WHERE Id = @randno
                    SET @counter = 1
                END
                ELSE
                BEGIN
                    SELECT @randno = Round(((@uBound - @lBound -1 ) * Rand() + @lBound), 0)
                END
            END

但我可以不会成功。我的表包含此字段Que，Ans1，Ans2，Ans3，TrueAns。请帮我解决这个问题。我还要创建一个返回所有记录的网络服务

but i can't get success. My Table contain this fields Que, Ans1, Ans2, Ans3, TrueAns. Please Help me for this problem. And i want to also create a web-service which return all the records

推荐答案

看看这个msdn文章

http://msdn.microsoft.com/en-us/library/cc441928.aspx [ ^ ]

也试试这个。 .store在一个变量中检索id并在where子句中附加for not in子句...我想说下面的代码...希望它会是

check out this msdn article
http://msdn.microsoft.com/en-us/library/cc441928.aspx[^]
also try this ..store retrieve id in one variable and append in where clause for not in clause ...i want say something like below code...hope it wil

DECLARE @counter int, @randno int, @uBound int, @lBound int ,@notInId varchar(100)

SELECT @uBound = Max(Id) FROM Quiz
SELECT @lBound = Min(Id) FROM Quiz
SET @notInId=""
SELECT @randno = SELECT TOP 1 id, NewID() as Random FROM Quiz where id notin @notInId ORDER BY Random
SET @notInId=notInId+"'"+@randno+"'"
SET @Counter = 0

        WHILE @counter = 0
           BEGIN
            IF EXISTS(SELECT Id FROM Quiz WHERE Id = @randno)
                BEGIN
                    SET NOCOUNT OFF
                    SELECT * FROM Quiz WHERE Id = @randno
                    SET @counter = 1
                END
                ELSE
                BEGIN
                    SELECT @randno = SELECT TOP 1 id, NewID() as Random FROM Quiz where id notin @notInId ORDER BY Random
            SET @notInId=notInId+"'"+@randno+"'"
                END
            END

如果答案字段在同一个表中，则需要更改SELECT语句：

If the field for answer is in the same table, you need to change SELECT statement:

SELECT @counter=COALESCE(ID,0)
FROM Quiz
WHERE Id = @randno AND HasBeenAlreadyAnswered = False

你应该随机化，直到 @ counter = 0 。



伪代码：

1）生成随机数（设置 @random ）

2）检查 ID = @random 的问题是否已经回答

a）如果是，转到1）

b）如果没有，退出while循环并返回记录





以下SP应该适合你：

You should randomize untill @counter=0.

In pseudo code:
1) generate random number (set @random)
2) check if question with ID = @random has been already answered
a) if yes, goto 1)
b) if not, exit while loop and return record


Below SP should works for you:

DECLARE @id INT
DECLARE @random INT

SET @id =0
SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
WHILE (SELECT COALESCE(ID,0) FROM Quiz WHERE ID = @id AND HasBeenAlreadyAnswered=True)=0
BEGIN
    SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
    SELECT @id = ID FROM Quiz WHERE ID = @random
END

--finally
SELECT *
FROM Quiz
WHERE ID = @id AND HasBeenAlreadyAnswered=False

这就是全部;）



更多关于： WHILE [ ^ ]







解释后，我看到你的数据库设计很糟糕。如果你不存储关于用户答案的​​信息，就不可能生成随机问题......这对于消除随机算法中的重复是必要的。

参见示例：

创建测验（适合儿童） [ ^ ]

在线测验 [ ^ ]

测验 - C＃和.NET秘密 [ ^ ]

C＃ASP NET中的在线考试 [ ^ ]



此时解决方案听起来像：你需要重新设计数据库。

That's all ;)

More about: WHILE[^]



After your explanation, i see that your database design is bad. It's not possible to generate random question, if you do not store information about user answers... This is necessary to eliminate duplicates inside "random algorithm".
See examples:
Create a Quiz (for kids)[^]
Online Quiz[^]
Quiz - C# and .NET Secrets[^]
Online Exam in C# ASP NET[^]

At this moment solution sounds like: You need to redesign database.

DECLARE @id INT

DECLARE @random INT



SET @id = 0

SET @random = Round（（（@ uBound - @lBound）* Rand（）+ @lBound），0）

WHILE（SELECT COALESCE（ID，0）FROM Quiz WHERE ID = @id AND HasBeenAlreadyAnswered = True）= 0

BEGIN

SET @random = Round（（（@ uBound - @lBound）* Rand（）+ @lBound），0）

SELECT @id = ID FROM Quiz WHERE ID = @random

END



- 最终

SELECT *

来自测验

WHERE ID = @id AND HasBeenAlreadyAnswered = False

DECLARE @id INT
DECLARE @random INT

SET @id =0
SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
WHILE (SELECT COALESCE(ID,0) FROM Quiz WHERE ID = @id AND HasBeenAlreadyAnswered=True)=0
BEGIN
SET @random = Round(((@uBound - @lBound) * Rand() + @lBound), 0)
SELECT @id = ID FROM Quiz WHERE ID = @random
END

--finally
SELECT *
FROM Quiz
WHERE ID = @id AND HasBeenAlreadyAnswered=False

这篇关于从数据库中获取随机记录以进行测验游戏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！