数据表A 结构如下：
L1 L2
3 100
5 200
2 100
4 400
8 300
10 100
我想查询 L1按升序 L2相加刚好大于1000的最少数据
结果如下
L1 L2
2 100
3 100
4 400
5 200
8 300
L2相加大于1000的最少行的所有数据
------解决方案--------------------
IF EXISTS(SELECT NAME FROM sys.objects AS o WHERE NAME = 'test')
DROP TABLE test
GO
CREATE TABLE test(l1 INT , l2 INT)
GO
INSERT INTO test (l1 , l2)
SELECT 3 ,100 UNION ALL
SELECT 5 , 200 UNION ALL
SELECT 2 , 100 UNION ALL
SELECT 4 , 400 UNION ALL
SELECT 8 , 300 UNION ALL
SELECT 10 , 100
GO
--執行查詢
WITH a AS (SELECT l1 , l2 , SUM(l2) OVER (ORDER BY l2 DESC , l1 ASC) AS val FROM test AS t)
SELECT * FROM a WHERE val <= (SELECT MIN(val) FROM a WHERE val > 1000 ) ORDER BY l1
/*
l1 l2 val
----------- ----------- -----------
2 100 1000
3 100 1100
4 400 400
5 200 900
8 300 700
(5 個資料列受到影響)
*/