雜七雜八的小筆記!

使用到的SQL keyword: sum, timestampdiff, timestamp, left join, group by



Table1: mappingTable
+----------------+-----------------------+
| ComputerID | ComputerName |
+----------------+-----------------------+
|               1    | HP                         |
|               2    | IBM                       |
|               3    | Acer                      |
+----------------+-----------------------+



Table2: timeTable
+-------------+-------------------+----------------------------+-----------------------------+
|       ID       |    ComputerID  |      downTime              |             upTime              |
+-------------+-------------------+----------------------------+-----------------------------+
|              1 |                       1 | 2012-08-01 09:00:00 |  2012-08-05 17:00:00 |
|              2 |                       2 | 2010-07-07 10:00:00 |  2010-07-10 14:00:00 |
|              3 |                       2 | 2010-06-01 11:17:00 |  2010-06-05 17:37:00 |
|              4 |                       1 | 2011-10-08 12:00:00 |  2011-10-08 20:00:00 |
|              5 |                       3 | 2012-02-02 10:05:00 |  2012-02-02 18:06:00 |
|              6 |                       3 | 2010-01-08 14:00:00 |  2010-01-15 17:00:00 |
|              7 |                       1 | 2010-12-03 10:42:00 |  2010-12-03 16:14:00 |
|              8 |                       1 | 2012-04-02 12:32:00 |  2012-04-09 16:02:00 |
|              9 |                       2 | 2012-07-10 01:00:00 |  2012-07-11 08:16:00 |
|            10 |                       2 | 2012-01-09 12:08:00 |  2012-01-10 14:11:00 |
|            11 |                       3 | 2011-03-02 14:00:00 |  2011-03-03 12:00:00 |
|            12 |                       3 | 2012-08-19 10:00:00 |  2012-08-19 11:00:00 |
+-------------+-------------------+----------------------------+-----------------------------+



mysql>  select ComputerName, SUM(TIMESTAMPDIFF(second,`downTime`,`upTime`)) as totalTime from timeTable as A LEFT JOIN mappingTable as B ON A.ComputerID = B.ComputerID where downTime >= TIMESTAMP(20000101000000) and upTime <= TIMESTAMP(20140101000000) GROUP BY ComputerID;



Output: (以下為示意表，totalTime的值並非正確)
+--------------------+-------------------+
| ComputerName |      totalTime    |
+--------------------+-------------------+
|  HP                    |    192123575    |
|  IBM                  |       1682133     |
|  Acer                 |       9827673     |
+--------------------+-------------------+