sql - 每行随机排列列值

Question

我正在使用 C#.NET 开发多项选择题考试生成器。每次提交报告时，都会在数据库中随机选择问题，并随机洗牌。我可以做随机问题部分，但我不能做随机选择。

我有一个表格，其中一行是这样的:

-----------------------------------------------------
|question|answer|distractor1|distractor2|distractor3|
|q1      |ansq1 |d1q1       |d2q1       |d2q1       |
-----------------------------------------------------

我想做的是打乱 answer、distractor1 和 distractor3 列的值。例如:

-----------------------------------------------------
|question|answer|distractor1|distractor2|distractor3|
|q1      |d3q1  |d2q1        |d1q1       |ansq1     |
-----------------------------------------------------

这可以使用 SQL 查询吗？

Answer 1

这是使用 Dynamic Sql 的一个技巧

WHILE 1 = 1
    BEGIN
        DECLARE @answer SYSNAME = (SELECT TOP 1 COLUMN_NAME
            FROM   INFORMATION_SCHEMA.columns
            WHERE  TABLE_NAME = 'yourtable' AND COLUMN_NAME NOT IN ( 'question', 'answer')
            ORDER  BY Newid())
        DECLARE @distractor1 SYSNAME = (SELECT TOP 1 COLUMN_NAME
            FROM   INFORMATION_SCHEMA.columns
            WHERE  TABLE_NAME = 'yourtable' AND COLUMN_NAME NOT IN ( 'question', @answer, 'distractor1' )
            ORDER  BY Newid())
        DECLARE @distractor2 SYSNAME = (SELECT TOP 1 COLUMN_NAME
            FROM   INFORMATION_SCHEMA.columns
            WHERE  TABLE_NAME = 'yourtable' AND COLUMN_NAME NOT IN ( 'question', @answer, 'distractor2', @distractor1 )
            ORDER  BY Newid())
        DECLARE @distractor3 SYSNAME = (SELECT TOP 1 COLUMN_NAME
            FROM   INFORMATION_SCHEMA.columns
            WHERE  TABLE_NAME = 'yourtable' AND COLUMN_NAME NOT IN ( 'question', @answer, 'distractor3', @distractor2, @distractor1 )
            ORDER  BY Newid())

        IF @distractor1 IS NOT NULL
            AND @distractor2 IS NOT NULL
            AND @distractor3 IS NOT NULL
            AND @answer IS NOT NULL
        BREAK
    END
--select @distractor1,@distractor2,@distractor3
exec( 'update yourtable set answer = '+@answer+', distractor1 = '+@distractor1+', distractor2 = '+@distractor2+', distractor3 = '+@distractor3)

select * from yourtable