如何把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

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

 

你先试下这个：
select a.userid,a.checktype,b.checktype,c.checktype,d.checktype
  from ((checktmp a right outer join checktmp b on a.userid=b.userid and b.checktype='O' and b.checkflag='4')
       right outer join checktmp c on a.userid=c.userid and c.checktype='I' and c.checkflag='5')
       right outer join checktmp d on a.userid=d.userid and d.checktype='O' and d.checkflag='5'
where a.checktype='I' and a.checkflag='4'
看看结果
然后用：
select a.userid as 用户id,
       count(a.checktype) as 上午签到次数,
       count(b.checktype) as 上午签退次数,
        count(c.checktype) as 下午签到次数,
       count(d.checktype) as 下午签退次数
from ((checktmp a right outer join checktmp b on a.userid=b.userid and b.checktype='O' and b.checkflag='4')
       right outer join checktmp c on a.userid=c.userid and c.checktype='I' and c.checkflag='5')
       right outer join checktmp d on a.userid=d.userid and d.checktype='O' and d.checkflag='5'
where a.checktype='I' and a.checkflag='4'
group by a.userid
注：count(列名)不会统计该列NULL值；测试表名为：checktmp

测试表结构：
CREATE TABLE [dbo].[CheckTMP] (
[SN] [char] (10) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[UserID] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CheckType] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[CheckTime] [datetime] NULL ,
[CheckFlag] [char] (10) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]

关于使用join连接（自己连自己）还是有很多种方法（如right/left/full/outer）将矩阵转置，但是具体数据发生的情况有些特殊的情况，我做了一些测试，结果都不是很满意。而且在实际工作情况中，建议使用4个独立的select语句来完成，这样不光效率高，而且维护性和可移植性也好。

偶是用oracle的，但是好像sql server 2005有pivot函数可以搞定这一切，在oracle中这是行转列的问题，已经有解决方案了～