求只有进打卡出未打卡的 卡号记录

[m0_37908418]

现在有新的问题 想求只有进入一直未出的的卡号
之前根据判断inticket和outticked得出一对一横向记录 可以判断出有哪些卡超过两小时，或一天未拍。即未出的。
但如果有人只入inticket=4 一直未出，那么怎样判断？试过用系统时间减去intime结果所有card号全部查出 非想要结果。

原因是如果根据之前条件getin.Card#=getOut.Card# and getOut.OutTime>getin.InTimeand getOut.OutTicket=20
那么outticket 无记录但inticket有记录的就查不出。
如果判断outticket is null,那么最后得出结果就非进与出对应关系，表结果就乱了。 哪位有高见 ？谢谢

[二月十六]

用union试试，不要一个语句查出所有，上边查出所有一对一横向记录，下边查出未出的

select * from 表 getin.Card#=getOut.Card# and getOut.OutTime>getin.InTime and getOut.OutTicket=20
union
select * from 表 a getOut.OutTicket=4 and NOT EXISTS ( SELECT * FROM 表 b WHERE a.card# = b.card# AND b.outtime > a.intime AND b.Ticket = 20 ) --未出的条件判断是什么？这里根据实际情况来写

[二月十六]

楼主光是这样描述理解起来可能会有误差，不如直接上测试数据和想要对应的结果，干净利索，有什么想得到什么，直接就写了。
比如
a b
1 2
1 3

得到
a sum
1 5

这样写一下清楚多了

[m0_37908418]

但是我已经生成两个临时表查询了 用union还可以吗

[二月十六]

引用 3 楼 m0_37908418 的回复：

但是我已经生成两个临时表查询了 用union还可以吗

可以的，正常sql语句查询

[m0_37908418]

这是两个查询结果的sql 不过怎么都union不到 报错 SELECT * from(SELECT td.Workshop, case when td.Ticket#=4 then td.Card# end as InCard, case when td.Ticket#=4 then td.Ticket# end as InTicket, case when td.Ticket#=4 then td.[Receive Time] end as InTime FROM [Test].[dbo].[TicketDetailData125] td )as getIn, ( SELECT td.Workshop, case when td.Ticket#=20 then td.Card# end as OutCard, case when td.Ticket#=20 then td.Ticket# end as OutTicket, case when td.Ticket#=20 then td.[Receive Time] end as OutTime FROM [Test].[dbo].[TicketDetailData125] td )AS getOut where getIn.InCard=getOut.OutCard order by getIn.Workshop union select * from( SELECT td.Workshop, td.card#, case when td.Ticket#=4 then td.Ticket# end as Inticket, case when td.Ticket#=20 then td.Ticket# end as Outticket, case when td.Ticket#=4 then td.Card# end as Incard, case when td.Ticket#=20 then td.Card# end as Outcard FROM [Test].[dbo].[TicketDetailData125] td )as getall where getall.Workshop =getall.Workshop order by getall.Workshop union select * from( SELECT td.Workshop, td.card#, case when td.Ticket#=4 then td.Ticket# end as Inticket, case when td.Ticket#=20 then td.Ticket# end as Outticket, case when td.Ticket#=4 then td.Card# end as Incard, case when td.Ticket#=20 then td.Card# end as Outcard FROM [Test].[dbo].[TicketDetailData125] td )as getall where getall.Workshop =getall.Workshop order by getall.Workshop 其实现在只差图中1166800300348 不能同时查出 因为这张卡只有入没有出 其它都是一对出现的

[m0_37908418]

没人知道可以怎样计只入无出的数据吗？

[二月十六]

报错报什么错？

SELECT *
FROM
(
    SELECT td.Workshop,
           CASE
               WHEN td.Ticket# = 4 THEN
                   td.Card#
           END AS InCard,
           CASE
               WHEN td.Ticket# = 4 THEN
                   td.Ticket#
           END AS InTicket,
           CASE
               WHEN td.Ticket# = 4 THEN
                   td.[Receive Time]
           END AS InTime
    FROM [Test].[dbo].[TicketDetailData125] td
) AS getIn ,
(
    SELECT td.Workshop,
           CASE
               WHEN td.Ticket# = 20 THEN
                   td.Card#
           END AS OutCard,
           CASE
               WHEN td.Ticket# = 20 THEN
                   td.Ticket#
           END AS OutTicket,
           CASE
               WHEN td.Ticket# = 20 THEN
                   td.[Receive Time]
           END AS OutTime
    FROM [Test].[dbo].[TicketDetailData125] td
) AS getOut
WHERE getIn.InCard = getOut.OutCard
UNION
SELECT *
FROM
(
    SELECT td.Workshop,
           td.card#,
           CASE
               WHEN td.Ticket# = 4 THEN
                   td.Ticket#
           END AS Inticket,
           CASE
               WHEN td.Ticket# = 20 THEN
                   td.Ticket#
           END AS Outticket,
           CASE
               WHEN td.Ticket# = 4 THEN
                   td.Card#
           END AS Incard,
           CASE
               WHEN td.Ticket# = 20 THEN
                   td.Card#
           END AS Outcard
    FROM [Test].[dbo].[TicketDetailData125] td
) AS getall
WHERE getall.Workshop = getall.Workshop
UNION
SELECT *
FROM
(
    SELECT td.Workshop,
           td.card#,
           CASE
               WHEN td.Ticket# = 4 THEN
                   td.Ticket#
           END AS Inticket,
           CASE
               WHEN td.Ticket# = 20 THEN
                   td.Ticket#
           END AS Outticket,
           CASE
               WHEN td.Ticket# = 4 THEN
                   td.Card#
           END AS Incard,
           CASE
               WHEN td.Ticket# = 20 THEN
                   td.Card#
           END AS Outcard
    FROM [Test].[dbo].[TicketDetailData125] td
) AS getall
WHERE getall.Workshop = getall.Workshop;

[m0_37908418]

报这个错 Msg 205, Level 16, State 1, Line 1 All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.

[m0_37908418]

你有邮箱吗 我将原始数据发给你 还有sql query 都查出来了 但就是结合在一起的时候 有问题 能解决的话 这200分都给你啊

[m0_37908418]

因为incard这种字段是通过临时表得出的 我意思有没有单独判定只入没出的方法 然后嵌套进sql 中一起通过临时表得出结果 网上查了 都没具体的

[john137724]

不要用select *，根据临时表里的字段和你需要的字段取。字段类型和个数不一样的结果集不能union的

[john137724]

我仔细看了下第一个结果集有个字段是incard，而对应第二个结果集里的字段是car，这样合并后的字段就不知道该叫什么了，所以会报错的。纠正下合并的前提是字段个数，类型，名称都要一致