从SQL中获取时间戳的第一个日期

Question

我已经在我的Moodle中dbtable每sessionsessid和timestart。该表是这样的：

+----+--------+------------+ 
| id | sessid | timestart | 
+----+--------+------------+ 
| 1 |  3 | 1456819200 | 
| 2 |  3 | 1465887600 | 
| 3 |  3 | 1459839600 | 
| 4 |  2 | 1457940600 | 
| 5 |  2 | 1460529000 | 
+----+--------+------------+ 

如何获得来自SQL的timestampseverysession的firstdate？

Answer 1

你可以很容易使用：

select sessid,min(timestart) FROM mytable GROUP by sessid; 

而对于第二个问题，像这样：

SELECT 
    my.id, 
    my.sessid, 
    IF(my.timestart = m.timestart, 'yes', 'NO') AS First, 
    my.timestart 
FROM mytable my 
LEFT JOIN 
    (
    SELECT sessid,min(timestart) AS timestart FROM mytable GROUP BY sessid 
) AS m ON m.sessid = my.sessid; 

Answer 2

查询

select sessid, min(timestart) as timestart 
from your_table_name 
group by sessid; 

只是一个其他的角度如果你需要的话id。

select t.id, t.sessid, t.timestart from 
(
    select id, sessid, timestart, 
    (
     case sessid when @curA 
     then @curRow := @curRow + 1 
     else @curRow := 1 and @curA := sessid end 
    ) as rn 
    from your_table_name t, 
    (select @curRow := 0, @curA := '') r 
    order by sessid,id 
)t 
where t.rn = 1; 

Answer 3

试试这个。

SELECT 
    * 
FROM 
    tbl 
WHERE 
    (sessid, timestart) IN (
     SELECT tbl2.sessid, MIN(tbl2.timestart) 
     FROM tbl tbl2 
     WHERE tbl.sessid = tbl2.sessid 
    );