搜索
搜索

使用日期和罐名称的递归T-SQL查询

Rick Savoy 发表于 Dev
13
里克·萨沃伊（Rick Savoy）

好的，这是一个故事：我在一家将危险液体化学物质回收为有用化学物质的公司工作。此过程的一部分涉及在回收过程的每个部分完成后将这些物料从一个储罐移至另一个储罐。这些罐位于我们的罐区中，是蒸馏系统的一部分，位于有轨车或油罐车中。我被分配的任务是编写一个查询，该查询使经理可以拿起任何储罐，并跟踪物料在每个储罐中的移动，直到将其处置或出售为止。该数据库是在大约10年前建立的，那是我的时间，因此数据就是我所需要的（即，无法对其进行更改以使其更易于使用）。

现在，表结构是：适合此工作的列为：

  • 坦克
  • OpenDate（空罐开始接收物料的日期）
  • CosedDate（容器关闭，清空和准备使用新材料的日期）
  • DestinationTank（将物料移至的储罐）
  • 产品名称（我添加了此信息以验证运动。许多材料不能混合。有些是。如果算法无法正常工作，则不能混合的材料可能会出现问题。）

所有储罐都有一个OpenDate，但是仍在装满或等待运出的储罐将没有关闭日期。通常，储罐与化学药品无关，例如，任何空罐均可用于任何化学药品。可以预期从1到10个动作。

我尝试使用递归CTE T-SQL算法，但是在尝试将SourceTank与匹配的DestinationTank匹配时很快迷失了，其中SourceTank的CosedDate在DestinationTank的OpenDate和CosedDate之间，或者至少在等于或等于OpenTate的OpenDate之后DestinationTank（如果尚未关闭）。

样本数据：

SourceTank  StartDate    ClosedDate   DestinationTank   ProductName  
------------------------------------------------------------------------
TNK01-5     08/03/2017   08/10/2017   TNK30-6           Fuels  
TNK01-5     08/07/2017   08/10/2017   TNK40-6           Fuels  
TNK01-5     07/20/2017   07/31/2017   TNK01-5           Incin  
TNK01-5     08/10/2017   08/17/2017   TNK30-6           Incin  
TNK01-5     08/12/2017   08/17/2017   TNK30-6           Fuels  
TNK03-5     08/13/2017   08/22/2017   TNK30-6           IBAC feed  
TNK07-5     08/11/2017   08/17/2017   TNK40-6           Incin  
TNK07-5     08/14/2017   08/29/2017   TNK40-6           Fuels  
TNK07-5     07/15/2017   08/10/2017   TNK30-6           Picoline Cut  
TNK07-5     08/03/2017   08/10/2017   TNK02-5           Pico 2nd Pass  
TNK07-5     08/06/2017   08/17/2017   TNK40-6           Fuels  
TNK08-5     08/05/2017   08/10/2017   TNK40-6           Fuels  
TNK08-5     08/07/2017   08/08/2017   TNK30-6           Fuels  
TNK08-5     08/10/2017   08/22/2017   TNK40-6           Water  
TNK08-5     07/24/2017   08/10/2017   TNK02-5           Picoline Cut  
TNK09-10    07/20/2017   NULL         TNK30-6           Picoline Crude  
TNK09-10    07/21/2017   08/04/2017   TNK30-6           Picoline Crude  
TNK09-10    08/02/2017   08/10/2017   TNK30-6           Cyclo Waste  
TNK09-10    08/05/2017   08/10/2017   TNK30-6           Cyclo Waste  
TNK09-10    08/07/2017   08/10/2017   TNK30-6           Cyclo Waste  
TNK09-10    08/04/2017   08/10/2017   TNK30-6           Cyclo Waste  
TNK09-10    08/15/2017   08/22/2017   TNK30-6           IBAC feed  
TNK09-10    08/11/2017   08/17/2017   TNK30-6           IBAC feed  
TNK09-10    08/12/2017   08/17/2017   TNK30-6           IBAC feed  
TNK30-6     08/08/2017   08/29/2017   TNK30-6           Cyclo Waste  
TNK40-6     08/13/2017   08/22/2017   TNK30-6           IBAC:PRODUCT  
TNK41-6     08/14/2017   09/27/2017   NATX25496         IBAC:PRODUCT  
TNK51-12    07/26/2017   09/15/2017   TNK30-6           CYCLO Product  
TNK62-12    07/28/2017   09/12/2017   TNK30-6           NON-RCRA NMP  
TNK74-12    07/29/2017   NULL         TNK30-6           Picoline Crude  
TNK91-8     08/03/2017   08/22/2017   TNK08-5           Picoline Prod

