有个表Log,有字段id,type,time。type=0表示用户进入,type=1表示用户离开
Log
id type time
0 0 1
0 1 2
1 0 4
1 1 5
0 0 6
0 1 8
2 0 9现在希望显示如下信息
Out
id in_time out_time
0 1 2
0 6 8
1 4 5
2 9 null
按用户显示其进入时间和离开时间
如何写SQL?
Log
id type time
0 0 1
0 1 2
1 0 4
1 1 5
0 0 6
0 1 8
2 0 9现在希望显示如下信息
Out
id in_time out_time
0 1 2
0 6 8
1 4 5
2 9 null
按用户显示其进入时间和离开时间
如何写SQL?
from Log
where type=0
a.id,a.time as in_time,min(b.time) as out_time
from
log a
left join
log b
where
a.id=b.id and a.time<b.time
group by
a.id,a.time
a.id,a.time as in_time,min(b.time) as out_time
from
log a
left join
log b
on
a.id=b.id and b.type=1 and a.time<b.time
where
a.type=0
group by
a.id,a.time
insert into @Log select 0,0,1
insert into @Log select 0,1,2
insert into @Log select 1,0,4
insert into @Log select 1,1,5
insert into @Log select 0,0,6
insert into @Log select 0,1,8
insert into @Log select 2,0,9
select
a.id,a.time as in_time,min(b.time) as out_time
from
@log a
left join
@log b
on
a.id=b.id and b.type=1 and a.time<b.time
where
a.type=0
group by
a.id,a.time/*
id in_time out_time
----------- ----------- -----------
0 1 2
1 4 5
0 6 8
2 9 NULL
*/
还想继续问一下,如果log表是某条select语句的查询结果
合起来应该如何写?
只换其中某个可以吗?
建视图时提示查询设计器不支持 CASE SQL 构造。
提点意见,首先要保证进入和离开这两条数据一定要有健全机制保证成对出现,那么查询才有意义。
同意以上观点,你这个表设计的就有问题
FROM (
SELECT id,time
FROM Log intime
WHERE type=0
)intime LEFT JOIN (
SELECT id,time
FROM Log outtime
WHERE type=1
)outtime
ON intime.id=outtime.Id
AND intime.time<outtime.time
GROUP BY intime.Id,intime.time
ORDER BY intime.Id
表结构设计有问题,不然也不至于如此麻烦。