在 SQL 中选择 2 列的不同组合 [英] selecting a distinct combination of 2 columns in SQL

问题描述

当我在表上进行多次连接后运行选择时，我有 2 列的输出，我想为返回的行集选择 col1 和 col2 的不同组合.

When i run a select after a number of joins on my table I have an output of 2 columns and I want to select a distinct combination of col1 and col2 for the rowset returned.

我运行的查询将是这样的:

the query that i run will be smthing like this:

select a.Col1,b.Col2 from a inner join b on b.Col4=a.Col3

现在输出有点像这样

Col1 Col2  
1   z  
2   z  
2   x  
2   y  
3   x  
3   x  
3   y  
4   a  
4   b  
5   b  
5   b  
6   c  
6   c  
6   d  

现在我希望输出应该如下

now I want the output should be something like follows

1  z  
2  y  
3  x  
4  a  
5  b  
6  d 

如果我随机选择第二列就可以了，因为我的查询输出就像一百万行，我真的不认为会有这样的情况，我会让 Col1 和 Col2 输出相同，即使是这种情况我可以编辑值..

its ok if I pick the second column randomly as my query output is like a million rows and I really dnt think there will be a case where I will get Col1 and Col2 output to be same even if that is the case I can edit the value..

你能帮我做同样的事情吗..我认为基本上col3需要是我猜的行号，然后我需要根据随机行号选择两个cols..我不知道我如何转换这个到 SQL

Can you please help me with the same.. I think basically the col3 needs to be a row number i guess and then i need to selct two cols bases on a random row number.. I dont know how do i transalte this to SQL

考虑 1a 1b 1c 1d 1e 2a 2b 2c 2d 2e 现在 group by 会给我所有这些结果，因为我想要 1a 和 2d 或 1a 和 2b.任何这样的组合.

consider the case 1a 1b 1c 1d 1e 2a 2b 2c 2d 2e now group by will give me all these results where as i want 1a and 2d or 1a and 2b. any such combination.

好的，让我解释一下我的期望:

OK let me explain what im expecting:

with rs as(
select a.Col1,b.Col2,rownumber() as rowNumber from a inner join b on b.Col4=a.Col3)
select rs.Col1,rs.Col2 from rs where rs.rowNumber=Round( Rand() *100)

现在我不知道如何让行号或随机数正常工作！！

now I am not sure how do i get the rownumber or the random working correctly!!

提前致谢.

推荐答案

如果你根本不关心返回什么 col2 值

If you simply don't care what col2 value is returned

select a.Col1,MAX(b.Col2) AS Col2
from a inner join b on b.Col4=a.Col3 
GROUP BY a.Col1

如果你确实想要一个随机值，你可以使用下面的方法.

If you do want a random value you could use the approach below.

 ;WITH T
     AS (SELECT a.Col1,
                b.Col2
                ROW_NUMBER() OVER (PARTITION BY a.Col1 ORDER BY (SELECT NEWID())
                ) AS RN
         FROM   a
                INNER JOIN b
                  ON b.Col4 = a.Col3)
SELECT Col1,
       Col2
FROM   T
WHERE  RN = 1  

或者使用 CLR 聚合函数.这种方法的优点是不需要按 partition, newid() 排序，示例实现如下.

Or alternatively use a CLR Aggregate function. This approach has the advantage that it eliminates the requirement to sort by partition, newid() an example implementation is below.

using System;
using System.Data.SqlTypes;
using System.IO;
using System.Security.Cryptography;
using Microsoft.SqlServer.Server;

[Serializable]
[SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize = 8000)]
public struct Random : IBinarySerialize
{
    private MaxSoFar _maxSoFar;

    public void Init()
    {
    }

    public void Accumulate(SqlString value)
    {
        int rnd = GetRandom();
        if (!_maxSoFar.Initialised || (rnd > _maxSoFar.Rand))
            _maxSoFar = new MaxSoFar(value, rnd) {Rand = rnd, Value = value};
    }

    public void Merge(Random group)
    {
        if (_maxSoFar.Rand > group._maxSoFar.Rand)
        {
            _maxSoFar = group._maxSoFar;
        }
    }

    private static int GetRandom()
    {
        var buffer = new byte[4];

        new RNGCryptoServiceProvider().GetBytes(buffer);
        return BitConverter.ToInt32(buffer, 0);
    }

    public SqlString Terminate()
    {
        return _maxSoFar.Value;
    }

    #region Nested type: MaxSoFar

    private struct MaxSoFar
    {
        private SqlString _value;

        public MaxSoFar(SqlString value, int rand) : this()
        {
            Value = value;
            Rand = rand;
            Initialised = true;
        }

        public SqlString Value
        {
            get { return _value; }
            set
            {
                _value = value;
                IsNull = value.IsNull;
            }
        }

        public int Rand { get; set; }

        public bool Initialised { get; set; }
        public bool IsNull { get; set; }
    }

    #endregion


    #region IBinarySerialize Members

    public void Read(BinaryReader r)
    {
        _maxSoFar.Rand = r.ReadInt32();
        _maxSoFar.Initialised = r.ReadBoolean();
        _maxSoFar.IsNull = r.ReadBoolean();

        if (_maxSoFar.Initialised && !_maxSoFar.IsNull)
            _maxSoFar.Value = r.ReadString();
    }

    public void Write(BinaryWriter w)
    {
        w.Write(_maxSoFar.Rand);
        w.Write(_maxSoFar.Initialised);
        w.Write(_maxSoFar.IsNull);

        if (!_maxSoFar.IsNull)
            w.Write(_maxSoFar.Value.Value);
    }

    #endregion
}

这篇关于在 SQL 中选择 2 列的不同组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！