这是我尝试过的。不知道如何处理NULL ClosingDate。

WITH TanksCTE AS (
SELECT [TrackingNum]
      ,[TblTankTracking].[TankID]
      ,[StartDate]
      ,[TblTankTracking].[ProductID]
      ,[ClosedDate]
      ,[Destination]
  FROM [MAFTS].[Laboratory].[TblTankTracking]

UNION ALL  

SELECT TCTE.[TrackingNum]
      ,TRACK.[TankID]
      ,TRACK.[StartDate]
      ,TRACK.[ProductID]
      ,TRACK.[ClosedDate]
      ,TRACK.[Destination]
  FROM TanksCTE AS TCTE INNER JOIN 
       [MAFTS].[Laboratory].[TblTankTracking]  TRACK
       ON  TCTE.[TankID] = TRACK.[Destination] 
       AND TRACK.[ClosedDate] BETWEEN  TCTE.[StartDate] AND TCTE.[ClosedDate]
)

SELECT * FROM TanksCTE
    option (maxrecursion 0)
Used_By_Already

我不确定这会有多大用处，因为我不知道预期的结果会是什么样，但它可能会有所帮助。我试图弄清日期，所以这就是为什么您会看到它们串联在“途径”列中的原因。我也不确定从哪里开始，所以我猜测每个坦克的最早开始日期。

SQL小提琴

MS SQL Server 2014架构设置：

CREATE TABLE Table1
    ([SourceTank] varchar(8), [StartDate] datetime, [ClosedDate] datetime, [DestinationTank] varchar(9), [ProductName] varchar(14))
;

INSERT INTO Table1
    ([SourceTank], [StartDate], [ClosedDate], [DestinationTank], [ProductName])
VALUES
    ('TNK01-5', '2017-08-03 00:00:00', '08/10/2017', 'TNK30-6', 'Fuels'),
    ('TNK01-5', '2017-08-07 00:00:00', '08/10/2017', 'TNK40-6', 'Fuels'),
    ('TNK01-5', '2017-07-20 00:00:00', '07/31/2017', 'TNK01-5', 'Incin'),
    ('TNK01-5', '2017-08-10 00:00:00', '08/17/2017', 'TNK30-6', 'Incin'),
    ('TNK01-5', '2017-08-12 00:00:00', '08/17/2017', 'TNK30-6', 'Fuels'),
    ('TNK03-5', '2017-08-13 00:00:00', '08/22/2017', 'TNK30-6', 'IBAC feed'),
    ('TNK07-5', '2017-08-11 00:00:00', '08/17/2017', 'TNK40-6', 'Incin'),
    ('TNK07-5', '2017-08-14 00:00:00', '08/29/2017', 'TNK40-6', 'Fuels'),
    ('TNK07-5', '2017-07-15 00:00:00', '08/10/2017', 'TNK30-6', 'Picoline Cut'),
    ('TNK07-5', '2017-08-03 00:00:00', '08/10/2017', 'TNK02-5', 'Pico 2nd Pass'),
    ('TNK07-5', '2017-08-06 00:00:00', '08/17/2017', 'TNK40-6', 'Fuels'),
    ('TNK08-5', '2017-08-05 00:00:00', '08/10/2017', 'TNK40-6', 'Fuels'),
    ('TNK08-5', '2017-08-07 00:00:00', '08/08/2017', 'TNK30-6', 'Fuels'),
    ('TNK08-5', '2017-08-10 00:00:00', '08/22/2017', 'TNK40-6', 'Water'),
    ('TNK08-5', '2017-07-24 00:00:00', '08/10/2017', 'TNK02-5', 'Picoline Cut'),
    ('TNK09-10', '2017-07-20 00:00:00', NULL, 'TNK30-6', 'Picoline Crude'),
    ('TNK09-10', '2017-07-21 00:00:00', '08/04/2017', 'TNK30-6', 'Picoline Crude'),
    ('TNK09-10', '2017-08-02 00:00:00', '08/10/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK09-10', '2017-08-05 00:00:00', '08/10/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK09-10', '2017-08-07 00:00:00', '08/10/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK09-10', '2017-08-04 00:00:00', '08/10/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK09-10', '2017-08-15 00:00:00', '08/22/2017', 'TNK30-6', 'IBAC feed'),
    ('TNK09-10', '2017-08-11 00:00:00', '08/17/2017', 'TNK30-6', 'IBAC feed'),
    ('TNK09-10', '2017-08-12 00:00:00', '08/17/2017', 'TNK30-6', 'IBAC feed'),
    ('TNK30-6', '2017-08-08 00:00:00', '08/29/2017', 'TNK30-6', 'Cyclo Waste'),
    ('TNK40-6', '2017-08-13 00:00:00', '08/22/2017', 'TNK30-6', 'IBAC:PRODUCT'),
    ('TNK41-6', '2017-08-14 00:00:00', '09/27/2017', 'NATX25496', 'IBAC:PRODUCT'),
    ('TNK51-12', '2017-07-26 00:00:00', '09/15/2017', 'TNK30-6', 'CYCLO Product'),
    ('TNK62-12', '2017-07-28 00:00:00', '09/12/2017', 'TNK30-6', 'NON-RCRA NMP'),
    ('TNK74-12', '2017-07-29 00:00:00', NULL, 'TNK30-6', 'Picoline Crude'),
    ('TNK91-8', '2017-08-03 00:00:00', '08/22/2017', 'TNK08-5', 'Picoline Prod')
