h********r
发帖数: 38 | 1 求简单和高效的Query:
Input table:
ID Number
1 2
2 1
3 6
4 3
5 5
Input TotalNumber: 10
How to find the latest ID with number's sum 10
Step1: Starting from ID 5 find number 5, 10-5=5
Step2: From ID 4 find number 3, 5-3=2
Step3: From ID 3 find number 6, since 6 >2 so no more ID searching.
Output table should be:
ID Number
3 2
4 3
5 5
----------------
More complex case
Input table:
Category ID Number
1 1 2
1 2 1
1 3 6
1 4 3
1 5 5
2 1 2
2 2 6
2 3 2
Input TotalNumber for category 1 is 10, for category 2 is 5
Output table should be:
Category ID Number
1 3 2
1 4 3
1 5 5
2 2 3
2 3 2 | i*****w
发帖数: 75 | 2 不知题目有何实际应用意义,全当做数学题了
BEIJING说得对,做题要有包子,不然大家都没有积极性.所以,有包子就公布答案. | B*****g
发帖数: 34098 | 3 http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions
ref:
http://www.mitbbs.com/article/Database/31179909_3.html
【在 h********r 的大作中提到】
: 求简单和高效的Query:
: Input table:
: ID Number
: 1 2
: 2 1
: 3 6
: 4 3
: 5 5
: Input TotalNumber: 10
: How to find the latest ID with number's sum 10
| B*****g
发帖数: 34098 | 4 spt
【在 i*****w 的大作中提到】
: 不知题目有何实际应用意义,全当做数学题了
: BEIJING说得对,做题要有包子,不然大家都没有积极性.所以,有包子就公布答案.
| l******b
发帖数: 39 | 5
俺来班门弄斧一下, 看这样行不行?
为简化起见, 根据OP的要求, 先建两张表,一张放数据, 一张放参数.
CREATE TABLE t7(
CATEGORY INT,
ID INT,
num int
);
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,1,2) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,2,1) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,3,6) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,4,3) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(1,5,5) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(2,1,2) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(2,2,6) ;
INSERT INTO t7(CATEGORY, ID, num) VALUES(2,3,2) ;
SELECT * FROM t7 ;
-----------------------------------------------------
CATEGORY ID NUM
1 1 2
1 2 1
1 3 6
1 4 3
1 5 5
2 1 2
2 2 6
2 3 2
CREATE TABLE t8(
CATEGORY INT,
maxNum INT
);
INSERT INTO t8(CATEGORY, maxNum) VALUES(1,10);
INSERT INTO t8(CATEGORY, maxNum) VALUES(2,5);
SELECT * FROM t8 ;
------------------------------------------------------
CATEGORY MAXNUM
1 10
2 5
WITH
C1 AS(
SELECT CATEGORY, ID, num,
sum(num) OVER (partition by category ORDER BY ID DESC
ROWS UNBOUNDED PRECEDING) AS sumN
FROM t7) ,
C2 AS(
SELECT C1.CATEGORY,C1.id, C1.num, C1.sumN, t8.maxNum
FROM t8, C1
WHERE t8.CATEGORY = C1.CATEGORY)
SELECT category, ID,
CASE WHEN sumN > maxNum
THEN maxNum - (SELECT MAX(sumN) FROM c2 inner2 where
inner2.category = outer.category and sumN<=maxNum)
ELSE num
END AS num
FROM C2 OUTER
WHERE sumN
(SELECT MIN(sumN) FROM c2 INNER WHERE
OUTER.CATEGORY=INNER.CATEGORY AND sumN>=maxNum )
ORDER BY category, id ;
-------------------------------------------------------------
CATEGORY ID NUM
1 3 2
1 4 3
1 5 5
2 2 3
2 3 2
-----------------------------------------------------------------
【在 h********r 的大作中提到】
: 求简单和高效的Query:
: Input table:
: ID Number
: 1 2
: 2 1
: 3 6
: 4 3
: 5 5
: Input TotalNumber: 10
: How to find the latest ID with number's sum 10
|
|
