rank, get the latest version

2010-12-21  来源:本站原创  分类:Database  人气:83 

There are 3 fields,

One is the project id,

2 is the version number version 1.0,2.0. . .

3 is the amount of money;

Now divided into 3 columns, shows the latest three versions were taken the money, if not the latest version. Empty

Showing results are as follows;

- 1 group ranking, in descending order, (get the latest version is 1);
SELECT RANK () OVER (PARTITION BY ID ORDER BY VERSION DESC) RAN,
ID,
VERSION,
MONEY
FROM MY_RANK

- 2 to take the largest of the three versions
SELECT * FROM (
SELECT RANK () OVER (PARTITION BY ID ORDER BY VERSION DESC) RAN,
ID,
VERSION,
MONEY
FROM MY_RANK) WHERE RAN <4

- 3 groups
SELECT ID, DECODE (RAN, 1, MONEY), DECODE (RAN, 2, MONEY), DECODE (RAN, 3, MONEY) FROM (
SELECT RANK () OVER (PARTITION BY ID ORDER BY VERSION DESC) RAN,
ID,
VERSION,
MONEY
FROM MY_RANK) WHERE RAN <4

- 4 Sum
SELECT ID, SUM (T1) MONEY1, SUM (T2) MONEY2, SUM (T3) MONEY3 FROM (
SELECT ID, DECODE (RAN, 1, MONEY) T1, DECODE (RAN, 2, MONEY) T2, DECODE (RAN, 3, MONEY) T3 FROM (
SELECT RANK () OVER (PARTITION BY ID ORDER BY VERSION DESC) RAN,
ID,
VERSION,
MONEY
FROM MY_RANK) WHERE RAN <4)
GROUP BY ID

Order to the last sentence of the above is the result of the

相关文章