首页 / 技术问答 / Sql问答列表 / 问答详情

Sql 如何根据行状态设置正确的标志

我想 for each 中继点标记客户没有收集的Package.

要识别留下的包裹(不是由客户领取)，状态需要为("Dropoff"或"preparation")

要求:

Flag Cases Example
The row needs to be the first row with status ID in (20,71) Rows 7,34
The row needs to be the first row with status ID in (20,71) Following a row with status in (50,51,90,91,30,60,10) Rows 3,10,27..

预期结果:

Line Number OrderNumber RelayPoint Status DateShipper F ID Status
2 XN75 NULL Shipment 1/27/2022 15:29 0 10
3 XN75 123 Dropoff 2/1/2022 7:15 1 20
4 XN75 123 Relay point Collection 2/2/2022 7:50 0 90
5 XN75 123 Collected 2/2/2022 7:50 0 91
6 XN75 45678 Shipper 2/2/2022 16:02 0 30
7 XN76 234 Dropoff 4/1/2022 9:16 1 20
8 XN76 234 Dropoff 4/1/2022 9:17 0 20
9 XN77 980 Shipper Delivery 9/22/2022 9:23 0 50
10 XN77 980 Preparation 9/29/2022 23:00 1 71
11 XN77 980 Shipper Delivery 10/1/2022 10:48 0 50
12 XN77 980 Preparation 10/11/2022 16:48 1 71
13 XN81 1314 Dropoff 10/10/2022 13:45 1 20
14 XN81 1314 Relay point Delivery 10/11/2022 13:46 0 51
15 XN81 1314 Preparation 10/11/2022 15:45 1 71
16 XN81 1314 Pickup 10/12/2022 8:46 0 60
17 XN79 NULL Shipment 9/25/2022 20:38 0 10
18 XN79 979 Dropoff 10/1/2022 9:16 1 20
19 XN79 979 Relay point Collection 10/3/2022 10:14 0 90
20 XN79 198271 Shipper 10/4/2022 15:05 0 30
21 XN79 154 Shipper Delivery 10/9/2022 10:42 0 50
22 XN79 154 Relay point Delivery 10/9/2022 10:43 0 51
23 XN79 154 Pickup 10/9/2022 14:08 0 60
24 XN79 711 Dropoff 10/10/2022 13:45 1 20
25 XN79 108223 Shipper 10/11/2022 13:45 0 30
26 XN80 980 Shipper Delivery 9/22/2022 9:23 0 50
27 XN80 980 Preparation 9/29/2022 23:00 1 71
28 XN80 980 Shipper Delivery 10/1/2022 10:48 0 50
29 XN80 980 Preparation 10/11/2022 16:48 1 71
30 XN80 980 Shipper Delivery 10/12/2022 10:48 0 50
31 XN80 980 Preparation 10/13/2022 16:48 1 71
32 XD30 340 Dropoff 11/17/2022 12:29 1 20
33 XD30 340 Pickup 11/17/2022 12:48 0 60
34 XD30 340 Dropoff 11/20/2022 18:24 1 20
35 XD30 340 Preparation 11/20/2022 18:25 0 71
36 XD30 340 Collected 11/20/2022 18:26 0 91

我try 了下面的查询，但没有给出正确的结果.

;WITH cte
     AS (SELECT *,Row_number() OVER (partition BY ordernumber, relaypoint ORDER BY dateshipper) AS RN
         FROM   #shipperstatushistory t1
         WHERE  status IN( 'Dropoff', 'preparation' )
                AND EXISTS (SELECT 1
                            FROM   #shipperstatushistory t2
                            WHERE  status IN( 'shipper Delivery','Relay point Delivery','pickup','announcement','Shipment', 'collected', 'shipper','Relay point Collection')
                                   AND t1.ordernumber = t2.ordernumber
                                   AND t1.relaypoint = t2.relaypoint
                                   AND t2.dateshipper > t1.dateshipper)
         UNION
         SELECT*,Row_number() OVER (partition BY ordernumber, relaypoint ORDER BY dateshipper) AS RN
         FROM   #shipperstatushistory t1
         WHERE  status IN( 'Dropoff', 'preparation' )
                AND NOT EXISTS (SELECT 1
                                FROM   #shipperstatushistory t2
                                WHERE  status IN( 'shipper Delivery','Relay point Delivery','pickup','announcement','Shipment', 'collected', 'shipper','Relay point Collection')
                                       AND t1.ordernumber = t2.ordernumber
                                       AND t1.relaypoint = t2.relaypoint
                                       AND t2.dateshipper > t1.dateshipper))
