entityMap|blocks|key|2708e|text|首先想到的是NOT+EXISTS：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|4atm|select+t.*
from+t
where+status+=+'Checked+Out'+and
++++++not+exists+(select+1
++++++++++++++++++from+t+t2
++++++++++++++++++where+t2.card_number+=+t.card_number+and
++++++++++++++++++++++++t2.status+=+'Checked+In'+and
++++++++++++++++++++++++t2.transaction_date+>=+t.transaction_date+and
++++++++++++++++++++++++t2.transaction_date+<+dateadd(day,+7,+t.transaction_date)
+++++++++++++++++);|code-block|syntax|javascript|fq5af^0|6|A|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|M|9|@$A|N|B|O|C|D]]|E|@]|F|$]]|$2|G|4|H|6|I|8|P|9|@]|E|@]|F|$J|K]]|$2|L|4|-4|6|7|8|Q|9|@]|E|@]|F|$]]]]

<code>NOT EXISTS</code> is the first thing that comes to mind:

<pre><code>select t.*
from t
where status = 'Checked Out' and
 not exists (select 1
 from t t2
 where t2.card_number = t.card_number and
 t2.status = 'Checked In' and
 t2.transaction_date &gt;= t.transaction_date and
 t2.transaction_date &lt; dateadd(day, 7, t.transaction_date)
 );
</code></pre>

entityMap|blocks|key|49vjf|text|select+*+from+T+t
where+status+=+'out'+and+not+exists+(
++++select+1+from+T+t2
++++where+t2.card+=+t.card
++++++++and+t2.status+=+'in'
++++++++and+t.dt+between+t2.dt+and+dateadd(day,+6,+t.dt)
);|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|fssv7|unstyled^0|0^^$0|$]|1|@$2|3|4|5|6|7|8|G|9|@]|A|@]|B|$C|D]]|$2|E|4|-4|6|F|8|H|9|@]|A|@]|B|$]]]]

<pre><code>select * from T t
where status = 'out' and not exists (
 select 1 from T t2
 where t2.card = t.card
 and t2.status = 'in'
 and t.dt between t2.dt and dateadd(day, 6, t.dt)
);
</code></pre>

entityMap|blocks|key|7g1hb|text|您是否研究过SQL+Server的领先/滞后功能？它们允许轻松访问上一行或下一行。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|cdqqn|示例：|ee3tc|DECLARE+@data+TABLE+(+[card_number]+VARCHAR(15),+[status]+VARCHAR(15),+[transaction_date]+DATETIME+);

INSERT+INTO+@data+(
++++++++++++[card_number],+[status],+[transaction_date]
++++++++)+VALUES
++++++++(+'123456',+'Checked+In',+'07/08/2018'+)
++++++++,+(+'123456',+'Checked+Out',+'07/01/2018'+)
++++++++,+(+'234567',+'Checked+In',+'07/08/2018'+)
++++++++,+(+'234567',+'Checked+Out',+'07/01/2018'+)
++++++++,+(+'456789',+'Checked+In',+'07/05/2018'+)
++++++++,+(+'456789',+'Checked+Out',+'07/01/2018'+);

++++++++SELECT
++++++++++++[card_number]
++++++++++++,+[status]
++++++++++++,+CONVERT(+VARCHAR(10),+[transaction_date],+101+)+AS+[txn_date]
++++++++++++,+CONVERT(+VARCHAR(10),+LAG(+[transaction_date],+1+)+OVER+(+ORDER+BY+[card_number],+[transaction_date]+),+101+)+AS+[prev_txn_date]
++++++++++++,+CONVERT(+VARCHAR(10),+LEAD(+[transaction_date],+1+)+OVER+(+ORDER+BY+[card_number],+[transaction_date]+),+101+)+AS+[next_txn_date]
++++++++FROM+@data+
++++++++WHERE
++++++++++++[card_number]+=+'123456'
++++++++ORDER+BY+
++++++++++++[card_number],+[transaction_date];|code-block|syntax|javascript|alhvr|返回：|afn6|%2B-------------%2B-------------%2B------------%2B---------------%2B---------------%2B
%7C+card_number+%7C+++status++++%7C++txn_date++%7C+prev_txn_date+%7C+next_txn_date+%7C
%2B-------------%2B-------------%2B------------%2B---------------%2B---------------%2B
%7C++++++123456+%7C+Checked+Out+%7C+07/01/2018+%7C+NULL++++++++++%7C+07/08/2018++++%7C
%7C++++++123456+%7C+Checked+In++%7C+07/08/2018+%7C+07/01/2018++++%7C+NULL++++++++++%7C
%2B-------------%2B-------------%2B------------%2B---------------%2B---------------%2B|6jkga^0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|O|9|@]|A|@]|B|$]]|$2|C|4|D|6|7|8|P|9|@]|A|@]|B|$]]|$2|E|4|F|6|G|8|Q|9|@]|A|@]|B|$H|I]]|$2|J|4|K|6|7|8|R|9|@]|A|@]|B|$]]|$2|L|4|M|6|G|8|S|9|@]|A|@]|B|$H|I]]|$2|N|4|-4|6|7|8|T|9|@]|A|@]|B|$]]]]

