如何在不删除mysql组的情况下获得所有记录 [英] How to get the all the record with out removing in Group in mysql
问题描述
我正在使用考勤管理系统.我必须准备一些报告打孔报告，其中包括每位员工的所有指纹记录.我想要显示日期和与该日期相关的所有时间的周转箱表.使用以下查询，我得到了这样的结果
I am working with attendance management system. I have to prepare some report punch report with include all the finger print record of the each employee. I want crate table which show date and all the time relevant to that date. Using following query I got result like that
SELECT * FROM `attendancedata` where `EnrolledID`= 23 GROUP BY
Date,EnrolledID,Time ORDER BY Date DESC,Time DESC
EnrolledID Date Time
23 2019-09-09 07:58:36
23 2019-09-07 17:25:00
23 2019-09-07 16:08:27
23 2019-09-07 07:32:39
23 2019-09-06 16:00:10
23 2019-09-06 07:48:51
23 2019-09-05 16:00:23
23 2019-09-05 07:40:55
23 2019-09-04 16:00:39
23 2019-09-04 07:45:42
23 2019-09-03 16:00:06
23 2019-09-03 07:43:32
23 2019-09-02 16:00:04
23 2019-09-02 07:56:25
我的预期结果是
Date P01 P02 P03 P04 P05 P06 P07
2019-09-02 07:56:25 16:00:04
2019-09-03 07:43:32 16:00:06
2019-09-04 07:45:42 16:00:39
2019-09-05 07:40:55 16:00:23
2019-09-06 07:48:51 16:00:10
2019-09-07 07:32:39 16:08:27 17:25:00
2019-09-08 07:58:36
我该如何解决这个问题? (打卡时间限制最多为7次) (使用查询或使用php)
how do i solve this problem? (punch time limit to maximum 7 time) (using query or using php)
推荐答案
我们的出勤率数据表完全相同，我进行了一些查询来检索最终结果，这些结果与您所需要的相似.请尝试以下一种方法:
We have the exact same attendance data table and I have made a few queries to retrieve the end result similar to what you need. Try this one below:
SELECT EnrolledID,DATE,
SUBSTRING_INDEX(TimeAttList,' ',1) AS 'P01',
CASE WHEN TotalCheckin >=2 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(TimeAttList,' ',2),' ',-1) END AS 'P02',
CASE WHEN TotalCheckin >=3 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(TimeAttList,' ',3),' ',-1) END AS 'P03',
CASE WHEN TotalCheckin >=4 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(TimeAttList,' ',4),' ',-1) END AS 'P04',
CASE WHEN TotalCheckin >=5 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(TimeAttList,' ',5),' ',-1) END AS 'P05',
CASE WHEN TotalCheckin >=6 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(TimeAttList,' ',6),' ',-1) END AS 'P06',
CASE WHEN TotalCheckin =7 THEN SUBSTRING_INDEX(SUBSTRING_INDEX(TimeAttList,' ',7),' ',-1) END AS 'P07'
FROM
(SELECT EnrolledID,DATE,GROUP_CONCAT(TIME ORDER BY TIME ASC SEPARATOR ' ') AS 'TimeAttList',COUNT(*) AS 'TotalCheckin'
FROM `attendancedata`
WHERE `EnrolledID`= 1
GROUP BY EnrolledID, DATE ORDER BY DATE ASC,TIME ASC) A;
我正在使用MySQL 4.1.
我所做的是首先将GROUP_CONCAT与ORDER BY一起使用.这将始终列出以下内容:
I'm using MySQL 4.1.
What I did is first I use GROUP_CONCAT with ORDER BY inside it. This will list all the time as following:
+------------+------------+-------------------------------------------------------+--------------+
| EnrolledID | Date | TimeAttList | TotalCheckin |
+------------+------------+-------------------------------------------------------+--------------+
| 1 | 2019-06-24 | 08:53:01 12:02:17 12:35:33 18:04:57 | 4 |
| 1 | 2019-06-25 | 09:01:13 12:00:13 12:59:23 18:17:32 | 4 |
| 1 | 2019-06-26 | 08:54:10 12:03:32 13:00:37 18:03:56 | 4 |
| 1 | 2019-06-27 | 08:53:46 12:01:49 12:56:30 18:17:31 | 4 |
| 1 | 2019-06-28 | 08:59:31 11:03:31 11:58:59 17:03:10 17:58:31 21:05:58 | 6 |
| 1 | 2019-06-29 | 08:58:50 11:01:54 11:59:51 17:05:01 18:01:05 21:08:48 | 6 |
| 1 | 2019-06-30 | 09:01:13 11:00:49 12:00:03 17:04:42 18:00:17 20:14:28 | 6 |
| 1 | 2019-07-01 | 10:53:00 15:04:06 16:01:18 20:00:21 | 4 |
+------------+------------+-------------------------------------------------------+--------------+
我使基础成为子查询，然后使用SUBSTRING_INDEX分隔GROUP_CONCAT结果:
I make the base to become sub-query then I use SUBSTRING_INDEX to separate the GROUP_CONCAT results:
+------------+------------+----------+----------+----------+----------+----------+----------+-----+
| EnrolledID | DATE | P01 | P02 | P03 | P04 | P05 | P06 | P07 |
+------------+------------+----------+----------+----------+----------+----------+----------+-----+
| 1 | 2019-06-24 | 08:53:01 | 12:02:17 | 12:35:33 | 18:04:57 | | | |
| 1 | 2019-06-25 | 09:01:13 | 12:00:13 | 12:59:23 | 18:17:32 | | | |
| 1 | 2019-06-26 | 08:54:10 | 12:03:32 | 13:00:37 | 18:03:56 | | | |
| 1 | 2019-06-27 | 08:53:46 | 12:01:49 | 12:56:30 | 18:17:31 | | | |
| 1 | 2019-06-28 | 08:59:31 | 11:03:31 | 11:58:59 | 17:03:10 | 17:58:31 | 21:05:58 | |
| 1 | 2019-06-29 | 08:58:50 | 11:01:54 | 11:59:51 | 17:05:01 | 18:01:05 | 21:08:48 | |
| 1 | 2019-06-30 | 09:01:13 | 11:00:49 | 12:00:03 | 17:04:42 | 18:00:17 | 20:14:28 | |
| 1 | 2019-07-01 | 10:53:00 | 15:04:06 | 16:01:18 | 20:00:21 | | | |
+------------+------------+----------+----------+----------+----------+----------+----------+-----+
这篇关于如何在不删除mysql组的情况下获得所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