SELECT t.*,
       CASE WHEN t2.id IS NOT NULL THEN 1 ELSE 0 END AS f
FROM   #shipperstatushistory t
       LEFT JOIN cte t2
              ON t.id = t2.id
                 AND rn = 1

我的查询在OrderNumber XN81、XN80和XD30上返回错误的值

Here is a sqlFiddle个

谢谢你的帮助！

推荐答案

请考虑以下事项:

DECLARE @ShipperStatusHistory TABLE (id Int primary key identity, OrderNumber varchar(50), RelayPoint INT, Status varchar(50), DateShipper Datetime);
    
INSERT INTO @ShipperStatusHistory VALUES 
('XN75',NULL,'Shipment','2022-01-27 15:29:22.000'), ('XN75',123,'Dropoff','2022-02-01 07:15:53.000'), ('XN75',123,'Relay point Collection','2022-02-02 07:50:29.000'),
('XN75',123,'collected','2022-02-02 07:50:45.000'), ('XN75',45678,'shipper','2022-02-02 16:02:37.000'), ('XN76',234,'Dropoff','2022-04-01 09:16:51.000'),
('XN76',234,'Dropoff','2022-04-01 09:17:06.000'), ('XN77',980,'shipper Delivery','2022-09-22 09:23:35.000'), ('XN77',980,'Preparation','2022-09-29 23:00:06.000'),
('XN77',980,'shipper Delivery','2022-10-01 10:48:04.000'), ('XN77',980,'Preparation','2022-10-11 16:48:30.000'), ('XN81',1314,'Dropoff','2022-10-10 13:45:59.000'),
('XN81',1314,'Relay point Delivery','2022-10-11 13:46:59.000'), ('XN81',1314,'Preparation','2022-10-11 15:45:59.000'), ('XN81',1314,'Pickup','2022-10-12 08:46:59.000'),
('XN79',NULL,'Shipment','2022-09-25 20:38:19.000'), ('XN79',979,'Dropoff','2022-10-01 09:16:51.000'), ('XN79',979,'Relay point Collection','2022-10-03 10:14:06.000'),
('XN79',198271,'shipper','2022-10-04 15:05:30.000'), ('XN79',154,'shipper Delivery','2022-10-09 10:42:01.000'), ('XN79',154,'Relay point Delivery','2022-10-09 10:43:46.000'),
('XN79',154,'Pickup','2022-10-09 14:08:50.000'), ('XN79',711,'Dropoff','2022-10-10 13:45:59.000'), ('XN79',108223,'shipper','2022-10-11 13:45:59.000'),
('XN80',980,'shipper Delivery','2022-09-22 09:23:35.000'), ('XN80',980,'Preparation','2022-09-29 23:00:06.000'), ('XN80',980,'shipper Delivery','2022-10-01 10:48:04.000'),
('XN80',980,'Preparation','2022-10-11 16:48:30.000'), ('XN80',980,'shipper Delivery','2022-10-12 10:48:04.000'), ('XN80',980,'Preparation','2022-10-13 16:48:30.000'),
('XD30',340,'Dropoff','2022-11-17 12:29:29.000'), ('XD30',340,'Pickup','2022-11-17 12:48:43.000'), ('XD30',340,'Dropoff','2022-11-20 18:24:01.000'), ('XD30',340,'Preparation','2022-11-20 18:25:12.000'),
('XD30',340,'Collected','2022-11-20 18:26:10.000');

