22,209
社区成员
发帖
与我相关
我的任务
分享
--2005
SELECT DISTINCT b.*
FROM [T_where] AS a
CROSS APPLY
(
SELECT TOP([抽取数量]) * FROM [T_topic] WHERE a.[题型ID]=[题型ID] AND a.[难易度ID]=[难易度ID]
ORDER BY [试题ID]-- NEWID() newid 是随机抽取
) AS b
;with cte as
(
select row_number() over(partition by a.题型,a.难易度ID order by newid()) rn,a.*,b.抽取数量 from T_topic a, T_where b
where a.题型=b.题型 and a.难易度ID=b.难易度ID
)
select * from cte
where rn<抽取数量
--> 测试数据:[T_topic]
IF OBJECT_ID('[T_topic]') IS NOT NULL DROP TABLE [T_topic]
GO
CREATE TABLE [T_topic]([试题ID] INT,[题型ID] INT,[难易度ID] VARCHAR(1))
INSERT [T_topic]
SELECT 1,1,'A' UNION ALL
SELECT 2,1,'A' UNION ALL
SELECT 3,1,'A' UNION ALL
SELECT 4,2,'A' UNION ALL
SELECT 5,2,'A' UNION ALL
SELECT 6,1,'B' UNION ALL
SELECT 7,1,'B' UNION ALL
SELECT 8,1,'B' UNION ALL
SELECT 9,1,'B'
--> 测试数据:[T_where]
IF OBJECT_ID('[T_where]') IS NOT NULL DROP TABLE [T_where]
GO
CREATE TABLE [T_where]([主键] INT,[题型ID] INT,[难易度ID] VARCHAR(1),[抽取数量] INT)
INSERT [T_where]
SELECT 1,1,'A',2 UNION ALL
SELECT 2,2,'A',1 UNION ALL
SELECT 3,1,'B',3
--------------开始查询--------------------------
SELECT a.*,b.[抽取数量] FROM [T_topic]a, [T_where] b
WHERE a.[题型ID]=b.[题型ID] AND a.[难易度ID]=b.[难易度ID]
AND (select count(*) FROM [T_topic] AS c WHERE a.[题型ID]=c.[题型ID] AND a.[难易度ID]=c.[难易度ID] AND c.[试题ID]>a.[试题ID])<b.[抽取数量]
----------------结果----------------------------
/*
试题ID 题型ID 难易度ID 抽取数量
----------- ----------- ----- -----------
3 1 A 2
2 1 A 2
5 2 A 1
9 1 B 3
8 1 B 3
7 1 B 3
(6 行受影响)
*/
select *
from t_topic
where exists(select 1 from t_where where t_topic.题型ID = t_where.题型ID)