TB1 考勤系统里的刷卡数据表。记录员工工号及刷卡的日期和时间,如下表:
BH,RQ, SJ
1, 2009-05-01,08:00
1, 2009-05-01,12:00
1, 2009-05-01,13:00
1, 2009-05-01,17:00
2, 2009-05-01,08:00
2, 2009-05-01,12:11
2, 2009-05-01,13:13
2, 2009-05-01,17:00
3, 2009-05-01,08:13
3, 2009-05-01,12:00
3, 2009-05-01,14:00
3, 2009-05-01,18:00
3, 2009-05-01,19:00
3, 2009-05-01,20:00
.
.
.
TB2 考勤系统里的 考勤日报表 记录员工每天的刷卡情况,如下表:
BH,RQ,SJ1,SJ2,SJ3,SJ4,SJ5,SJ6,SJ7,SJ8,SJ9,SJ10,SJ11,SJ12
分别对应为:
工号,日期,当天的第一次刷卡时间,第二次刷卡,第三次刷卡......
我想将TB1里的数据,转换成TB2的格式。
注意:
1,如果某员工当天刷卡超过12次则,超过的卡都不插入到TB2了。(按刷卡时间排序。)按以上数据,得以的结果
TB2
BH,RQ, SJ1, SJ2, SJ3, SJ4, SJ5, SJ6, SJ7, SJ8, SJ9, SJ10, SJ11, SJ12
1, 2009-05-01,08:00,12:00,13:00,17:00
2, 2009-05-01,08:00,12:11,13:13,17:00
3, 2009-05-01,08:13,12:00,14:00,18:00,19:00,20:00
BH,RQ, SJ
1, 2009-05-01,08:00
1, 2009-05-01,12:00
1, 2009-05-01,13:00
1, 2009-05-01,17:00
2, 2009-05-01,08:00
2, 2009-05-01,12:11
2, 2009-05-01,13:13
2, 2009-05-01,17:00
3, 2009-05-01,08:13
3, 2009-05-01,12:00
3, 2009-05-01,14:00
3, 2009-05-01,18:00
3, 2009-05-01,19:00
3, 2009-05-01,20:00
.
.
.
TB2 考勤系统里的 考勤日报表 记录员工每天的刷卡情况,如下表:
BH,RQ,SJ1,SJ2,SJ3,SJ4,SJ5,SJ6,SJ7,SJ8,SJ9,SJ10,SJ11,SJ12
分别对应为:
工号,日期,当天的第一次刷卡时间,第二次刷卡,第三次刷卡......
我想将TB1里的数据,转换成TB2的格式。
注意:
1,如果某员工当天刷卡超过12次则,超过的卡都不插入到TB2了。(按刷卡时间排序。)按以上数据,得以的结果
TB2
BH,RQ, SJ1, SJ2, SJ3, SJ4, SJ5, SJ6, SJ7, SJ8, SJ9, SJ10, SJ11, SJ12
1, 2009-05-01,08:00,12:00,13:00,17:00
2, 2009-05-01,08:00,12:11,13:13,17:00
3, 2009-05-01,08:13,12:00,14:00,18:00,19:00,20:00
解决方案 »
- 取最大值
- 不小心删了个存储与表!没有备份,还有救吗?
- 关于BCP到处文件的最大长度。
- 我想要求一个算法,就是每当传入一个日期的时候就把与这个日期最接近的两个值取出来???
- 请问这个触发器怎么写?更新数据时触发,如何取得更新字段的原值和新值都传递给调用的存储过程?
- 关于分组
- 關於產品BOM的查詢方法
- 请教!在VB里调用sql存储过程时候,出现精度无效。 是发生在cmd.Parameters.Append cmd.CreateParameter(, adNumeric, adParamInput, , r
- 请问如何用vb+SQl Server(存储过程)实现记录分页??!!谢谢.
- 请问不安装SQL服务器用什么办法可以连接上SQL数据库??
- VC与数据库开发初学者急求帮助!
- 数据库面试题
*
标题:普通行列转换(version 2.0)
作者:爱新觉罗.毓华(十八年风雨,守得冰山雪莲花开)
时间:2008-03-09
地点:广东深圳
说明:普通行列转换(version 1.0)仅针对sql server 2000提供静态和动态写法,version 2.0增加sql server 2005的有关写法。问题:假设有张学生成绩表(tb)如下:
姓名 课程 分数
张三 语文 74
张三 数学 83
张三 物理 93
李四 语文 74
李四 数学 84
李四 物理 94
想变成(得到如下结果):
姓名 语文 数学 物理
---- ---- ---- ----
李四 74 84 94
张三 74 83 93
-------------------
*/create table tb(姓名 varchar(10) , 课程 varchar(10) , 分数 int)
insert into tb values('张三' , '语文' , 74)
insert into tb values('张三' , '数学' , 83)
insert into tb values('张三' , '物理' , 93)
insert into tb values('李四' , '语文' , 74)
insert into tb values('李四' , '数学' , 84)
insert into tb values('李四' , '物理' , 94)
go--SQL SERVER 2000 静态SQL,指课程只有语文、数学、物理这三门课程。(以下同)
select 姓名 as 姓名 ,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理
from tb
group by 姓名--SQL SERVER 2000 动态SQL,指课程不止语文、数学、物理这三门课程。(以下同)
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + 课程 from tb group by 课程
set @sql = '[' + @sql + ']'
exec ('select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b')---------------------------------/*
问题:在上述结果的基础上加平均分,总分,得到如下结果:
姓名 语文 数学 物理 平均分 总分
---- ---- ---- ---- ------ ----
李四 74 84 94 84.00 252
张三 74 83 93 83.33 250
*/--SQL SERVER 2000 静态SQL。
select 姓名 姓名,
max(case 课程 when '语文' then 分数 else 0 end) 语文,
max(case 课程 when '数学' then 分数 else 0 end) 数学,
max(case 课程 when '物理' then 分数 else 0 end) 物理,
cast(avg(分数*1.0) as decimal(18,2)) 平均分,
sum(分数) 总分
from tb
group by 姓名--SQL SERVER 2000 动态SQL。
declare @sql varchar(8000)
set @sql = 'select 姓名 '
select @sql = @sql + ' , max(case 课程 when ''' + 课程 + ''' then 分数 else 0 end) [' + 课程 + ']'
from (select distinct 课程 from tb) as a
set @sql = @sql + ' , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名'
exec(@sql) --SQL SERVER 2005 静态SQL。
select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (语文,数学,物理)) b) m,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名--SQL SERVER 2005 动态SQL。
declare @sql varchar(8000)
select @sql = isnull(@sql + ',' , '') + 课程 from tb group by 课程
exec ('select m.* , n.平均分 , n.总分 from
(select * from (select * from tb) a pivot (max(分数) for 课程 in (' + @sql + ')) b) m ,
(select 姓名 , cast(avg(分数*1.0) as decimal(18,2)) 平均分 , sum(分数) 总分 from tb group by 姓名) n
where m.姓名 = n.姓名')drop table tb ------------------
------------------
-- Author : HappyFlyStone
-- Date : 2009-05-30 15:07:38
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([BH] INT,[RQ] datetime,[SJ] NVARCHAR(6))
Go
INSERT INTO ta
SELECT 1,'2009-05-01','08:00' union all
SELECT 1,'2009-05-01','12:00' union all
SELECT 1,'2009-05-01','13:00' union all
SELECT 1,'2009-05-01','17:00' union all
SELECT 2,'2009-05-01','08:00' union all
SELECT 2,'2009-05-01','12:11' union all
SELECT 2,'2009-05-01','13:13' union all
SELECT 2,'2009-05-01','17:00' union all
SELECT 3,'2009-05-01','08:13' union all
SELECT 3,'2009-05-01','12:00' union all
SELECT 3,'2009-05-01','14:00' union all
SELECT 3,'2009-05-01','18:00' union
SELECT 3,'2009-05-01','19:00' union
SELECT 3,'2009-05-01','20:00' GO
--Start
; with cte
as
(
SELECT
* ,rowid = row_number() over (partition by bh,rq order by sj)
FROM
TA
)
select bh,rq,
max(case when rowid = 1 then sj else '' end) as sj1,
max(case when rowid = 2 then sj else '' end) as sj2,
max(case when rowid = 3 then sj else '' end) as sj3,
max(case when rowid = 4 then sj else '' end) as sj4,
max(case when rowid = 5 then sj else '' end) as sj5,
max(case when rowid = 6 then sj else '' end) as sj6,
max(case when rowid = 7 then sj else '' end) as sj7,
max(case when rowid = 8 then sj else '' end) as sj8,
max(case when rowid = 9 then sj else '' end) as sj9,
max(case when rowid = 10 then sj else '' end) as sj10,
max(case when rowid = 11 then sj else '' end) as sj11,
max(case when rowid = 12 then sj else '' end) as sj12
from cte
group by bh,rq--Result:
/*(14 行受影响)
bh rq sj1 sj2 sj3 sj4 sj5 sj6 sj7 sj8 sj9 sj10 sj11 sj12
----------- ----------------------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
1 2009-05-01 00:00:00.000 08:00 12:00 13:00 17:00
2 2009-05-01 00:00:00.000 08:00 12:11 13:13 17:00
3 2009-05-01 00:00:00.000 08:13 12:00 14:00 18:00 19:00 20:00 (3 行受影响)*/
--End
-- Author : HappyFlyStone
-- Date : 2009-05-30 15:07:38
-- Version: Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
-- Apr 14 2006 01:12:25
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
--
-------------------------------------------------------------------------- Test Data: ta
IF OBJECT_ID('[ta]') IS NOT NULL
DROP TABLE [ta]
Go
CREATE TABLE ta([BH] INT,[RQ] datetime,[SJ] NVARCHAR(6))
Go
INSERT INTO ta
SELECT 1,'2009-05-01','08:00' union all
SELECT 1,'2009-05-01','12:00' union all
SELECT 1,'2009-05-01','13:00' union all
SELECT 1,'2009-05-01','17:00' union all
SELECT 2,'2009-05-01','08:00' union all
SELECT 2,'2009-05-01','12:11' union all
SELECT 2,'2009-05-01','13:13' union all
SELECT 2,'2009-05-01','17:00' union all
SELECT 3,'2009-05-01','08:13' union all
SELECT 3,'2009-05-01','12:00' union all
SELECT 3,'2009-05-01','14:00' union all
SELECT 3,'2009-05-01','18:00' union
SELECT 3,'2009-05-01','19:00' union
SELECT 3,'2009-05-01','20:00' GO
--Startselect bh,rq,
max(case when rowid = 1 then sj else '' end) as sj1,
max(case when rowid = 2 then sj else '' end) as sj2,
max(case when rowid = 3 then sj else '' end) as sj3,
max(case when rowid = 4 then sj else '' end) as sj4,
max(case when rowid = 5 then sj else '' end) as sj5,
max(case when rowid = 6 then sj else '' end) as sj6,
max(case when rowid = 7 then sj else '' end) as sj7,
max(case when rowid = 8 then sj else '' end) as sj8,
max(case when rowid = 9 then sj else '' end) as sj9,
max(case when rowid = 10 then sj else '' end) as sj10,
max(case when rowid = 11 then sj else '' end) as sj11,
max(case when rowid = 12 then sj else '' end) as sj12
from (
select *,rowid = (select count(1) from ta where a.bh = bh and datediff(d,a.rq,rq) = 0 and sj <= a.sj)
from ta a
) b
group by bh,rq--Result:
/*(14 行受影响)
bh rq sj1 sj2 sj3 sj4 sj5 sj6 sj7 sj8 sj9 sj10 sj11 sj12
----------- ----------------------- ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------ ------
1 2009-05-01 00:00:00.000 08:00 12:00 13:00 17:00
2 2009-05-01 00:00:00.000 08:00 12:11 13:13 17:00
3 2009-05-01 00:00:00.000 08:13 12:00 14:00 18:00 19:00 20:00 (3 行受影响)*/
--End
IF OBJECT_ID('LI') IS NOT NULL
DROP TABLE LI CREATE TABLE LI(BH INT,RQ SMALLDATETIME,SJ SMALLDATETIME)
INSERT INTO LI(BH,RQ,SJ)
SELECT 1,'2009-05-01','08:00' UNION ALL
SELECT 1,'2009-05-01','12:00' UNION ALL
SELECT 1,'2009-05-01','13:00' UNION ALL
SELECT 1,'2009-05-01','17:00' UNION ALL
SELECT 2,'2009-05-01','08:00' UNION ALL
SELECT 2,'2009-05-01','12:11' UNION ALL
SELECT 2,'2009-05-01','13:13' UNION ALL
SELECT 2,'2009-05-01','17:00' UNION ALL
SELECT 3,'2009-05-01','08:13' UNION ALL
SELECT 3,'2009-05-01','12:00' UNION ALL
SELECT 3,'2009-05-01','14:00' UNION ALL
SELECT 3,'2009-05-01','18:00' UNION ALL
SELECT 3,'2009-05-01','19:00' UNION ALL
SELECT 3,'2009-05-01','20:00'
--结果
SELECT LO.BH, LO.RQ,
MAX(CASE WHEN RID = 1 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ1 ,
MAX(CASE WHEN RID = 2 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ2 ,
MAX(CASE WHEN RID = 3 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ3 ,
MAX(CASE WHEN RID = 4 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ4 ,
MAX(CASE WHEN RID = 5 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ5 ,
MAX(CASE WHEN RID = 6 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ6 ,
MAX(CASE WHEN RID = 7 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ7 ,
MAX(CASE WHEN RID = 8 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ8 ,
MAX(CASE WHEN RID = 9 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ9 ,
MAX(CASE WHEN RID = 10 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ10 ,
MAX(CASE WHEN RID = 11 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ11 ,
MAX(CASE WHEN RID = 12 THEN CONVERT(VARCHAR,SJ,108) ELSE NULL END) AS SJ12
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY BH ORDER BY BH ) AS RID,*
FROM LI) LO
GROUP BY LO.BH,LO.RQ
/*结果
1 2009-05-01 00:00:00 08:00:00 12:00:00 13:00:00 17:00:00 NULL NULL NULL NULL NULL NULL NULL NULL
2 2009-05-01 00:00:00 08:00:00 12:11:00 13:13:00 17:00:00 NULL NULL NULL NULL NULL NULL NULL NULL
3 2009-05-01 00:00:00 08:13:00 12:00:00 14:00:00 18:00:00 19:00:00 20:00:00 NULL NULL NULL NULL NULL NULL
*/