DECLARE @Expected TABLE (LineNumber INT, OrderNumber VARCHAR(4), RelayPoint VARCHAR(20), Status VARCHAR(50), DateShipper DATETIME, F BIT, IDStatus INT);
INSERT INTO @Expected (LineNumber, OrderNumber, RelayPoint, Status, DateShipper, F, IDStatus) VALUES
(2 ,'XN75', NULL , 'Shipment ', '1/27/2022 15:29 ', 0 , 10), (3 ,'XN75', 123 , 'Dropoff', '2/1/2022 7:15 ', 1 , 20), (4 ,'XN75', 123 , 'Relay point Collection ', '2/2/2022 7:50 ', 0 , 90),
(5 ,'XN75', 123 , 'Collected ', '2/2/2022 7:50 ', 0 , 91), (6 ,'XN75', 45678 , 'Shipper', '2/2/2022 16:02 ', 0 , 30), (7 ,'XN76', 234 , 'Dropoff ', '4/1/2022 9:16 ', 1 , 20), 
(8 ,'XN76', 234 , 'Dropoff ', '4/1/2022 9:17 ', 0 , 20), (9 ,'XN77', 980 , 'Shipper Delivery ', '9/22/2022 9:23 ', 0 , 50), (10 ,'XN77', 980 , 'Preparation ', '9/29/2022 23:00 ', 1 , 71), 
(11 ,'XN77', 980 , 'Shipper Delivery ', '10/1/2022 10:48 ', 0 , 50), (12 ,'XN77', 980 , 'Preparation ', '10/11/2022 16:48 ', 1 , 71), (13 ,'XN81', 1314 , 'Dropoff ', '10/10/2022 13:45 ', 1 , 20), 
(14 ,'XN81', 1314 , 'Relay point Delivery ', '10/11/2022 13:46 ', 0 , 51), (15 ,'XN81', 1314 , 'Preparation ', '10/11/2022 15:45 ', 1 , 71), (16 ,'XN81', 1314 , 'Pickup ', '10/12/2022 8:46 ', 0 , 60), 
(17 ,'XN79', NULL , 'Shipment ', '9/25/2022 20:38 ', 0 , 10), (18 ,'XN79', 979 , 'Dropoff ', '10/1/2022 9:16 ', 1 , 20), (19 ,'XN79', 979 , 'Relay point Collection ', '10/3/2022 10:14 ', 0 , 90), 
(20 ,'XN79', 198271 , 'Shipper ', '10/4/2022 15:05 ', 0 , 30), (21 ,'XN79', 154 , 'Shipper Delivery ', '10/9/2022 10:42 ', 0 , 50), (22 ,'XN79', 154 , 'Relay point Delivery ', '10/9/2022 10:43 ', 0 , 51), 
(23 ,'XN79', 154 , 'Pickup ', '10/9/2022 14:08 ', 0 , 60), (24 ,'XN79', 711 , 'Dropoff ', '10/10/2022 13:45 ', 1 , 20), (25 ,'XN79', 108223 , 'Shipper ', '10/11/2022 13:45 ', 0 , 30), 
(26 ,'XN80', 980 , 'Shipper Delivery ', '9/22/2022 9:23 ', 0 , 50), (27 ,'XN80', 980 , 'Preparation ', '9/29/2022 23:00 ', 1 , 71), (28 ,'XN80', 980 , 'Shipper Delivery ', '10/1/2022 10:48 ', 0 , 50), 
(29 ,'XN80', 980 , 'Preparation ', '10/11/2022 16:48 ', 1 , 71), (30 ,'XN80', 980 , 'Shipper Delivery ', '10/12/2022 10:48 ', 0 , 50), (31 ,'XN80', 980 , 'Preparation ', '10/13/2022 16:48 ', 1 , 71), 
(32 ,'XD30', 340 , 'Dropoff ', '11/17/2022 12:29 ', 1 , 20), (33 ,'XD30', 340 , 'Pickup ', '11/17/2022 12:48 ', 0 , 60), (34 ,'XD30', 340 , 'Dropoff ', '11/20/2022 18:24 ', 1 , 20), 
(35 ,'XD30', 340 , 'Preparation ', '11/20/2022 18:25 ', 0 , 71), (36 ,'XD30', 340 , 'Collected ', '11/20/2022 18:26 ', 0 , 91);

UPDATE @Expected SET LineNumber = LineNumber - 1;

SELECT s.*,
       CASE WHEN s.Status IN ('Dropoff','Preparation') AND ROW_NUMBER() OVER (PARTITION BY s.OrderNumber, CASE WHEN s.Status IN ('Dropoff','Preparation') THEN 1 ELSE 0 END ORDER BY s.DateShipper) =  1 THEN 1
            WHEN LAG(s.Status,1) OVER (PARTITION BY s.OrderNumber ORDER BY s.DateShipper) IN ('shipper Delivery', 'Relay point Delivery', 'Pickup', 'announcement', 
                                                                                              'Shipment', 'collected', 'shipper', 'Relay point Collection') 
                 AND s.Status IN ('Dropoff','Preparation') THEN 1 
            ELSE 0
       END AS Flag, e.F
  FROM @ShipperStatusHistory s
    LEFT OUTER JOIN @Expected e 
      ON s.id = e.LineNumber
 ORDER BY s.Id;

