根据不同的值从列中选择随机值

问题描述：

我在表中有以下数据:-

I have following data in table :-

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

我需要一个返回以下输出的查询:

and i need a query which return following output :

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

即 item_no 应该是不同的并且具有随机一个速率值..

i.e item_no should be distinct and with randomly one rate value..

提前致谢

答

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) 上的索引会有所帮助.

Index on (item) include (rate) would help.

根据不同的值从列中选择随机值

相关推荐