每次值根据日期顺序发生变化时选择行
美好的一天希望我能很好地解释我的问题。我没有任何示例查询，因为我无法完成任何工作。但我的问题是我有一个表，其中包含帐户列表及其状态以及帐户发生更改的日期。我需要提取帐号，并且每次状态发生变化时以及它第一次更改的日期。我尝试过使用排名和最小值（日期）和最大值（日期）；我遇到的问题是帐户可以在状态之间来回切换，每次更改时我都需要。此外，表中的新行可能具有相同的帐号和状态，但更新日期不同。
这是示例数据：
abc C50 1/20/2022
abc C50 1/21/2022
abc C09 2/20/2022
abc C50 3/1/2022
def A54 1/20/2022
def A26 1/21/2022
def A26 2/20/2022
def A54 3/1/2022
正如您所看到的，帐户 abc 有 3 个 C50 实例和一个 C09 实例，对于我的结果，我希望看到的是，前两个 C50 中最早的一个，然后是 C09，然后是下一个 C50，因为它是新的状态变化。
abc C50 1/20/2022
abc C09 2/20/2022
abc C50 3/1/2022
对于第二个帐户 def；我希望在一号看到第一个 A54，第一个 A26，然后是下一个 A54，因为它是一个新实例。
def A54 1/20/2022
def A26 1/21/2022
def A54 3/1/2022
Good Day Hopefully I can explain my problem well enough. I do not have any sample query as I can not get anything to work. But my problem is I have a table that contains a list of accounts and their status and a date that a change occurred on the account. I need to pull the account number and each time the status changes along with the first date it changed. I have tried using rank and min(date) and max(date); the problem I am running into is the account can go back and forth between statuses and I need each time it changes. Also, a new row could be in the table with the same account number and status but the update date is different.
This is sample data:
abc C50 1/20/2022
abc C50 1/21/2022
abc C09 2/20/2022
abc C50 3/1/2022
def A54 1/20/2022
def A26 1/21/2022
def A26 2/20/2022
def A54 3/1/2022
As you can see account abc has 3 instances of C50 and one instance of C09 for my results I would expect to see, the earliest of the first two C50s, then the C09 and then next C50, since its a new status change.
abc C50 1/20/2022
abc C09 2/20/2022
abc C50 3/1/2022
For the second account def; I would expect to see the first A54, the first A26, then the next A54 on the 1st as it is a new instance.
def A54 1/20/2022
def A26 1/21/2022
def A54 3/1/2022
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
IMO 对于这种问题，我发现最好考虑一种不同类型的模型。您要求的查询不容易编写（如果可能的话），并且如果您的底层结构发生变化，则很难继续前进
那么您有什么选择？选项 1< /strong> 创建实际更改的汇总表。将此表视为日志/审核表，您仅在发生更改时记录更改。然后您可以简单地从此表中读取并输出结果
选项2
在主表上创建一个
revision列。第一次时您可以从 1 开始，只有在发生更改时才增加修订版本。例如，然后您可以获得大于零的帐户的所有修订版本，并按修订版本排序（或 desc 以便最新的在顶部）。您还可以使用此列，以便显示第一个更改、第二个更改等。
在插入和附加/更新（在适当情况下）之前，您需要检查这两个选项是否有更改。
现在您可能会说这是我获得的数据，我无法修改应用程序。如果是这样，那么您也许可以编写一个小型应用程序，计划每小时/每晚为您完成这项工作
IMO With this kind of problem I find it better to think of a different kind of model. The query you are asking for is not easy to write (if at all possible) and would be difficult to maintain moving forward if your underlying structure changes
So what options do you have?Option 1 Create a summary table of ACTUAL changes. Think of this table of a log/audit table where you only record a change when it happens. Then you can simply read from this table and out put results
Option 2
Create a
revisioncolumn on your main table. You would start with 1 for the first instance and only increase the revision if a change happens. e.g.Then you can get all revisions for an account that is greater than ZERO and ordered by revision (or desc so newest on top). You can also use this column so you can show 1st change, 2nd change etc.
Both options you would need to check for changes before you insert and append/update where appropiate.
Now you will probably say this is data I get and I cant modify the application. If so then you may be able to write a small application that is scheduled to do this work for you on a hourly/nightly basis