带有选择性标准的 TSQL 随机选择

问题描述

我的数据库在category"表中有 5 个类别.我还有一个名为items"的表，其中每个项目都有唯一的 Id 和一个类别 Id FK.

My database has 5 categories in table "category". I also have a table called "items", where each item has unique Id and a category Id FK.

我需要从 1 个类别中随机选择 10 个项目.

I need to randomly select 10 items from 1 category.

如果只有 1 个类别，这不会有问题.但是表items"以非顺序存储类别id.

This would not be problem if there was only 1 category. But table "items" stores categories id in non-sequential order.

下面的随机选择语句有效并且能够在一个范围内生成随机 ID.但是如何生成 10 个属于同一类别的随机 ID?

The random select statement below works and is able to generate random IDs within a range. But how can I generate 10 random IDs that belong to the same category?

Declare @maxRandomValue tinyint = 100
    , @minRandomValue tinyint = 0;

Select Cast(((@maxRandomValue + 1) - @minRandomValue) 
    * Rand() + @minRandomValue As tinyint) As 'randomNumber';

定义:

Table Categories
ID INT
Desc Varchar(100)

Table Items
ID Int
CategoryID Int (fk)
Desc Varchar(100)

推荐答案

使用

• 过滤类别的 WHERE
• 新增随机行
• TOP 限制您最多 10 个项目

所以:

SELECT TOP 10
   *
FROM
   Items
WHERE
   CategoryID = @whatever
ORDER BY
   NEWID()