MW211 EXIT

devlog
SQL/先入先出法の在庫シミュレーション
2020年06月20日
入庫レコードと出庫レコードを先入先出法で紐づける場合には
出庫側を両者の重複区間とすればよい。(例はMSSQL)
┌──────────────────────────────────────┐
│WITH [入庫D]([ID],[数]) AS (                                               │
│         SELECT * FROM (VALUES (1, 3),                                      │
│                               (2, 3),                                      │
│                               (3, 3)) AS [入庫D]([ID],[数])               │
│     ),                                                                     │
│     [出庫D]([ID],[数]) AS (                                               │
│         SELECT * FROM (VALUES (1, 1),                                      │
│                               (2, 4)) AS [出庫D]([ID],[数])               │
│     )                                                                      │
│SELECT [入庫D].[ID] AS [入庫ID],                                           │
│       [出庫D].[ID] AS [出庫ID],                                           │
│       CASE                                                                 │
│         WHEN [出庫D].[始] < [入庫D].[始] THEN [入庫D].[始]              │
│         ELSE                                    [出庫D].[始]              │
│       END AS [始],                                                         │
│       CASE                                                                 │
│         WHEN [出庫D].[終] > [入庫D].[終] THEN [入庫D].[終]              │
│         ELSE                                    [出庫D].[終]              │
│       END AS [終]                                                          │
│    FROM (                                                                  │
│        SELECT [ID],                                                        │
│               ISNULL((SELECT SUM([数])                                     │
│                           FROM [入庫D] AS [他D]                          │
│                           WHERE [他D].[ID] < [入庫D].[ID]),              │
│                      0) + 1 AS [始],                                       │
│               (SELECT SUM([数])                                            │
│                    FROM [入庫D] AS [他D]                                 │
│                    WHERE [他D].[ID] <= [入庫D].[ID]) AS [終]             │
│            FROM [入庫D]                                                   │
│    ) AS [入庫D]                                                           │
│        LEFT JOIN (                                                         │
│            SELECT [ID],                                                    │
│                   ISNULL((SELECT SUM([数])                                 │
│                               FROM [出庫D] AS [他D]                      │
│                               WHERE [他D].[ID] < [出庫D].[ID]),          │
│                          0) + 1 AS [始],                                   │
│                   (SELECT SUM([数])                                        │
│                        FROM [出庫D] AS [他D]                             │
│                        WHERE [他D].[ID] <= [出庫D].[ID]) AS [終]         │
│                FROM [出庫D]                                               │
│        ) AS [出庫D]                                                       │
│          ON  [出庫D].[始] <= [入庫D].[終]                                │
│          AND [出庫D].[終] >= [入庫D].[始]                                │
│    ORDER BY [入庫D].[ID] ASC,                                             │
│             [出庫D].[ID] ASC;                                             │
└──────────────────────────────────────┘
分類:SQL