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

m0_37908418 2018-12-06 12:03:01

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

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

...全文

865 12 打赏收藏转发到动态举报

写回复

用AI写文章

12 条回复

切换为时间正序

请发表友善的回复…

发表回复

john137724 2018-12-09

打赏
举报

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

john137724 2018-12-09

打赏
举报

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

m0_37908418 2018-12-09

打赏
举报

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

m0_37908418 2018-12-07

打赏
举报

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

m0_37908418 2018-12-07

打赏
举报

报这个错 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.

二月十六 2018-12-07

打赏
举报

报错报什么错？

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 2018-12-07

打赏
举报

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

m0_37908418 2018-12-06

打赏
举报

这是两个查询结果的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 不能同时查出因为这张卡只有入没有出其它都是一对出现的

二月十六 2018-12-06

打赏
举报

引用 3 楼 m0_37908418 的回复：

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

可以的，正常sql语句查询

m0_37908418 2018-12-06

打赏
举报

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

二月十六 2018-12-06

打赏
举报

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

得到
a sum
1 5

这样写一下清楚多了

二月十六 2018-12-06

打赏
举报

用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 ) --未出的条件判断是什么？这里根据实际情况来写