选择随机行并在达到特定总和/总数时停止 [英] 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屋！