mysql - 双选缩小结果

Question

我想从表格中选择观看次数、增加量和增加百分比，但只显示唯一 ID。我可以获得我想要的信息，但它超出了我的需要，无法缩小结果范围。

我有以下数据:

+------------+------------+-------+-------+
| datum      | youtube_id | views | likes |
+------------+------------+-------+-------+
| 2012-10-07 |     123456 |   100 |   100 |
| 2012-10-07 |      98765 |   200 |   200 |
| 2012-10-08 |     123456 |   150 |   150 |
| 2012-10-08 |      98765 |   300 |   300 |
| 2012-10-09 |     123456 |   300 |   300 |
| 2012-10-09 |      98765 |   500 |   500 |
+------------+------------+-------+-------+

还有下面的sql语句:

SELECT 
id, 
startcount, 
endcount, 
(endcount - startcount)increasing, 
((endcount - startcount) / ( startcount ) *100)percentChange
FROM (SELECT youtube_id AS id, views AS startcount
    FROM charts
    WHERE datum =  '2012-10-08')startRange,
(SELECT views AS endcount
FROM charts
WHERE datum =  '2012-10-09')endRange

这给了我以下结果:

+--------+------------+----------+------------+---------------+
| id     | startcount | endcount | increasing | percentChange |
+--------+------------+----------+------------+---------------+
| 123456 |        150 |      300 |        150 |      100.0000 |
|  98765 |        300 |      300 |          0 |        0.0000 |
| 123456 |        150 |      500 |        350 |      233.3333 |
|  98765 |        300 |      500 |        200 |       66.6667 |
+--------+------------+----------+------------+---------------+

预期的结果是:

+--------+------------+----------+------------+---------------+
| id     | startcount | endcount | increasing | percentChange |
+--------+------------+----------+------------+---------------+
| 123456 |        150 |      300 |        150 |      100.0000 |
|  98765 |        300 |      500 |        200 |       66.6667 |
+--------+------------+----------+------------+---------------+

我一直在查看 group by 或 join，但无法弄清楚这一点，已经重做了几天，但我自己遇到了循环。

如果有人能指出正确的方向或帮助我，那就太好了!

Answer 1

SQL Fiddle

SELECT 
    c1.youtube_id id, 
    c1.views startcount, 
    c2.views endcount, 
    c2.views - c1.views increasing, 
    (c2.views - c1.views) * 100 / c1.views percentChange
FROM 
    charts c1
    inner join
    charts c2 on c1.youtube_id = c2.youtube_id
WHERE 
    c1.datum = '2012-10-08' and c2.datum =  '2012-10-09'

编辑:改变递增