问题描述
我的数据库在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()