选择随机行并在达到特定总和/总数时停止 [英] Select random rows and stop when a specific sum/total is reached
本文介绍了选择随机行并在达到特定总和/总数时停止的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用 SQL Server 2012 并且我正在尝试执行以下操作:
I'm using SQL Server 2012 and I'm trying to do something like this:
SELECT SUM(MILES) from tblName WHERE
mDate > = '03/01/2012' and
mDate <= '03/31/2012'
-- and...
/*
now I want to add here do until the SUM of Miles
is equal to or greater then '3250' and get the
results rows randomly
*/
换句话说,我想从表中选择随机行,这些行具有指定的起始日期和截止日期,并在英里总和等于或超过以下数字时停止:3250
So in other words, I want to select random rows from a table that have a specified from and to date and stop when the sum of miles is at or over the number: 3250
推荐答案
由于您使用的是 SQL Server 2012,这里有一个更简单的方法,不需要循环.
Since you're using SQL Server 2012, here is a much easier approach that doesn't require looping.
DECLARE @tbl TABLE(mDate DATE, Miles INT)
INSERT @tbl VALUES
('20120201', 520), ('20120312', 620),
('20120313', 720), ('20120314', 560),
('20120315', 380), ('20120316', 990),
('20120317', 1020), ('20120412', 520);
;WITH x AS
(
SELECT
mDate,
Miles,
s = SUM(Miles) OVER
(
ORDER BY NEWID() ROWS UNBOUNDED PRECEDING
)
FROM @tbl
WHERE mDate >= '20120301'
AND mDate < '20120401'
)
SELECT
mDate,
Miles,
s
FROM x
WHERE s <= 3250
ORDER BY s;
SQLfiddle 演示 - 多次点击运行 SQL"以查看随机结果.
SQLfiddle demo - hit "Run SQL" multiple times to see random results.
这篇关于选择随机行并在达到特定总和/总数时停止的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文