基于不同的值从列中选择随机值(selecting random value from column

我有以下表中的数据： -

| item    | rate |
-------------------
| a       | 50   |
| a       | 12   |
| a       | 26   |
| b       | 12   |
| b       | 15   |
| b       | 45   |
| b       | 10   |
| c       | 5    |
| c       | 15   |

我需要查询其返回下面的输出：

| item no | rate |
------------------
| a       | 12 |  --from (26 , 12 , 50)
| b       | 45 | --from (12 ,15 , 45 , 10)
| c       | 5  | --from (5 , 15)

即ITEM_NO应该是不同的，并用一个随机速率值..

提前致谢

Answer 1:

WITH
CTE
AS
(
    SELECT DISTINCT
        item
    FROM T
)
SELECT
    CTE.item
    ,A.rate
FROM
    CTE
    CROSS APPLY
    (
        SELECT TOP(1) 
            T.rate
        FROM T
        WHERE T.item = CTE.item
        ORDER BY CRYPT_GEN_RANDOM(4)
    ) AS A
;

在指数(item) include (rate)会有所帮助。

Answer 2:

一种方法是使用行号窗口函数item的分区。 然后使用ORDER BY NEWID()在每个分区内，以生成随机排序，保持各第一行item分区任意。

SELECT t.item,
       t.rate
FROM
(
    SELECT item,
           rate,
           ROW_NUMBER() OVER(PARTITION BY item ORDER BY NEWID()) AS rn
    FROM yourTable
) t
WHERE t.rn = 1

Answer 3:

该处NEWID()被用于分区生成随机排序，
分区中，将保留每个项目的第一行。

with cte as
    (

        SELECT item,
               rate,
               ROW_NUMBER() OVER(PARTITION BY item ORDER BY NEWID()) AS rn
        FROM #Table1
        )
        select item,rate from cte where rn=1

Answer 4:

尝试一次此查询。

select * into #temp  from (
        SELECT 'a' item,50 rate 
        UNION ALL
        SELECT 'a',12  
        UNION ALL
        SELECT 'a', 26 
        UNION ALL
        SELECT 'b',12  
        UNION ALL
        SELECT 'b', 15 
        UNION ALL
        SELECT 'b',10 
        UNION ALL
        SELECT 'c',5  
        UNION ALL
        SELECT 'c',15 )as a

    select * from (
    select *,row_number()over(partition by item  ORDER BY newid() )as runm from #temp
    ) as a
    where runm=1

注意：上面的查询给出了每一次新的输出作为NEWID（）生成一个新的ID对于每个执行。

Answer 5:

; WITH tb( item, rate)AS(
    SELECT 'a',50 UNION
    SELECT 'a',12  UNION
    SELECT 'a', 26 UNION
    SELECT 'b',12  UNION
    SELECT 'b', 15 UNION
    SELECT 'b',10 UNION
    SELECT 'c',5  UNION
    SELECT 'c',15 
    ) 
    SELECT * FROM (
        SELECT *,ROW_NUMBER()OVER( PARTITION BY item ORDER BY NEWID())  AS rn FROM tb
    ) t WHERE t.rn=1

文章来源: selecting random value from column based on distinct values