sql取一列中不重复值 然后判断与之相关数据方法

Question

现有下面一个原始表和两个查询sql语句
想得到只入未出或只出未入的卡号并通过系统时间判断已经超过多久
ticket=4为入 ticket=20为出
下面分别为查出只入未出到现在过多久记录，及判断一张卡唯一即未入或未出。想将两个sql查出的结果合并 不知怎样做。
或用其它方法实现所要求结果。
原始表

可查出离最近多久时间sql 但会漏掉唯一的只入未出记录
select
tt.Workshop,
tt.InTicket,
CONVERT(VARCHAR(24),tt.InTime,120)AS InTime,
tt.OutTicket,
CONVERT(VARCHAR(24),tt.OutTime,120)AS OutTime,
tt.Card#,
case when
tt.NotExitCard3hours =tt.NotExitCard3hours
or tt.NotExitCard4hours =tt.NotExitCard4hours
or tt.NotExitCard5hours =tt.NotExitCard5hours
or tt.NotExit1day=tt.NotExit1day
or tt.NotExitCard2day = tt.NotExitCard2day
or tt.NotExitCard3day = tt.NotExitCard3day
then tt.Workshop end as notexitworkshop,
tt.NotExitCard3hours,
tt.NotExitCard4hours,
tt.NotExitCard5hours,
tt.NotExit1day,
tt.NotExitCard2day,
tt.NotExitCard3day
from(
select
getOut.Workshop,
getin.Card#,
getin.InTicket,
getin.InTime,
getOut.OutTicket,
getOut.OutTime,
case when
DATEDIFF(HOUR,getin.InTime,getOut.OutTime)=3
then getOut.Card# end as NotExitCard3hours,
case when
DATEDIFF(HOUR,getin.InTime,getOut.OutTime)=4
then getOut.Card# end as NotExitCard4hours,
case when
DATEDIFF(HOUR,getin.InTime,getOut.OutTime)=5
then getOut.Card# end as NotExitCard5hours,
case when
DATEDIFF(DAY,getin.InTime,getOut.OutTime)=1
then getOut.Card# end as NotExit1day,
case when
DATEDIFF(DAY,getin.InTime,getOut.OutTime)=2
then getOut.Card# end as NotExitCard2day,
case when
DATEDIFF(DAY,getin.InTime,getOut.OutTime)=3
then getOut.Card# end as NotExitCard3day

from(
select
tk.card#,
tk.workshop,
case when tk.Ticket#=4
then tk.Ticket# end as InTicket,

case when tk.Ticket#=4
then min(tk.[Receive Time])end as InTime

from Test.dbo.TicketDetailData125 tk
group by
tk.card#,
tk.workshop,
tk.Ticket#
) as getin,
(
select
tk.card#,
tk.workshop,
case when tk.Ticket#=20
then tk.Ticket# end as OutTicket,
case when tk.Ticket#=20
then max(tk.[Receive Time])end as OutTime
from test.dbo.TicketDetailData125 tk
group by
tk.card#,
tk.workshop,
tk.Ticket#
)as getOut
where
getin.Card#=getOut.Card#
and getOut.OutTime>getin.InTime
and getOut.OutTicket=20
)as tt
where tt.Workshop=tt.Workshop
order by
tt.Workshop

这是可查出那张卡是唯一的即只入或其它状况记录

Answer 1

废话不多说，先上代码：

select A.*,
       decode(A.judge,
              0,
              (decode(A.Ticket#,
                      4,
                      '多次进入，中间未退出',
                      20,
                      '多次退出，中间未进入')),
              -4,
              '只入未出',
              -20,
              '只出未入') as state,
       --找到后分两层decode来判断，第一层先判断是不是0，如果是0，意味着这一次和下一次的状态相同，那就是该卡多次刷了进入或者多次刷了退出
       --第一次另外两种就是-4和-20，一般出现在最后。
       --第二层判断就看原来是进入还是退出了
       floor(period) || '天' || floor((period - floor(period)) * 24) || '小时' ||
       floor((period * 24 - floor(period * 24)) * 60) || '分钟' as period2
       --一个能够显示出X天X小时X分的时间间隔模式
  from (select Ticket#,
               lead(Ticket#, 1, 0) over(partition by Workshop, Card# order by Receive Time) - Ticket# as judge,
               --开窗函数，按照区域（workshop），卡编号（Card#）分组，取下一次刷卡的状态，并和上一次取差值
               --用人话来说就是找到这张卡下一次在这个门是进入还是进出
               Recieve Time,
               lead(Recieve Time, 1, sysdate) over(partition by Workshop, Card# order by Receive Time) - Recieve Time as period,
               --同理，按照区域（workshop），卡编号（Card#）分组，取下一次刷卡的时间，并和上一次取差值，得到的小数的单位是（天）
               --用人话来说就是找到这张卡下一次在这个门是进入还是进出的时间
               Card#,
               Card No.
          from TEST.DBO.TICKETDETAILDATA125
         where Ticket# in (4, 20)) A

嫌麻烦就没把数据导入做测试，不知道行不行……

P.S. 我用的是ORACLE，不知道语法上面什么的行不行……

Answer 2

union all 没法满足需求吗？

Answer 3

试过 嵌套太多 不知怎么搞了

Answer 4

建议你用程序算吧，这表太复杂了

Answer 5

select card, receive_time from
(select card, ticket, receive_time from (select * from tt where ticket in (4,20) order by receive_time desc) t1 group by card, ticket) t
group by card having count(*)<2

Answer 6

用中括号，是sql sever？记录最后入卡时间min(tk.[Receive Time])，不应该是max(tk.[Receive Time])吗？Receive Time是日期格式还是日期时间格式？你代码也不是算只入未出到现在，而是从入到出的时间。

字段ReceiveTime已去掉空格

select t1.card#,t1.last_rt20,t2.last_rt4,datediff(hour,t2.last_rt4,getdate()) lost_hour from
(select card#,max(ReceiveTime) last_rt20 from Test.dbo.TicketDetailData125 where ticket=20 group by card#) t1 inner join (select card#,max(ReceiveTime) last_rt4 from Test.dbo.TicketDetailData125 where ticket=4) t2
on t1.card#=t2.card# and t1.last_rt20<t2.last_rt4