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.
