我有一个带列的表：USER_ID,TIMESTAMP和ACTION

每一行都告诉我哪个用户在某个时间戳上做了什么操作.

例：

> Alice于2014-06-12 16:37:46开始申请
> Alice于2014-06-12 17:48:55停止申请

我想要一个用户列表,它们在启动应用程序的第一行和它们关闭它的最后一行之间有时差.

这是我试图这样做的方式：

    SELECT USER_ID,DATEDIFF(
(SELECT timestamp FROM MOBILE_LOG WHERE ACTION="START_APP" AND USER_ID="Alice"  order by TIMESTAMP LIMIT 1),
(SELECT timestamp FROM MOBILE_LOG WHERE ACTION ="CLOSE_APP" AND USER_ID="Alice"  order by TIMESTAMP LIMIT 1)
) AS Duration FROM MOBILE_LOG AS t WHERE USER_ID="Alice";

我要求两个SELECT查询之间的DATEDIFF,但我得到一个Alice`s列表,其中-2为持续时间.

我在正确的轨道上吗？

解决方法:

我认为您应该按USER_ID对此表进行分组,并为每个用户找到“START_APP”的最小日期和“CLOSE_APP”的最大值.你也应该先在DATEDIFF使用CLOSE_APP时间然后再使用START_APP时间,这样你就会得到正值结果

SELECT USER_ID,
       DATEDIFF(MAX(CASE WHEN ACTION="CLOSE_APP" THEN timestamp END),
                MIN(CASE WHEN ACTION="START_APP" THEN timestamp END)
               ) AS Duration 
FROM MOBILE_LOG AS t 
GROUP BY USER_ID

SQLFiddle demo