我有一个表格,包括有关出席某些活动的数据.我在表中每次用户发出新的出席情况时都会出现数据,信息如下:
mysql> SELECT id_branch_channel, id_member, attendance, timestamp, id_member FROM view_event_attendance WHERE id_event = 782;
+-------------------+-----------+------------+------------+-----------+
| id_branch_channel | id_member | attendance | timestamp | id_member |
+-------------------+-----------+------------+------------+-----------+
| 1326 | 131327 | 459 | 1363208604 | 131327 |
| 1326 | 131327 | 123 | 1363208504 | 131327 |
| 1326 | 131327 | 1 | 1363208459 | 131327 |
| 1326 | 93086 | 0 | NULL | 93086 |
| 1326 | 93087 | 0 | NULL | 93087 |
| 1326 | 93088 | 0 | NULL | 93088 |
| 1326 | 93093 | 0 | NULL | 93093 |
| 1326 | 99113 | 0 | NULL | 99113 |
| 1326 | 99135 | 0 | NULL | 99135 |
| 1326 | 99199 | 0 | NULL | 99199 |
| 1326 | 99200 | 0 | NULL | 99200 |
| 1326 | 131324 | 0 | NULL | 131324 |
| 1326 | 85850 | 0 | NULL | 85850 |
| 1326 | 93085 | 0 | NULL | 93085 |
+-------------------+-----------+------------+------------+-----------+
14 rows in set (0.00 sec)
(这实际上是一个视图,因为一些字段为null).
我可以对id_member进行分组,所以每个成员只能得到一行(也就是用户发送的最后一次考勤).但是,当我这样做时,我收到了用户发出的第一次出席,而不是最后一次.
mysql> SELECT id_branch_channel, id_member, attendance, timestamp, id_member FROM view_event_attendance WHERE id_event = 782 GROUP BY id_event,id_member;
+-------------------+-----------+------------+------------+-----------+
| id_branch_channel | id_member | attendance | timestamp | id_member |
+-------------------+-----------+------------+------------+-----------+
| 1326 | 131327 | 1 | 1363208459 | 131327 |
| 1326 | 93086 | 0 | NULL | 93086 |
| 1326 | 131324 | 0 | NULL | 131324 |
| 1326 | 93087 | 0 | NULL | 93087 |
| 1326 | 93088 | 0 | NULL | 93088 |
| 1326 | 93093 | 0 | NULL | 93093 |
| 1326 | 99113 | 0 | NULL | 99113 |
| 1326 | 99135 | 0 | NULL | 99135 |
| 1326 | 85850 | 0 | NULL | 85850 |
| 1326 | 99199 | 0 | NULL | 99199 |
| 1326 | 93085 | 0 | NULL | 93085 |
| 1326 | 99200 | 0 | NULL | 99200 |
+-------------------+-----------+------------+------------+-----------+
12 rows in set (0.00 sec)
我已经尝试添加ORDER BY clausules,但是它们根本没有工作…任何想法?
提前致谢!
编辑:这是创建表的脚本
CREATE OR REPLACE VIEW view_event_attendance
AS
SELECT
tbl_event.id_event,
tbl_member_event.id_member,
tbl_event.id_branch_channel,
tbl_member_event_attendance.id_member_event_attendance,
IF(ISNULL(tbl_member_event_attendance.attendance), 0, tbl_member_event_attendance.attendance) AS attendance,
tbl_member_event_attendance.timestamp
FROM
tbl_event
INNER JOIN
tbl_member_event ON tbl_member_event.id_event = tbl_event.id_event
LEFT OUTER JOIN
tbl_member_event_attendance ON tbl_member_event_attendance.id_member_event = tbl_member_event.id_member_event
ORDER BY
tbl_member_event_attendance.timestamp DESC;
编辑2:
感谢MichaelBenjamin,但使用子查询时的问题是视图的大小:
mysql> DESCRIBE SELECT id_branch_channel, id_member, attendance, timestamp, id_member
-> FROM (select * from view_event_attendance order by timestamp desc) as whatever
-> WHERE id_event = 782
-> GROUP BY id_event,id_member;
+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 16755 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | tbl_member_event | index | id_event | id_event | 8 | NULL | 16346 | Using index; Using temporary; Using filesort |
| 2 | DERIVED | tbl_event | eq_ref | PRIMARY | PRIMARY | 4 | video_staging.tbl_member_event.id_event | 1 | |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using index |
+----+-------------+-----------------------------+--------+-----------------+-----------------+---------+------------------------------------------------+-------+----------------------------------------------+
4 rows in set (0.08 sec)
正如你可以看到,我的表中有很多行,所以因此我不想使用子查询…
编辑3:
但是添加WHERE到子查询看起来更好…
mysql> DESCRIBE SELECT id_branch_channel, id_member, attendance, timestamp, id_member
-> FROM (select * from view_event_attendance where id_event = 782 order by timestamp desc) as whatever
-> WHERE id_event = 782
-> GROUP BY id_event,id_member;
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 14 | Using where; Using temporary; Using filesort |
| 2 | DERIVED | tbl_event | const | PRIMARY | PRIMARY | 4 | | 1 | Using temporary; Using filesort |
| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 | Using index |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using index |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+----------------------------------------------+
4 rows in set (0.01 sec)
如果我找不到没有使用子查询的东西,我想我会选择这个作为答案…
编辑4
在答案中看到评论后,我决定选择另一个作为答案.这是两个查询的DESCRIBE,我认为很明显什么是最好的解决方案:
mysql> DESCRIBE SELECT
-> id_branch_channel,
-> id_member,
-> attendance,
-> timestamp,
-> id_member
-> FROM view_event_attendance AS t1
-> WHERE id_event = 782
-> AND timestamp = (SELECT MAX(timestamp)
-> FROM view_event_attendance AS t2
-> WHERE t1.id_member = t2.id_member
-> AND t1.id_event = t2.id_event
-> GROUP BY id_event, id_member)
-> OR timestamp IS NULL
-> GROUP BY id_event, id_member;
+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
| 1 | PRIMARY | tbl_event | index | PRIMARY | id_member_branch_channel | 4 | NULL | 208 | Using index; Using temporary; Using filesort |
| 1 | PRIMARY | tbl_member_event | ref | id_event | id_event | 4 | video_staging.tbl_event.id_event | 64 | Using index |
| 1 | PRIMARY | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | tbl_event | eq_ref | PRIMARY | PRIMARY | 4 | func | 1 | Using where; Using index; Using temporary; Using filesort |
| 2 | DEPENDENT SUBQUERY | tbl_member_event | eq_ref | id_event,id_member | id_event | 8 | video_staging.tbl_event.id_event,func | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using where; Using index |
+----+--------------------+-----------------------------+--------+--------------------+--------------------------+---------+------------------------------------------------+------+-----------------------------------------------------------+
6 rows in set (0.00 sec)
mysql> DESCRIBE SELECT *
-> FROM (SELECT id_branch_channel, id_member, attendance, timestamp, id_event
-> FROM view_event_attendance
-> WHERE id_event = 782
-> ORDER BY timestamp desc
-> ) as whatever
-> GROUP BY id_event,id_member;
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
| 1 | PRIMARY | | ALL | NULL | NULL | NULL | NULL | 14 | Using temporary; Using filesort |
| 2 | DERIVED | tbl_event | const | PRIMARY | PRIMARY | 4 | | 1 | Using temporary; Using filesort |
| 2 | DERIVED | tbl_member_event | ref | id_event | id_event | 4 | | 12 | Using index |
| 2 | DERIVED | tbl_member_event_attendance | ref | id_event_member | id_event_member | 4 | video_staging.tbl_member_event.id_member_event | 1 | Using index |
+----+-------------+-----------------------------+-------+-----------------+-----------------+---------+------------------------------------------------+------+---------------------------------+
4 rows in set (0.00 sec)