班级出勤记录格式SQL查询 [英] Class attendance record format SQL Query

问题描述

可能重复:
MySQL动态交叉表

Possible Duplicate:
MySQL dynamic cross tab

我有student_record表

I have student_record table

-------------------------------------------------------------------
student_id | semester    | schoolyear| name  | section |
-------------------------------------------------------------------
1          | 1st Semester| 2011-2012 | john  | c21     |
2          | 1st Semester| 2011-2012 | eric  | c21     |
3          | 1st Semester| 2011-2012 | mark  | c21     |

和考勤表

-------------------------------------------------------------------
attendance_id | stud_id              | week       | 
-------------------------------------------------------------------
1             | 1                    | 02/04/2012 | 
2             | 2                    | 02/04/2012 | 
3             | 3                    | 02/04/2012 | 
4             | 1                    | 02/11/2012 | 
5             | 2                    | 02/11/2012 | 
6             | 1                    | 02/18/2012 | 
7             | 2                    | 02/18/2012 | 
8             | 3                    | 02/18/2012 | 

我想使用sql实现此输出.

And I want to achieve this output using sql.

-------------------------------------------------------------------
student_id | name    | 02/04/2012 | 02/11/2012  | 02/18/2012 |
-------------------------------------------------------------------
1          | john    | present    | present    | present     |
2          | erik    | present    | absent     | present     |
3          | mark    | present    | present    | present     |

这对我来说很模糊，有人可以帮忙吗? 我试过了，但是没用.

this is very vague to me, can anyone help? I tried this, but didn't work.

Select week,
[02/28/2012],
[02/29/2012]
From attendance
group by student_id

推荐答案

无论使用哪种数据库，都将尝试实现的概念称为数据透视表".

Regardless of the database you are using, the concept of what you are trying to achieve is called "Pivot Table".

这是mysql的示例: http://en.wikibooks.org/wiki/MySQL/Pivot_table

Here's an example for mysql: http://en.wikibooks.org/wiki/MySQL/Pivot_table

某些数据库具有内置功能，请参见下面的链接.

Some databases have builtin features for that, see the links below.

SQLServer: http://msdn.microsoft.com/de-de/library/ms177410. aspx

SQLServer: http://msdn.microsoft.com/de-de/library/ms177410.aspx

Oracle: http://www.dba-oracle.com/t_pivot_examples.htm

您始终可以手动创建枢轴.只需选择结果集中的所有聚合，然后从该结果集中选择即可.

You can always create a pivot by hand. Just select all the aggregations in a result set and then select from that result set.

也请检查此链接，您将得到MGA提供的答案...

Also check this link, you will get your answer provided by MGA...

这篇关于班级出勤记录格式SQL查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！