两个表的MySQL

Question

我有两张这样的桌子

表PERSON

|NAME        |AGE       |
+------------+----------+
|mark        |20        |
|david       |18        |
|john        |35        |
|mary        |29        |
|sophie      |32        |

表LOG

|NAME        |ACTION      |TIME_STAMP            |
+------------+------------+----------------------+
|mark        |LOGIN       |2010-10-10 00:20:00   |
|mark        |LOGIN       |2010-10-10 00:25:00   |
|mark        |LOGIN       |2010-10-10 00:30:00   |
|mark        |LOGIN       |2010-10-10 00:35:00   |
|david       |LOGIN       |2010-10-10 00:30:00   |
|david       |LOGIN       |2010-10-10 00:35:00   |

想要这个

|NAME        |AGE       |TOTAL ACTIONS |TOTAL TIME CONECTED |
+------------+----------+--------------+--------------------+
|mark        |20        |4             |00:15:00            |
|david       |18        |2             |00:05:00            |
|john        |35        |0             |00:00:00            | Or null
|mary        |29        |0             |00:00:00            | or null 
|sophie      |32        |0             |00:00:00            | or null

实际上，我有这个SQL，但是它没有显示第一个表中还没有登录的人。

SELECT person.name, person.age, COUNT(DISTINCT log.time_stamp), SEC_TO_TIME(MAX(TIME_TO_SEC(time_stamp))- MIN(TIME_TO_SEC(time_stamp)))
FROM person, log
WHERE log.name = person.name
GROUP BY person.name;

Answer 1

试着使用：

SELECT person.name, person.age, COUNT(DISTINCT log.time_stamp), 
       SEC_TO_TIME(MAX(TIME_TO_SEC(time_stamp))- MIN(TIME_TO_SEC(time_stamp)))
       FROM person
       LEFT JOIN log ON person.name = log.name
       GROUP BY person.name;