select A1.card_no,
A1.atte_time as "上班时间",
A2.atte_time as "上班时间1",
A3.atte_time as "上班时间2",
A4.atte_time as "上班时间3",
B1.atte_time as "下班时间",
B2.atte_time as "下班时间1",
B3.atte_time as "下班时间2",
B4.atte_time as "下班时间3"
from (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID
from attetime
where doorinout = 1) T
where T.In_ID = 1) A1
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID
from attetime
where doorinout = 1) T
where T.In_ID = 2) A2
on A1.card_no = A2.card_no
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID
from attetime
where doorinout = 1) T
where T.In_ID = 3) A3
on A1.card_no = A3.card_no
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time) as In_ID
from attetime
where doorinout = 1) T
where T.In_ID = 4) A4
on A1.card_no = A4.card_no
full join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID
from attetime
where doorinout = 0) T
where T.Out_ID = 1) B1
on A1.card_no = B1.card_no
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID
from attetime
where doorinout = 0) T
where T.Out_ID = 2) B2
on A1.card_no = B2.card_no
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID
from attetime
where doorinout = 0) T
where T.Out_ID = 3) B3
on A1.card_no = B3.card_no
left join (select card_no, atte_time
from (select card_no,
atte_time,
ROW_NUMBER() over(partition by card_no order by atte_time desc) as Out_ID
from attetime
where doorinout = 0) T
where T.Out_ID = 4) B4
on A1.card_no = B4.card_no
投入验证数据如下：
执行结果如下：
这是在oracle数据库下做的，不知道你是什么数据库，给你提供一下思路
select
card_no,
attr_date,
to_char(atte_time,'HH24:MI')tim,
doorinout,
rn
from (
select
card_no,
attr_date,
atte_time,
doorinout,
ROW_NUMBER() OVER(PARTITION BY card_no,attr_date,doorinout ORDER BY atte_time asc) rn
from(
select
card_no,
(
case
when doorinout=1
then to_date(to_char(atte_time,'yyyy-MM-dd'),'yyyy-MM-dd')
when (doorinout=0 and to_number(to_char(atte_time,'HH24'))<3)
then (to_date(to_char(atte_time,'yyyy-MM-dd'),'yyyy-MM-dd')-1)
else to_date(to_char(atte_time,'yyyy-MM-dd'),'yyyy-MM-dd')
end
)attr_date,
atte_time,
doorinout
from AtteTime
) tmp order by card_no,atte_time asc,doorinout desc
) where rn < 5
能不能来一份数据 这么凭空想 或者构造数据 太麻烦 一些代表性的数据 就行。还有问题要描述清楚 :1)各 取 4笔还是各 去 4 笔，少了为空，多了呢？取时间早的还是时间晚的。
2) 没人每天是什么意思
3) 不经过调试 谁也不能保证自己写出的语句是100%正确的，给点代表性的数据。
内容全部来源于网络收集，如有侵权，请联系网站删除：QQ：24596024