create table tb(工号 int,上班1 datetime,下班1 datetime)
insert into tb values(1586,'2007-11-01 19:00','2007-11-01 22:00')
insert into tb values(1586,'2007-11-02 19:00','2007-11-02 22:00')
insert into tb values(1586,'2007-11-03 19:00','2007-11-03 22:00')
insert into tb values(1586,'2007-11-04 19:00','2007-11-04 22:00')
goselect 工号 , 上班1 = dateadd(minute , px , 上班1) , 下班1 = dateadd(minute , px , 下班1) from
(
select px = (select count(1) from tb where 工号 = a.工号 and 上班1 < a.上班1) + 1 , * from tb a
) tdrop table tb/*
工号 上班1 下班1
----------- ------------------------------------------------------ ------------------------------------------------------
1586 2007-11-01 19:01:00.000 2007-11-01 22:01:00.000
1586 2007-11-02 19:02:00.000 2007-11-02 22:02:00.000
1586 2007-11-03 19:03:00.000 2007-11-03 22:03:00.000
1586 2007-11-04 19:04:00.000 2007-11-04 22:04:00.000(所影响的行数为 4 行)
*/
insert into tb values(1586,'2007-11-01 19:00','2007-11-01 22:00')
insert into tb values(1586,'2007-11-02 19:00','2007-11-02 22:00')
insert into tb values(1586,'2007-11-03 19:00','2007-11-03 22:00')
insert into tb values(1586,'2007-11-04 19:00','2007-11-04 22:00')
goselect 工号 , 上班1 = dateadd(minute , px , 上班1) , 下班1 = dateadd(minute , px , 下班1) from
(
select px = (select count(1) from tb where 工号 = a.工号 and 上班1 < a.上班1) + 1 , * from tb a
) tdrop table tb/*
工号 上班1 下班1
----------- ------------------------------------------------------ ------------------------------------------------------
1586 2007-11-01 19:01:00.000 2007-11-01 22:01:00.000
1586 2007-11-02 19:02:00.000 2007-11-02 22:02:00.000
1586 2007-11-03 19:03:00.000 2007-11-03 22:03:00.000
1586 2007-11-04 19:04:00.000 2007-11-04 22:04:00.000(所影响的行数为 4 行)
*/
解决方案 »
- select '1' 用法
- 如何将SELECT出来的结果,INSERT到一个新的表格???
- 一个sql 查询的问题,如何多出一个rowid列
- 怎样把表中的identity列改成从1开始
- 两个表select问题 在线等
- SELECT语句如何将一条记录拆成几条?
- 存储过程的问题,关于游标声明
- 高手请教:这个存储过程问题出在哪里>>新手上路问题多多
- 如何改变 sql server 中字段类型,,,同时把该字段改为"自动编号",,,急。。
- SQL高手请进,有一组数据,分别是收入,支出,余额,它们的关系是:
- 单词匹配问题,a表为关键词,b表为全称词,如何匹配关键词为纯英文单词?
- 触发器写法,急求大侠帮助!!
create table #
(工号 int, 上班1 datetime, 下班1 datetime )insert into #
select 1586, '2007-11-01 19:00', '2007-11-01 22:00' union all
select 1586, '2007-11-02 19:00', '2007-11-02 22:00' union all
select 1586, '2007-11-03 19:00', '2007-11-03 22:00' union all
select 1586, '2007-11-04 19:00', '2007-11-04 22:00' select top 15 id=identity(int,1,1) into #t from sysobjects,syscolumnsselect top 4 工号,上班1=dateadd(minute,-b.id,上班1),
下班1=dateadd(minute,b.id,下班1)
from # a , #t b
order by newid()/*
----------- ------------------------------------------------------ ------------------------------------------------------
1586 2007-11-04 18:56:00.000 2007-11-04 22:04:00.000
1586 2007-11-01 18:51:00.000 2007-11-01 22:09:00.000
1586 2007-11-03 18:51:00.000 2007-11-03 22:09:00.000
1586 2007-11-01 18:54:00.000 2007-11-01 22:06:00.000(所影响的行数为 4 行)
*/
insert into tb values(1586,'2007-11-01 19:00','2007-11-01 22:00')
insert into tb values(1586,'2007-11-02 19:00','2007-11-02 22:00')
insert into tb values(1586,'2007-11-03 19:00','2007-11-03 22:00')
insert into tb values(1586,'2007-11-04 19:00','2007-11-04 22:00') select 工号,上班1=dateadd(ss, -rand(checksum(newid()))*900, 上班1),下班1=dateadd(ss, rand(checksum(newid()))*900, 下班1)
from tbdrop table tb/*
工号 上班1 下班1
----------- ----------------------- -----------------------
1586 2007-11-01 18:48:40.000 2007-11-01 22:01:03.000
1586 2007-11-02 18:48:10.000 2007-11-02 22:12:00.000
1586 2007-11-03 18:46:31.000 2007-11-03 22:14:46.000
1586 2007-11-04 18:53:44.000 2007-11-04 22:00:29.000(4 row(s) affected)
*/
insert into tb values(1586,'2007-11-01 19:00','2007-11-01 22:00')
insert into tb values(1586,'2007-11-02 19:00','2007-11-02 22:00')
insert into tb values(1586,'2007-11-03 19:00','2007-11-03 22:00')
insert into tb values(1586,'2007-11-04 19:00','2007-11-04 22:00') select 工号,上班1=dateadd(ss, checksum(newid())%450-450, 上班1),下班1=dateadd(ss, 450+checksum(newid())%450, 下班1)
from tbdrop table tb/*
工号 上班1 下班1
----------- ----------------------- -----------------------
1586 2007-11-01 18:50:20.000 2007-11-01 22:06:56.000
1586 2007-11-02 18:59:24.000 2007-11-02 22:08:39.000
1586 2007-11-03 18:47:24.000 2007-11-03 22:07:48.000
1586 2007-11-04 18:56:24.000 2007-11-04 22:02:32.000(4 row(s) affected)
*/
create table #
(工号 int, 上班1 datetime, 下班1 datetime )insert into #
select 1586, '2007-11-01 19:00', '2007-11-01 22:00' union all
select 1586, '2007-11-02 19:00', '2007-11-02 22:00' union all
select 1586, '2007-11-03 19:00', '2007-11-03 22:00' union all
select 1586, '2007-11-04 19:00', '2007-11-04 22:00' select top 15 id=identity(int,1,1) into #t from sysobjects,syscolumnsdeclare @i int
declare @s varchar(1000)
select @i=(select count(*) from #)select @s='select top '+ ltrim(@i) +' 工号,上班1=dateadd(minute,-b.id,上班1),' +
' 下班1=dateadd(minute,b.id,下班1) from # a , #t b order by newid()'exec(@s)/*
工号 上班1 下班1
----------- ------------------------------------------------------ ------------------------------------------------------
1586 2007-11-04 18:56:00.000 2007-11-04 22:04:00.000
1586 2007-11-02 18:45:00.000 2007-11-02 22:15:00.000
1586 2007-11-01 18:58:00.000 2007-11-01 22:02:00.000
1586 2007-11-04 18:55:00.000 2007-11-04 22:05:00.000*/