带有选择性标准的 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()
相关文章