mysql查询结果翻转_如何把sql结果集翻转

我用的是sql２０００，请教如何把sql结果集翻转？

如下一张表:checkinout 显示员工签到,签退的考勤表,

checktype:考勤类型 I 表示签到,O 表示签退

timeflag: 4表示上午,5表示下午

checktime  签到,签

userid                      checktype                           checktime                                   timeflag

1                                   I                                      2007-01-01 08:30:30                    4

1                                  O                                       2007-01-01 11:40:00                   4

1                                   I                                      2007-01-01 13:30:30                     5

1                                  O                                      2007-01-01 17:40:00                    5

2                                    I                                      2007-01-01 08:30:30                     4

2                                  O                                    2007-01-01 11:40:00                       4

2                                   I                                      2007-01-01 13:30:30                      5

2                                  O                                      2007-01-01 17:40:00                     5

如何来统计任意一段日子里的考勤情况 ,如下效果:相当于把checkinout表翻转过来

用户id                      上午签到次数                      上午签退次数              下午签到次数                  下午签退次数

1                                        10                                        10                                        10                                 9

2                                       10                                        10                                        10                                 10

下面给出我的解决方案:

通过循环每一个人的userid,checktype,timeflag

如上午签到的次数：

CREATE FUNCTION getShangwuin(@userID as int)

RETURNS int as

begin

declare @counts Int

SELECT @counts = count(*) from checkinout2 where userid=@userID and checktype='I' and timeflag='4'

return (@counts)

end

请教给出更好的解决方案，最好通过一条语句把它搞定