在sql表中随机播放一列值？ [英] Shuffle one column value in sql table?

问题描述

我的桌子看起来像这样：

My table is looking like this:

Id      name    disease     city        age     gender  zipcode
1	mani	flu	    chennai	34	*	6000**
2	arun	dyspepsia   chennai	28	*	6000**
3	deenu	flu	    vellore	23	*	6321**
4	naren	cancer	    vellore	24	*	6325**

我希望疾病列值在我给定的桌子上随机播放 .. ..如何在我桌上制作洗牌唯一的疾病......



实际上我需要我的洗牌桌如下：

i wanna disease column value shuffle randomly on my given table....how to make shuffle only disease on my table...

Actually i need my shuffle table like below :

Id      name    disease     city        age     gender  zipcode
1       mani    cancer      chennai     34        *     6000**
2       arun     flu        chennai     28        *     6000**
3       deenu    dyspepsia  vellore     23        *     6321**
4       naren    flu        vellore     24        *     6325**

推荐答案

试试这个.....

没有te： - 每次运行都会得到不同的疾病

Try this.....
Note:- Every run you get a different "disease"

WITH TCE_Shuffled AS
(
SELECT ROW_NUMBER() OVER (ORDER BY id ) AS ORIG_ROWNUM,
       ROW_NUMBER() OVER (ORDER BY NEWID()) AS NEW_ROWNUM,
      *
from dbo.myTableDis
)

select t1.id, t1.name, t2.disease, t1.city, t1.age, t1.gender, t1.zipcode
from TCE_Shuffled t1
join TCE_Shuffled t2
on t1.ORIG_ROWNUM = t2.NEW_ROWNUM

private DataSet GetDataset()
{
    string connstring = "server=.;"
                        + "Trusted_Connection=yes;"
                        + "database=test; "
                        + "connection timeout=30";
    DataSet dataset = new DataSet();
    string queryString = "WITH TCE_Shuffled AS "
                        + " ("
                        + "  SELECT ROW_NUMBER() OVER (ORDER BY id ) AS ORIG_ROWNUM,"
                        + "          ROW_NUMBER() OVER (ORDER BY NEWID()) AS NEW_ROWNUM,"
                        + "         *"
                        + "   from dbo.myTableDis"
                        + "   )"

                       + "    select t1.id, t1.name, t2.disease, t1.city, t1.age, t1.gender, t1.zipcode"
                       + "    from TCE_Shuffled t1"
                       + "    join TCE_Shuffled t2"
                       + "    on t1.ORIG_ROWNUM = t2.NEW_ROWNUM";

    using (SqlConnection connection = new SqlConnection(connstring))
    {
        SqlDataAdapter adapter = new SqlDataAdapter();
        adapter.SelectCommand = new SqlCommand(queryString, connection);
        adapter.Fill(dataset);
        return dataset;
    }
}

在SQL中你不能轻易做到这一点 - 它确实没有为此设置。

我建议您在C＃中执行此操作，方法是读取数据，在循环中手动对其进行混洗，然后将其写回 - 没有自动机制来执行此操作，每行都是一个独立的对象并且不知道是否存在任何其他行。

You can''t do that easily in SQL - it really isn''t set up for that.
I would suggest that you do it in C#, by reading the data, manually shuffling it in a loop, and writing it back - there is no automatic mechanism to do this, each row is an independent object and does not know of the existence of any other row.

我假设您正在尝试创建样本数据或屏蔽生产数据。如果是样本数据，我认为Red-Gate可能有一个生成样本数据的工具。



对于掩蔽，你将不得不写一个程序来做到这一点，正如格里夫所说，没有工具可以自动完成。

I presume you are trying to create sample data or mask production data. If it is sample data I think Red-Gate may have a tool for generating sample data.

For masking you are going to have to write a procedure to do that, as Griff said there is no tool to do it automatically.

这篇关于在sql表中随机播放一列值？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！