时间戳

首页 > 解决方案 > 从范围表中选择范围

sql - 从范围表中选择范围

问题描述

我有 2 个范围表ReceivedRanges和 DispatchedRanges.

ReceivedRanges
 From  - To
     1 - 100000
200000 - 300000
350000 - 400000


DispatchedRanges
     From  - To
     10000 - 50000
    250000 - 275000
    350000 - 400000

我想从以上 2 个表中选择新范围。输出范围为：

InventoryRanges
 From  - To
     1 - 9999
 50001 - 100000
200000 - 249999
275001 - 300000

如何从范围表中选择这些范围。

我试过的：

或者，我尝试将所有单独的接收到的序列号生成为sequence表格，并将已调度的编号标记为已调度为真。基于此表，我正在分组并能够检索InventoryRanges。但是对于这些，我需要存储所有序列号并在调度期间更新巨大的范围，这将减慢调度过程。

标签： sqlsql-server

解决方案

我认为这将涵盖您在 SQL 中查找范围之间差异的所有案例。希望这可以帮助你：

create table received_ranges(item_id int, [from] int, [to] int);
create table dispatched_ranges(item_id int, [from] int, [to] int);

insert into received_ranges (item_id,[from],[to]) values
(1, 1,5000),
(1, 7000,8000),
(2, 6000,9000),
(3, 10000,15000),
(4, 20000,25000);

insert into dispatched_ranges (item_id,[from],[to]) values
(1, 1,250),
(2, 6000,7250),
(2, 7500,8000),
(2, 8200, 9000),
(3, 12000,14000),
(4, 20000,25000);


with dispatched_batch(dispatched_batch_num, received_item, received_from, received_to, dispatched_item, dispatched_from, dispatched_to) as
(select row_number() over (partition by rr.item_id, rr.[from] order by rr.[from]) dispatched_batch_num,
rr.item_id as received_item,
rr.[from] as received_from,
rr.[to] as received_to,
dr.item_id as dispatched_item,
dr.[from] as dispatched_from,
dr.[to] as dispatched_to
from received_ranges rr
left join
dispatched_ranges dr
ON
rr.item_id = dr.item_id
AND
dr.[from] >= rr.[from]
AND
dr.[to] <= rr.[to])
select * from
(select
[current].[received_item],
case when [next].dispatched_batch_num is null then
       case when [current].[received_to] <> [current].[dispatched_to] then
              [current].dispatched_to + 1 
            else
              0
            end
     else
       case when [next].[dispatched_from] <> [current].[dispatched_to]+1 then
              [current].[dispatched_to] + 1
            else
              0 
            end
     end
      as 'inventory from',
case when [next].dispatched_batch_num is null then
       case when [current].[received_to] <> [current].[dispatched_to] then
              [current].received_to
            else
              0
            end
     else
       case when [next].[dispatched_from] <> [current].[dispatched_to]+1 then
              [next].[dispatched_from] - 1
            else
              0 
            end
     end
      as 'inventory to'

from dispatched_batch [current]
left join
dispatched_batch [next]
on
[current].received_item = [next].received_item
and 
[current].dispatched_batch_num + 1 = [next].dispatched_batch_num

UNION

select 
[current].received_item,
case when [current].[dispatched_from] is null then
       [current].[received_from]
       else
       case when [previous].dispatched_batch_num is null then
              case when [current].[received_from] <> [current].[dispatched_from] then
                     [current].received_from 
                   else
                     0
                   end
            else
              case when [previous].[dispatched_to] <> [current].[dispatched_from]+1 then
                     [previous].[dispatched_to] + 1
                   else
                     0 
                   end
            end
        end
      as 'inventory from',
case when [current].[dispatched_to] is null then
       [current].[received_to]
       else
       case when [previous].dispatched_batch_num is null then
              case when [current].[received_from] <> [current].[dispatched_from] then
                     [current].dispatched_from -1
                   else
                     0
                   end
            else
              case when [previous].[dispatched_to] <> [current].[dispatched_from]+1 then
                     [current].[dispatched_from] - 1
                   else
                     0 
                   end
            end
        end
      as 'inventory to'

from dispatched_batch [current]
left join
dispatched_batch previous
on
[current].received_item = previous.received_item
and 
[current].dispatched_batch_num  = previous.dispatched_batch_num + 1) result
where [inventory from] <> 0;

推荐阅读