TSQL 检查特定行序列是否存在 [英] TSQL check if specific rows sequence exists

问题描述

我有一个包含字段的表:

I have a table containing fields:

History_ID   ORDER_ID   Previous_STATUS_ID   Next_STATUS_ID
0            2          null                 1
1            2          1                    2
2            2          2                    3
3            1          null                 1
4            2          3                    4
5            3          null                 2
6            2          4                    1
7            2          1                    2
8            2          2                    5
9            7          3                    4

10 4 6 211 9 3 5

10 4 6 2 11 9 3 5

它包含我的订单ID 和ID 状态.基本上表格包含每个订单的 STATUS 历史记录.

It contains my order ID and ID of status. Basically table contains history of STATUS for every order.

我想检查一个特定的订单是否有特定的订单顺序.

I would like to check if a specific order has a specific order sequence.

示例:如果订单 ID 4 有状态 2,4,3,1 然后返回 true

Example: if order id 4 had states 2,4,3,1 then return true

状态应该是一个接一个，所以2之后必须是4，4之后是3，之后是1.

Statuses should be one after other so after 2 must be 4, after 4 is 3 and after that 1.

这些序列为我提供了有关特定订单流的信息(将显示在我的报告中).

Those sequences give me information (which will be displayed in my report) about flow of specific order.

我需要检查那种序列:

• 1,2,3
• 1,4,3,2
• 4,2
• (1 或 2 或 3 或 4),5,3 - 4、5 和 3 之一

我不知道如何从这种查询开始:/

I don't have idea how to start with that kind of query :/

我的最终报告是一个包含订单信息的表格，如下所示:

My final report is a table containing information about orders like so:

Orders type  Count      ...
Profile1     145        ...
Profile2     217        ...
Profile3     12         ...
Profile4     2          ...

我有大约 800k+ 的订单，我的报告必须每天完成，所以我决定创建一个报告表，而不是为整个表做报告(检查那些 STATES 并汇总其他表的信息):

I have about 800k+ orders and my report must be done every day, so instead doing report for whole table (checking those STATES and aggregating informations from other tables) I have decided to create a report table:

Order_ID   Profile   Order_date   Customer_profile     ...
1          5        '2008-01-03'  2
4          1        '2009-04-10'  3
3          7        '2011-11-03'  1
4523       2        '2012-11-03'  5

这样我就可以创建一个夜间工作，用我的信息将新订单添加到这个表中，我将从该表中做一个简单的选择，以避免聚合.第一次插入会很大，但在第二天晚上它只会添加新订单.

So that I can create a night job that will add new orders to this table with my information and I will just do a simple select from that table, to avoid aggregation. First insert will be big, but in next night it will only add new orders.

例如查看 ID = 2 的订单
Next_STATUS_ID ale like so: 1,2,3,4,1,2,5...按该顺序查找这些 ID.

For example look at order with ID = 2
Next_STATUS_ID ale like so: 1,2,3,4,1,2,5... I want to check if this order has history change like 1,2,5, so my function (select statement) should repeat 1 if it finds those ID's in that order.

推荐答案

我认为这可以使用递归 CTE 来解决:

I think this can be solved using a recursive CTE:

with change_tree as 
(
  SELECT order_id,
         previous_status_id, 
         next_status_id,
         cast(next_status_id as varchar(max)) as status_path
  FROM status_change
  WHERE previous_status_id = 1
    AND order_id = 2

  UNION ALL 

  SELECT sc.order_id,
         sc.previous_status_id,
         sc.next_status_id,
         ct.status_path + ',' + cast(sc.next_status_id as varchar(max))
  FROM status_change sc 
    JOIN change_tree ct ON ct.next_status_id = sc.previous_status_id AND ct.order_id = sc.order_id
)
SELECT *
FROM change_tree
WHERE status_path = '2,3,5';

这本质上是(递归地)将 next_status_id 的所有值连接到一个可以比较的字符串.

What this essentially does is to (recursively) concatenate all values of next_status_id to a single string that you can compare to.

根据您的示例数据，我不清楚哪一行标记了订单状态更改历史记录的开始".您需要调整并集第一部分中的 where 以选择正确的起始"行.

From your sample data it's not clear to me which row marks the "beginning" of an order status change history. You will need to adjust the where in the first part of the union to select the correct "starting" row.

这篇关于TSQL 检查特定行序列是否存在的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！