如何在一个日期内用关键字查询数据?

Here is the problematic SQL query and the results that it yields: 如何在一个日期内用关键字查询数据?

这里是有问题的SQL查询和它产生的结果:

What I need, however, is to have the data formatted in the following way:

然而，我需要的是将数据格式化如下:

Emp No   Sign In                          Sign out
022195   2013-01-29 09:18:00              2013-01-29 19:18:00  
043770   2013-01-29 10:07:00              2013-01-29 17:07:00

3 个解决方案

#1

You can use the date() function to aggregate the day in MySQL. The trick is to use it in the group by clause.

您可以使用date()函数在MySQL中聚合这一天。诀窍是在group by子句中使用它。

select name, 
   min(date) as Sign_In,
   max(date) as Sign_Out,
from text_based_attendance 
group by date(date), name
order by name

This will give a result grouping the employees by their names and the dates that they worked on, showing only the sing in/out times.

这将会根据员工的名字和他们工作的日期对员工进行分组，只显示出/输出的次数。

Here is my SQLFiddle: http://sqlfiddle.com/#!2/740e2/1

这里是我的SQLFiddle: http://sqlfiddle.com/#!2/740e2/1。

In Orace, you would do the same with the EXTRACT function:

在Orace中，提取函数也会这样做:

   select name, 
   min(mdate) as Sign_In,
   max(mdate) as Sign_Out
   from text_based_attendance
   group by EXTRACT(day from mdate),name

http://sqlfiddle.com/#!4/96b6c/1

http://sqlfiddle.com/ ! 4/96b6c / 1

For Postgres, it's more or less the same as in Oracle.

对于Postgres，它或多或少与Oracle相同。

http://www.postgresql.org/docs/7.4/static/functions-datetime.html

#2

you don't have to do any subqueries for that, you can do it with basic aggregate functions over the table and group by name

您不必为它做任何子查询，您可以通过表和组的名称来完成基本的聚合函数。

select
     t.name as EmpNo,
     min(t.date) as SignIn,
     max(t.date) as SignOut
from text_based_attendance as t
group by t.name
order by t.name

there're no need for alias (as t) in this query, but I think it's good practice to add alias to your query so you can easily modify and add joins in the future.

在这个查询中不需要别名(如t)，但是我认为在查询中添加别名是很好的做法，这样您就可以轻松地修改和添加将来的连接。

For PostgreSQL, if you want to group by date and get min and max for each date, you can do:

对于PostgreSQL，如果您想按日期分组，并在每次约会时获得最小值和最大值，您可以这样做:

select
     t.name as EmpNo,
     t::date as Date,
     min(t.date) as SignIn,
     max(t.date) as SignOut
from text_based_attendance as t
group by t.name, t::date
order by t.name

#3

If I've got it right:

如果我说对了:

select name, 
       min(date) as Sign_In,
       max(date) as Sign_Out
from text_based_attendance 
group by name

#1