;

查询1：

with TankStart as (
    select 
           *
         , row_number() over(partition by SourceTank order by StartDate ASC) as rn
    from Table1
    )
, TankPaths as (
      SELECT
            T.SourceTank
          , T.DestinationTank
          , T.StartDate
          , T.ClosedDate
          , T.ProductName
          , CAST(T.DestinationTank AS varchar(max)) 
            + ' '  
            + convert(varchar(10),T.ClosedDate,120)
            AS Pathway
      FROM TankStart T
      WHERE T.rn = 1
      union all
      SELECT
            T1.SourceTank
          , T1.DestinationTank
          , T1.StartDate
          , T1.ClosedDate
          , T1.ProductName
          , M.Pathway 
            + ' '  
            + convert(varchar(10),T1.StartDate,120)
            + ', ' 
            + CAST(T1.DestinationTank AS varchar(max)) 
            + ', ' 
            + convert(varchar(10),T1.ClosedDate,120)

      FROM Table1 T1
      INNER JOIN TankPaths M ON M.DestinationTank = T1.SourceTank
                       AND M.ClosedDate <= T1.StartDate
      where T1.SourceTank <> T1.DestinationTank
    )
select * 
from TankPaths
order by 1, 2, Pathway

结果：

| SourceTank | DestinationTank |            StartDate |           ClosedDate |    ProductName |                                                                            Pathway |
|------------|-----------------|----------------------|----------------------|----------------|------------------------------------------------------------------------------------|
|    TNK01-5 |         TNK01-5 | 2017-07-20T00:00:00Z | 2017-07-31T00:00:00Z |          Incin |                                                                 TNK01-5 2017-07-31 |
|    TNK01-5 |         TNK30-6 | 2017-08-03T00:00:00Z | 2017-08-10T00:00:00Z |          Fuels |                                 TNK01-5 2017-07-31 2017-08-03, TNK30-6, 2017-08-10 |
|    TNK01-5 |         TNK30-6 | 2017-08-10T00:00:00Z | 2017-08-17T00:00:00Z |          Incin |                                 TNK01-5 2017-07-31 2017-08-10, TNK30-6, 2017-08-17 |
|    TNK01-5 |         TNK30-6 | 2017-08-12T00:00:00Z | 2017-08-17T00:00:00Z |          Fuels |                                 TNK01-5 2017-07-31 2017-08-12, TNK30-6, 2017-08-17 |
|    TNK01-5 |         TNK40-6 | 2017-08-07T00:00:00Z | 2017-08-10T00:00:00Z |          Fuels |                                 TNK01-5 2017-07-31 2017-08-07, TNK40-6, 2017-08-10 |
|    TNK03-5 |         TNK30-6 | 2017-08-13T00:00:00Z | 2017-08-22T00:00:00Z |      IBAC feed |                                                                 TNK30-6 2017-08-22 |
|    TNK07-5 |         TNK30-6 | 2017-07-15T00:00:00Z | 2017-08-10T00:00:00Z |   Picoline Cut |                                                                 TNK30-6 2017-08-10 |
|    TNK08-5 |         TNK02-5 | 2017-07-24T00:00:00Z | 2017-08-10T00:00:00Z |   Picoline Cut |                                                                 TNK02-5 2017-08-10 |
|   TNK09-10 |         TNK30-6 | 2017-07-20T00:00:00Z |               (null) | Picoline Crude |                                                                             (null) |
|    TNK30-6 |         TNK30-6 | 2017-08-08T00:00:00Z | 2017-08-29T00:00:00Z |    Cyclo Waste |                                                                 TNK30-6 2017-08-29 |
|    TNK40-6 |         TNK30-6 | 2017-08-13T00:00:00Z | 2017-08-22T00:00:00Z |   IBAC:PRODUCT | TNK01-5 2017-07-31 2017-08-07, TNK40-6, 2017-08-10 2017-08-13, TNK30-6, 2017-08-22 |
|    TNK40-6 |         TNK30-6 | 2017-08-13T00:00:00Z | 2017-08-22T00:00:00Z |   IBAC:PRODUCT |                                                                 TNK30-6 2017-08-22 |
|    TNK41-6 |       NATX25496 | 2017-08-14T00:00:00Z | 2017-09-27T00:00:00Z |   IBAC:PRODUCT |                                                               NATX25496 2017-09-27 |
|   TNK51-12 |         TNK30-6 | 2017-07-26T00:00:00Z | 2017-09-15T00:00:00Z |  CYCLO Product |                                                                 TNK30-6 2017-09-15 |
|   TNK62-12 |         TNK30-6 | 2017-07-28T00:00:00Z | 2017-09-12T00:00:00Z |   NON-RCRA NMP |                                                                 TNK30-6 2017-09-12 |
|   TNK74-12 |         TNK30-6 | 2017-07-29T00:00:00Z |               (null) | Picoline Crude |                                                                             (null) |
|    TNK91-8 |         TNK08-5 | 2017-08-03T00:00:00Z | 2017-08-22T00:00:00Z |  Picoline Prod |                                                                 TNK08-5 2017-08-22 |

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

