SQL Server 随机求和

需求：随机取表中3条数据，要求3条数据中字段Price的和在范围600-700之间。

CREATE DATABASE db_Test
GO
USE db_Test
GO
CREATE TABLE tb_Test
(
[ Id ] int IDENTITY ( 1 , 1 ),
[ Price ] decimal ( 10 , 2 )
)
GO
INSERT INTO tb_Test VALUES ( 200 )
INSERT INTO tb_Test VALUES ( 300 )
INSERT INTO tb_Test VALUES ( 400 )
INSERT INTO tb_Test VALUES ( 500 )
INSERT INTO tb_Test VALUES ( 600 )
INSERT INTO tb_Test VALUES ( 700 )
INSERT INTO tb_Test VALUES ( 800 )
INSERT INTO tb_Test VALUES ( 80 )
INSERT INTO tb_Test VALUES ( 10 )
INSERT INTO tb_Test VALUES ( 20 )
INSERT INTO tb_Test VALUES ( 50 )
INSERT INTO tb_Test VALUES ( 90 )
INSERT INTO tb_Test VALUES ( 30 )
INSERT INTO tb_Test VALUES ( 40 )
INSERT INTO tb_Test VALUES ( 60 )
INSERT INTO tb_Test VALUES ( 70 )
INSERT INTO tb_Test VALUES ( 100 )
INSERT INTO tb_Test VALUES ( 330 )
INSERT INTO tb_Test VALUES ( 520 )
INSERT INTO tb_Test VALUES ( 180 )
GO

SELECT * FROM tb_Test WHERE EXISTS
(
SELECT * FROM
(
SELECT TOP 1 id1,id2,id3 FROM
(
SELECT a.Id AS id1,b.Id AS id2,c.Id AS id3
FROM tb_Test a,tb_Test b,tb_Test c
WHERE a.Id < b.Id and b.Id < c.Id
AND (a.Price + b.Price + c.Price) >= 600
AND (a.Price + b.Price + c.Price) <= 700
) t ORDER BY NEWID ()
) j
WHERE tb_Test.Id = j.id1 OR tb_Test.Id = j.id2 OR tb_Test.Id = j.id3
)

若有疑问或不正之处，欢迎提出指正和讨论。

转载于:https://www.cnblogs.com/iceknp/archive/2010/12/23/1914962.html