SQL语句面试题

一条SQL查询，一个表中按日期的累加数据
如表： tmp_pay_amount
pay_time amount
2013-11-1 10
2013-11-2 5
2013-11-3 4
2013-11-3 6
2013-11-4 8

查询结果：
pay_time amount
2013-11-1 10 (11-1的收入)
2013-11-2 15 (11-1 + 11-2 两天收入的和)
2013-11-3 25 (11-1 + 11-2 + 11-3 三天收入的和)
2013-11-4 33 (11-1 + 11-2 + 11-3 + 11-04 四天收入的和)

 

答案：

SELECT t1.pay_time, SUM(t2.AMOUNT_B) FROM
    (
    SELECT pay_time, SUM(amount) AS AMOUNT_A
    FROM `tmp_pay_amount`
    GROUP BY pay_time
    ) t1,
    (
    SELECT pay_time, SUM(amount) AS AMOUNT_B
    FROM `tmp_pay_amount`
    GROUP BY pay_time
    ) t2
WHERE t1.pay_time >= t2.pay_time
GROUP BY t1.pay_time ;