php - 同一表中每个重复行减少为一行

Question

Table Name - Enrolments

 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +  unit_id  +  student_number  +  unit_code  +  course_id  +  status_id  + last_modified_date  +
 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +   20102   +    SN0001        +  HP-306.12  +   BPL-12    +  WITHDRAWN  +   2/8/2016 15:08     +
 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +   20102   +    SN0001        +  HP-306.12  +   BMin-12   +  COMPLETED  +   2/8/2016 15:08     +
 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +   20110  +     SN0002        +  AS-105.11  +   BBS-12    +  WITHDRAWN  +   2/9/2016 12:01     +
 +-----------+------------------+-------------+-------------+-------------+----------------------+
 +   20110   +    SN0002        +  AS-105.11  +   BMin-13   +  WITHDRAWN  +   2/9/2016 15:13     +
 +-----------+------------------+-------------+-------------+-------------+----------------------+

我的数据如表注册中所示，有匹配的unit_id，student_number

我想在表上运行 sql 查询的 2 个条件:

a.我想删除“重复”行并仅保留不具有 status_id widthdrawn 的行。

b.但是，如果 2 个“重复”行都已撤回 status_id，而我只想保留其中一个。

最终结果应如下表屏幕截图所示

Table Name - Newly changed table

 +-----------+------------------+-------------+-------------+-------------+----------------------+
+  unit_id  +  student_number  +  unit_code  +  course_id  +  status_id  +  last_modified_date  +
+-----------+------------------+-------------+-------------+-------------+----------------------+
+   20102   +    SN0001        +  HP-306.12  +   BMin-12   +  COMPLETED  +   2/8/2016 15:08     +
+-----------+------------------+-------------+-------------+-------------+----------------------+
+   20110   +    SN0002        +  AS-105.11  +   BMin-13   +  WITHDRAWN  +   2/9/2016 15:13     +
+-----------+------------------+-------------+-------------+-------------+----------------------+

有人可以帮忙吗？

Answer 1

您的解决方案

也许是这样的

SELECT * FROM (SELECT * FROM table ORDER BY `status_id`) t1 GROUP BY `unit_id`, `student_number`;

请注意，我自己没有测试过这个查询，所以我不能保证它有效，如果不起作用请报告并让我知道错误^_^