为了便于比较，我已将您的预期yields 包括在LEFT OUTER JOIN中.我们不知道您打算在第二个条件中使用哪些ID，因此所有8个ID都在其中，但您可以很容易地对其进行调整.

id OrderNumber RelayPoint Status DateShipper Flag F
1 XN75 Shipment 2022-01-27 15:29:22.000 0 0
2 XN75 123 Dropoff 2022-02-01 07:15:53.000 1 1
3 XN75 123 Relay point Collection 2022-02-02 07:50:29.000 0 0
4 XN75 123 collected 2022-02-02 07:50:45.000 0 0
5 XN75 45678 shipper 2022-02-02 16:02:37.000 0 0
6 XN76 234 Dropoff 2022-04-01 09:16:51.000 1 1
7 XN76 234 Dropoff 2022-04-01 09:17:06.000 0 0
8 XN77 980 shipper Delivery 2022-09-22 09:23:35.000 0 0
9 XN77 980 Preparation 2022-09-29 23:00:06.000 1 1
10 XN77 980 shipper Delivery 2022-10-01 10:48:04.000 0 0
11 XN77 980 Preparation 2022-10-11 16:48:30.000 1 1
12 XN81 1314 Dropoff 2022-10-10 13:45:59.000 1 1
13 XN81 1314 Relay point Delivery 2022-10-11 13:46:59.000 0 0
14 XN81 1314 Preparation 2022-10-11 15:45:59.000 1 1
15 XN81 1314 Pickup 2022-10-12 08:46:59.000 0 0
16 XN79 Shipment 2022-09-25 20:38:19.000 0 0
17 XN79 979 Dropoff 2022-10-01 09:16:51.000 1 1
18 XN79 979 Relay point Collection 2022-10-03 10:14:06.000 0 0
19 XN79 198271 shipper 2022-10-04 15:05:30.000 0 0
20 XN79 154 shipper Delivery 2022-10-09 10:42:01.000 0 0
21 XN79 154 Relay point Delivery 2022-10-09 10:43:46.000 0 0
22 XN79 154 Pickup 2022-10-09 14:08:50.000 0 0
23 XN79 711 Dropoff 2022-10-10 13:45:59.000 1 1
24 XN79 108223 shipper 2022-10-11 13:45:59.000 0 0
25 XN80 980 shipper Delivery 2022-09-22 09:23:35.000 0 0
26 XN80 980 Preparation 2022-09-29 23:00:06.000 1 1
27 XN80 980 shipper Delivery 2022-10-01 10:48:04.000 0 0
28 XN80 980 Preparation 2022-10-11 16:48:30.000 1 1
29 XN80 980 shipper Delivery 2022-10-12 10:48:04.000 0 0
30 XN80 980 Preparation 2022-10-13 16:48:30.000 1 1
31 XD30 340 Dropoff 2022-11-17 12:29:29.000 1 1
32 XD30 340 Pickup 2022-11-17 12:48:43.000 0 0
33 XD30 340 Dropoff 2022-11-20 18:24:01.000 1 1
34 XD30 340 Preparation 2022-11-20 18:25:12.000 0 0
35 XD30 340 Collected 2022-11-20 18:26:10.000 0 0

Sql相关问答推荐

SQL更新，在2个额外的表上使用内部连接

如何在Snowflake SQL存储过程中传递LIMIT和OFFSET的参数？

更新在两个或多个面中具有交点的面

SQL查询每个客户的最新条目

PostgreSQL 9.6嵌套的INSERT/RETURN语句的CTE性能低得令人无法接受

PostgreSQL：按小时查看调整日期

导出部分条形码字符串GS1-128

如何为该查询编写正确分区依据

PATINDEX中与[A-Z]匹配(U除外)的正则表达式

从重复值中获取最新值

如何在android房间中进行多个加入

Select 组中的第一行，但在并发环境中

根据要过滤的列的值进行联接和分组

计数时如何为所有时间间隔返回 0 而不是什么都不返回

joins 组合多个重复数据删除策略

为 sqlite 全文搜索 (fts) 创建触发器时出现虚拟表的不安全使用

SQL查询以获取从特定可变日期看到的用户

SQL 计数和过滤查询优化

Postgres 窗口函数未按预期工作

删除具有相同 ID 的重复记录 - Postgresql

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630