Have you looked into SQL Servers LEAD / LAG functions? These allow easy access to the previous or next rows.

Example:

<pre><code>DECLARE @data TABLE ( [card_number] VARCHAR(15), [status] VARCHAR(15), [transaction_date] DATETIME );

INSERT INTO @data (
 [card_number], [status], [transaction_date]
 ) VALUES
 ( '123456', 'Checked In', '07/08/2018' )
 , ( '123456', 'Checked Out', '07/01/2018' )
 , ( '234567', 'Checked In', '07/08/2018' )
 , ( '234567', 'Checked Out', '07/01/2018' )
 , ( '456789', 'Checked In', '07/05/2018' )
 , ( '456789', 'Checked Out', '07/01/2018' );

 SELECT
 [card_number]
 , [status]
 , CONVERT( VARCHAR(10), [transaction_date], 101 ) AS [txn_date]
 , CONVERT( VARCHAR(10), LAG( [transaction_date], 1 ) OVER ( ORDER BY [card_number], [transaction_date] ), 101 ) AS [prev_txn_date]
 , CONVERT( VARCHAR(10), LEAD( [transaction_date], 1 ) OVER ( ORDER BY [card_number], [transaction_date] ), 101 ) AS [next_txn_date]
 FROM @data 
 WHERE
 [card_number] = '123456'
 ORDER BY 
 [card_number], [transaction_date];
</code></pre>

Returns:

<pre><code>+-------------+-------------+------------+---------------+---------------+
| card_number | status | txn_date | prev_txn_date | next_txn_date |
+-------------+-------------+------------+---------------+---------------+
| 123456 | Checked Out | 07/01/2018 | NULL | 07/08/2018 |
| 123456 | Checked In | 07/08/2018 | 07/01/2018 | NULL |
+-------------+-------------+------------+---------------+---------------+
</code></pre>

entityMap|blocks|key|5hihm|text|我不确定我是否做对了，但这就是我所尝试的。希望能有所帮助！|type|unstyled|depth|inlineStyleRanges|entityRanges|data|8jbhu|select+card_number,+transaction_date,+sta
from
(
select+t1.card_number,+t1.transaction_date,+t1.sta,+datediff(DAY,+t1.transaction_date,+t2.transaction_date)+as+diff
from+trans+t1
join+trans+t2
on+t1.card_number+=+t2.card_number
)+result
where+diff=7+and+sta+=+'Checked+Out'|code-block|syntax|javascript|i9a0^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|I|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|J|9|@]|A|@]|B|$F|G]]|$2|H|4|-4|6|7|8|K|9|@]|A|@]|B|$]]]]

Not sure if I did this right but this is what I attempted. Hope it helps !

<pre><code>select card_number, transaction_date, sta
from
(
select t1.card_number, t1.transaction_date, t1.sta, datediff(DAY, t1.transaction_date, t2.transaction_date) as diff
from trans t1
join trans t2
on t1.card_number = t2.card_number
) result
where diff=7 and sta = 'Checked Out'
</code></pre>

I have a SQL Server table that is storing transactions on a database whenever someone checks out and checks in a card. I am trying to come up with a view to show all card transactions where a person did not check-in a card within 7 days of a checkout. My table looks like below:

<pre><code>+-------------+-------------+------------------+
| Card_Number | Status | Transaction_Date |
+-------------+-------------+------------------+
| 123456 | Checked In | 7/8/2018 |
| 123456 | Checked Out | 7/1/2018 |
| 234567 | Checked In | 7/8/2018 |
| 234567 | Checked Out | 7/1/2018 |
| 456789 | Checked In | 7/5/2018 |
| 456789 | Checked Out | 7/1/2018 |
+-------------+-------------+------------------+
</code></pre>

In the example I would want the records with card numbers 123456 and 234567 but for some reason I can't figure out a good way to make this work. Any help would be appreciated. Thank You in advance.

SQL Server: Check Date of Previous Record

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

 我有一个SQL Server表，每当有人结帐和签入卡时，它都会将事务存储在数据库中。我试图拿出一个视图，以显示所有的卡交易，其中一个人没有登记卡在7天内结账。我的表格如下： +-------------+-------------+------------------+| Card_Number |   Status...

问SQL Server:检查上一条记录的日期
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问SQL Server:检查上一条记录的日期EN