下面是数据表的格式 D是到达,W为完工
姓名 时间 内容
张三 08:10:23 D
张三 10:23:45 W
张三 13:12:13 D
张三 16:14:14 W
张三 17:17:17 D
张三 20:20:20 W
张三 21:21:00 D李四 14:11:56 D
李四 16:25:11 W王二 08:08:08 D
王二 10:10:00 W
王二 14:00:00 D
王二 15:00:00 W怎么写Select语句能够实现下面的形式,D和W是成对出现,时间都是按个人的顺序来的
姓名 时间 内容 姓名 时间 内容
张三 08:10:23 D 张三 10:23:45 W
张三 13:12:13 D 张三 16:14:14 W
张三 17:17:17 D 张三 20:20:20 W
张三 21:21:00 D 李四 14:11:56 D 李四 16:25:11 W王二 08:08:08 D 王二 10:10:00 W
王二 14:00:00 D 王二 15:00:00 W求高手帮忙,谢谢了!
姓名 时间 内容
张三 08:10:23 D
张三 10:23:45 W
张三 13:12:13 D
张三 16:14:14 W
张三 17:17:17 D
张三 20:20:20 W
张三 21:21:00 D李四 14:11:56 D
李四 16:25:11 W王二 08:08:08 D
王二 10:10:00 W
王二 14:00:00 D
王二 15:00:00 W怎么写Select语句能够实现下面的形式,D和W是成对出现,时间都是按个人的顺序来的
姓名 时间 内容 姓名 时间 内容
张三 08:10:23 D 张三 10:23:45 W
张三 13:12:13 D 张三 16:14:14 W
张三 17:17:17 D 张三 20:20:20 W
张三 21:21:00 D 李四 14:11:56 D 李四 16:25:11 W王二 08:08:08 D 王二 10:10:00 W
王二 14:00:00 D 王二 15:00:00 W求高手帮忙,谢谢了!
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([姓名] varchar(4),[时间] varchar(10),[内容] varchar(1))
insert [tb]
select '张三','08:10:23','D' union all
select '张三','10:23:45','W' union all
select '张三','13:12:13','D' union all
select '张三','16:14:14','W' union all
select '张三','17:17:17','D' union all
select '张三','20:20:20','W' union all
select '张三','21:21:00','D' union all
select '李四','14:11:56','D' union all
select '李四','16:25:11','W' union all
select '王二','08:08:08','D' union all
select '王二','10:10:00','W' union all
select '王二','14:00:00','D' union all
select '王二','15:00:00','W'
---查询---
select a.姓名,a.时间,a.内容,b.姓名,b.时间,b.内容
from
(select *,rn=row_number() over(partition by 姓名 order by 时间) from tb where 内容='D') a
full join
(select *,rn=row_number() over(partition by 姓名 order by 时间) from tb where 内容='W') b
on
a.姓名=b.姓名 and a.rn=b.rn
order by a.姓名,a.时间
---结果---
姓名 时间 内容 姓名 时间 内容
---- ---------- ---- ---- ---------- ----
李四 14:11:56 D 李四 16:25:11 W
王二 08:08:08 D 王二 10:10:00 W
王二 14:00:00 D 王二 15:00:00 W
张三 08:10:23 D 张三 10:23:45 W
张三 13:12:13 D 张三 16:14:14 W
张三 17:17:17 D 张三 20:20:20 W
张三 21:21:00 D NULL NULL NULL(7 行受影响)
if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (姓名 varchar(4),时间 datetime,内容 varchar(1))
insert into #tb
select '张三','08:10:23','D' union all
select '张三','10:23:45','W' union all
select '张三','13:12:13','D' union all
select '张三','16:14:14','W' union all
select '张三','17:17:17','D' union all
select '张三','20:20:20','W' union all
select '张三','21:21:00','D' union all
select '李四','14:11:56','D' union all
select '李四','16:25:11','W' union all
select '王二','08:08:08','D' union all
select '王二','10:10:00','W' union all
select '王二','14:00:00','D' union all
select '王二','15:00:00','W'
select t1.*,t2.* from
( select A.*,n=(SELECT count(*) FROM #TB where 姓名=a.姓名 and 内容='D' and 时间<a.时间) from #tb A
WHERE 内容='D'
)t1
left join(
select b.*,n=(SELECT count(*) FROM #TB where 姓名=b.姓名 and 内容='W' and 时间<b.时间) from #tb b
WHERE 内容='W'
)t2
on t1.姓名=t2.姓名 and t1.n=t2.n/*
姓名 时间 内容 n 姓名 时间 内容 n
---- ----------------------- ---- ----------- ---- ----------------------- ---- -----------
张三 1900-01-01 08:10:23.000 D 0 张三 1900-01-01 10:23:45.000 W 0
张三 1900-01-01 13:12:13.000 D 1 张三 1900-01-01 16:14:14.000 W 1
张三 1900-01-01 17:17:17.000 D 2 张三 1900-01-01 20:20:20.000 W 2
张三 1900-01-01 21:21:00.000 D 3 NULL NULL NULL NULL
李四 1900-01-01 14:11:56.000 D 0 李四 1900-01-01 16:25:11.000 W 0
王二 1900-01-01 08:08:08.000 D 0 王二 1900-01-01 10:10:00.000 W 0
王二 1900-01-01 14:00:00.000 D 1 王二 1900-01-01 15:00:00.000 W 1(7 行受影响)
*/select a.姓名 ,a.时间,a.内容,b.姓名,b.时间,b.内容
from #tb a
left join #tb b
on a.姓名=b.姓名
and (select count(*) from #tb where 姓名=a.姓名 and 内容='D' and 时间<a.时间)=(select count(*) from #tb where 姓名=b.姓名 and 内容='W' and 时间<b.时间)
where a.内容='D' and b.内容='W'/*
---这个不可以,很奇怪,哪位帮看看
姓名 时间 内容 姓名 时间 内容
---- ----------------------- ---- ---- ----------------------- ----
张三 1900-01-01 08:10:23.000 D 张三 1900-01-01 10:23:45.000 W
张三 1900-01-01 13:12:13.000 D 张三 1900-01-01 16:14:14.000 W
张三 1900-01-01 17:17:17.000 D 张三 1900-01-01 20:20:20.000 W
李四 1900-01-01 14:11:56.000 D 李四 1900-01-01 16:25:11.000 W
王二 1900-01-01 08:08:08.000 D 王二 1900-01-01 10:10:00.000 W
王二 1900-01-01 14:00:00.000 D 王二 1900-01-01 15:00:00.000 W(6 行受影响)*/
from tableName a
group by a.姓名
having a.内容='D'
order by 时间
[Author]: OrchidCat[OC]_轻骑兵(向高手学习...)
[Time]: 2010-08-09 13:50:13
[Place]: From Beijing
[Version]:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)*/
--> 测试数据:[TBA]
if object_id('[TBA]') is not null drop table [TBA]
create table [TBA]([姓名] varchar(4),[时间] datetime,[内容] varchar(1))
insert [TBA]
select '张三','08:10:23','D' union all
select '张三','10:23:45','W' union all
select '张三','13:12:13','D' union all
select '张三','16:14:14','W' union all
select '张三','17:17:17','D' union all
select '张三','20:20:20','W' union all
select '张三','21:21:00','D' union all
select '李四','14:11:56','D' union all
select '李四','16:25:11','W' union all
select '王二','08:08:08','D' union all
select '王二','10:10:00','W' union all
select '王二','14:00:00','D' union all
select '王二','15:00:00','W'select * from [TBA]SELECT A.[姓名] ,
[时间] = CONVERT(VARCHAR(8), A.[时间], 114) ,
A.[姓名] ,
A.[内容] ,
[时间] = ISNULL(( SELECT CONVERT(VARCHAR(8), MIN([时间]), 114)
FROM TBA B
WHERE A.[姓名] = B.[姓名]
AND B.[内容] = 'W'
AND B.[时间] > A.[时间]
), '') ,
[内容] = 'W'
FROM TBA A
WHERE A.[内容] = 'D'/*姓名 时间 姓名 内容 时间 内容
---- -------- ---- ---- -------- ----
张三 08:10:23 张三 D 10:23:45 W
张三 13:12:13 张三 D 16:14:14 W
张三 17:17:17 张三 D 20:20:20 W
张三 21:21:00 张三 D W
李四 14:11:56 李四 D 16:25:11 W
王二 08:08:08 王二 D 10:10:00 W
王二 14:00:00 王二 D 15:00:00 W(7 行受影响)*/
if object_id('tb') is not null
drop table tb
go
create table tb(姓名 varchar(4),时间 time,内容 varchar(2))
insert into tb
select '张三', '08:10:23', 'D' union all
select '张三', '10:23:45', 'W' union all
select '张三', '13:12:13', 'D' union all
select '张三', '16:14:14', 'W' union all
select '张三', '17:17:17', 'D' union all
select '张三', '20:20:20', 'W' union all
select '张三', '21:21:00', 'D' union all
select '李四', '14:11:56', 'D' union all
select '李四', '16:25:11', 'W' union all
select '王二', '08:08:08', 'D' union all
select '王二', '10:10:00', 'W' union all
select '王二', '14:00:00', 'D' union all
select '王二', '15:00:00', 'W' select * from tb;with t
as
(
select rn=ROW_NUMBER() over(PARTITION by 姓名,内容 order by 时间),姓名, 时间, 内容
from tb
)
select a.姓名, a.时间, a.内容, b.姓名, b.时间, b.内容
from (select * from t
where 内容='D') a LEFT join (select * from t
where 内容='W') b
on a.姓名=b.姓名 and a.rn=b.rn 姓名 时间 内容 姓名 时间 内容
李四 14:11:56.0000000 D 李四 16:25:11.0000000 W
王二 08:08:08.0000000 D 王二 10:10:00.0000000 W
王二 14:00:00.0000000 D 王二 15:00:00.0000000 W
张三 08:10:23.0000000 D 张三 10:23:45.0000000 W
张三 13:12:13.0000000 D 张三 16:14:14.0000000 W
张三 17:17:17.0000000 D 张三 20:20:20.0000000 W
张三 21:21:00.0000000 D NULL NULL NULL
id 手机号 短息内容 日期 时间 COM
1 123456789 D 10-08-06 08:10:23 2
2 123456789 W 10-08-06 10:23:45 2
3 987654321 D 10-08-06 14:11:56 2
4 987654321 W 10-08-06 16:25:11 2
5 123456789 D 10-08-06 13:12:13 2
6 123456789 W 10-08-06 16:14:14 2
7 123456789 D 10-08-06 17:17:17 2
10 123456789 W 10-08-06 20:20:20 2
11 456456456 D 10-08-06 08:08:08 2
12 123456789 D 10-08-06 21:21:00 2
13 456456456 W 10-08-06 10:10:00 2
16 456456456 D 10-08-06 14:00:00 2
15 456456456 W 10-08-06 15:00:00 2
create table tb(姓名 varchar(10),时间 varchar(20),内容 varchar(1))
go
insert into tb select
'张三', '08:10:23', 'D 'union all select
'张三', '10:23:45', 'W' union all select
'张三', '13:12:13', 'D' union all select
'张三', '16:14:14', 'W' union all select
'张三', '17:17:17', 'D' union all select
'张三', '20:20:20', 'W 'union all select
'张三', '21:21:00', 'D' union all select
'李四', '14:11:56', 'D' union all select
'李四', '16:25:11', 'W' union all select
'王二', '08:08:08', 'D' union all select
'王二', '10:10:00', 'W' union all select
'王二', '14:00:00', 'D' union all select
'王二', '15:00:00', 'W'-----2000
select a.姓名,a.时间,'D' 内容,b.姓名,b.时间,内容='W'
from
(
select *,id=(select count(*) from tb where 姓名=a.姓名 and 时间>a.时间 and 内容='d')from tb a where a.内容='d'
)a
full join
(
select *,id=(select count(*) from tb where 姓名=a.姓名 and 时间>a.时间 and 内容='w')from tb a where a.内容='w'
)b
on a.id=b.id and a.姓名 = b.姓名
order by a.姓名,a.时间
/*
姓名 时间 内容 姓名 时间 内容
---------- -------------------- ---- ---------- -------------------- ----
李四 14:11:56 D 李四 16:25:11 W
王二 08:08:08 D 王二 10:10:00 W
王二 14:00:00 D 王二 15:00:00 W
张三 08:10:23 D NULL NULL W
张三 13:12:13 D 张三 10:23:45 W
张三 17:17:17 D 张三 16:14:14 W
张三 21:21:00 D 张三 20:20:20 W(所影响的行数为 7 行)
*/
(select * from tablename where 内容='D')as b
where a.姓名=b.姓名
(select * from tablename where 内容='W')as b
where a.姓名=b.姓名