mysql 表别名与已有表名重复_如何在MySql中使用同一表的别名查找同一日...

我有一个名为rjs_attendance的表,其中有以下四列

_________________________________________________

|attenedance_id | admin_id | note | created_date|

-------------------------------------------------

用户一天可以出席几次.奇数条目被认为是登录,偶数条目被认为是退出.

我需要的输出看起来像这样.

_______________________________________________________________________________

|admin_id | time_in | time_in_note | time_out | time_out_note | date |

-------------------------------------------------------------------------------

|1 |10:00 | none | 11:00 | none | 2015-12-24|

-------------------------------------------------------------------------------

|1 |11:30 |none |12:15 |none | 2015-12-24|

-------------------------------------------------------------------------------

我无法提取同一日期的所有记录,但可以记录同一日期的一条记录.

我运行的查询如下：

SELECT

`atd_in`.`admin_id` AS `admin_id`,

CAST(MIN(`atd_in`.`created_date`) AS TIME) AS `time_in`,

`atd_in`.`note` AS `time_in_note`,

CAST(MAX(`atd_out`.`created_date`) AS TIME) AS `time_out`,

`atd_out`.`note` AS `time_out_note`,

CAST(`atd_in`.`created_date` AS DATE) AS `date_on`

FROM

`zf2`.`rjs_attendance` `atd_in`

LEFT JOIN `zf2`.`rjs_attendance` `atd_out`

ON

`atd_in`.`admin_id` = `atd_out`.`admin_id`

AND CAST(`atd_in`.`created_date` AS DATE) = CAST(`atd_out`.`created_date` AS DATE)

AND `atd_in`.`attendance_id` <> `atd_out`.`attendance_id`

GROUP BY

CAST(`atd_in`.`created_date` AS DATE), `atd_in`.`admin_id`

任何帮助是极大的赞赏.