A very interesting SQL statement question. We have a 'payment' table:

payment table:

year salary

2000 1000

2001 2000

2002 3000

2003 4000

The question is: Write a query on payment table so we get the result below:

Query Result:

year salary

2000 1000

2001 3000

2002 6000

2003 10000

We can find that the result's salary is the sum of the salary of the year and the salary of last year. For example:

In Query Result, 2001's salary is 3000 = Table payment's year 2001's salary 2000 + Table payment's year 2000's salary 1000

Answer 1:

select year, (select sum(b.salary) from payment b where b.year <= a.year) as salary from payment a;

To expalin this one, better imagin we have two tables payment a, and payment b

payment a:

Year payment

2000 1000

2001 2000

2002 3000

2003 4000

payment b:

Year payment

2000 1000

2001 2000

2002 3000

2003 4000

First, MySql tries to retrieve all the year data from table a. For the second column, MySql will select the rows from table b that b.year <= a.year, and then get the SUM of the payment of these rows. The process is like:

For a.2000: b.2000 <= a.2000; SUM(b.1000)

For a.2001: b.2000 <= a.2001; b.2001 <= a.2001; SUM(b.1000,b.2000)

For a.2002: b.2000, b.2001, b.2002 <= a.2002; SUM(b.1000,b.2000,b.3000)

...

Finally, we can get the result:

year salary

2000 1000

2001 3000

2002 6000

2003 10000

Answer 2:

Answer 1 using sub query is straight forward and easier for people to understand. But when it comes to SQL, sub query is mostly not the best option. We can always write the easier sub query first, and then after we fully understand how the data should be retrieved, we can change it to use JOIN. That is how to use JOIN to do it:

select a.year, sum(b.salary) salary from payment b join payment a on b.year <= a.year group by a.year;

Answer 3 (Here is the real interesting answer):

If we still remember the math we learned at primary school(Well, at least that is being taught in primary school in China), we can find that the salary in payment table is actually an arithmetic sequence (http://en.wikipedia.org/wiki/Arithmetic_series)! For arithmetic sequence, we have this formula: Sn = (A1 + An) * n / 2. So, we can use this query:

select year, (1000+salary)*salary/2000 from payment;

It works perfectly for this specific question.

## No comments:

Post a Comment