22,300
社区成员




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