时间戳

首页 > 解决方案 > SQL - 对重复行进行分组和计数

mysql - SQL - 对重复行进行分组和计数

问题描述

我不知道如何在 mysql 上对重复行进行分组和计数

以下是我从查询中得到的结果

       ssn              +       checktime           +   nama            
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++     
'196702031989031001'    +   '2018-08-03 07:33:02'   +   'FAJAR PERMADI'
'196810021993031001'    +   '2018-08-01 07:33:25'   +   'ANDRI ANGGORO, SH'
'196911052000031001'    +   '2018-08-03 07:47:22'   +   'SEMI TEDDY RORY, SS'
'196912221994032001'    +   '2018-08-01 08:03:59'   +   'AI SALATUN'
'196912221994032001'    +   '2018-08-02 09:34:11'   +   'AI SALATUN'
'196912221994032001'    +   '2018-08-03 07:33:18'   +   'AI SALATUN'
'197012051993031001'    +   '2018-08-01 07:58:47'   +   'AHMAD SODIKIN, SH'
'197012192001121001'    +   '2018-08-01 09:54:21'   +   'JUARA PAHALA MARBUN, ST'
'197012192001121001'    +   '2018-08-02 09:39:41'   +   'JUARA PAHALA MARBUN, ST'

以下是我的查询

SELECT a.ssn, a.checktime, b.nama
FROM hki_kepegawaian.fo_absensi a
left join hki_kepegawaian.fo_pegawai b on a.ssn = b.nip  
where (substring(cast(checktime as DATE), 6, 2) = '08') 
and (cast(a.checktime as TIME)) >= '07:30:00' and (cast(a.checktime as 
TIME)) <= '10:00:00'
and (substring(golongan, 1, 2)) NOT IN ('IV')
group by ssn, cast(a.checktime as date)

以下是我预期的结果

        ssn             +       checktime           +   nama                        +   total
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++                                                                                                   
'196702031989031001'    +   '2018-08-03 07:33:02'   +   'FAJAR PERMADI'             +   1
'196810021993031001'    +   '2018-08-01 07:33:25'   +   'ANDRI ANGGORO, SH'         +   1
'196911052000031001'    +   '2018-08-03 07:47:22'   +   'SEMI TEDDY RORY, SS'       +   1
'196912221994032001'    +   '2018-08-01 08:03:59'   +   'AI SALATUN'                +   3
'197012051993031001'    +   '2018-08-01 07:58:47'   +   'AHMAD SODIKIN, SH'         +   1
'197012192001121001'    +   '2018-08-01 09:54:21'   +   'JUARA PAHALA MARBUN, ST'   +   2

标签： mysqlsql

解决方案

我同意蒂姆的观点，你似乎想最早的时间。group by在这种情况下，这是通过 a 完成的。

但是，我建议对查询进行一些其他修复：

  • 不要对日期/时间使用字符串操作。
  • 使用有意义的表别名，即表的缩写。
  • 将所有未聚合的列包含在GROUP BY.
  • 在适当的地方使用LIKE。

所以，我建议：

SELECT a.ssn, a.checktime, p.nama
FROM hki_kepegawaian.fo_absensi a LEFT JOIN
     hki_kepegawaian.fo_pegawai b 
     ON a.ssn = p.nip  
WHERE MONTH(checktime) = 8 AND
      CAST(a.checktime as TIME) >= '07:30:00' AND
      CAST(a.checktime as TIME)) <= '10:00:00' AND
      golongan NOT LIKE 'IV%'
GROUP BY a.ssn, p.nama;

推荐阅读