使用 PostgreSQL 的递归 SQL 查询

SQL累积和日期查询

潜在的递归SQL查询

SQL递归查询错误

SQL-递归查询

SQL Server递归查询

递归的Postgre / SQL查询

递归SQL关系查询

SQL 递归查询 Postgres

简单的递归 SQL 查询

SQL查询汇总日期

查询SQL日期

SQL查询搜索日期

SQL 查询 - 使用多个 AS 设置 SQL 查询

使用 sql 查询显示团队名称和父名称

SQL查询条件和或

SQL查询-如何查询递归键

SQL查询以获取最大日期的商家名称

更新查询-T SQL

T-SQL递归查询-数据沿袭

在SQL查询中使用Not in

使用python查询SQL

使用别名的 SQL 查询

使用Join的Sql Not IN查询

使用NULL的SQL查询

使用group by的sql查询

使用ActiveRecord的SQL查询

使用sum（）的SQL查询

使用count（*）的SQL查询

TOP 榜单

  1. 1

    Android Studio Kotlin：提取为常量

  2. 2

    计算数据帧R中的字符串频率

  3. 3

    如何使用Redux-Toolkit重置Redux Store

  4. 4

    http：// localhost：3000 /＃！/为什么我在localhost链接中得到“＃！/”。

  5. 5

    如何使用tweepy流式传输来自指定用户的推文（仅在该用户发布推文时流式传输）

  6. 6

    TreeMap中的自定义排序

  7. 7

    TYPO3：将 Formhandler 添加到新闻扩展

  8. 8

    遍历元素数组以每X秒在浏览器上显示

  9. 9

    在Ubuntu和Windows中，触摸板有时会滞后。硬件问题？

  10. 10

    警告消息：在matrix（unlist（drop.item），ncol = 10，byrow = TRUE）中：数据长度[16]不是列数的倍数[10]>？

  11. 11

    无法连接网络并在Ubuntu 14.04中找到eth0

  12. 12

    将辅助轴原点与主要轴对齐

  13. 13

    我可以ping IPv6但不能ping IPv4

  14. 14

    在Jenkins服务器中使用Selenium和Ruby进行的黄瓜测试失败，但在本地计算机中通过

  15. 15

    提交html表单时为空

  16. 16

    使用C ++ 11将数组设置为零

  17. 17

    如果从DB接收到的值为空，则JMeter JDBC调用将返回该值作为参数名称

  18. 18

    尝试在Dell XPS13 9360上安装Windows 7时出错

  19. 19

    如何在R中转置数据

  20. 20

    无法使用 envoy 访问 .ssh/config

  21. 21

    未捕获的SyntaxError：带有Ajax帖子的意外令牌u

热门标签

归档