我有一个名为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`
任何帮助是极大的赞赏.
解决方法:
尝试这个:
SELECT A.admin_id,
MAX(IF(A.ID % 2 = 1, CAST(A.created_date AS TIME), NULL)) AS time_in,
MAX(IF(A.ID % 2 = 1, note, NULL)) AS time_in_note,
MAX(IF(A.ID % 2 = 0, CAST(A.created_date AS TIME), NULL)) AS time_out,
MAX(IF(A.ID % 2 = 0, note, NULL)) AS time_out_note,
CAST(A.created_date AS DATE) AS date_on
FROM (SELECT IF(@adminId=@adminId:=A.admin_id, @id:=@id+1, @id:=1) AS ID,
A.admin_id, A.note, A.created_date
FROM zf2.rjs_attendance A, (SELECT @id:=1, @adminId:=0) AS B
ORDER BY A.admin_id, A.attendance_id
) AS A
GROUP BY A.admin_id, CAST(A.created_date AS DATE), CEILING(A.ID / 2);
标签:self-join,select,sql,mysql,group-by
来源: https://codeday.me/bug/20191027/1945311.html