SQL题目8：关于车辆和货物组合问题

with t1 as   ---货车表数据
(select 1 as 顺序, 1001 as 车队编号, '张三' as 车主姓名, 10 as 载货量
  from dual
union all
select 2, 1001, '李四', 15
  from dual
union all
select 3, 1001, '王五', 10
  from dual
union all
select 4, 1001, '马六', 30
  from dual
union all
select 1, 1002, '赵七', 35
  from dual
union all
select 2, 1002, '钱八', 75
  from dual
union all
select 1, 1003, '孙九', 100 from dual
)
,t2 as   ----货物堆数据
(
select 1 as 顺序, 1001 as 车队编号, 'a' as 货物堆编号, 20 as 货物量
  from dual
union all
select 2, 1001, 'b', 8
  from dual
union all
select 3, 1001, 'c', 12
  from dual
union all
select 4, 1001, 'd', 7
  from dual
union all
select 5, 1001, 'e', 9
  from dual
union all
select 6, 1001, 'f', 9
  from dual
union all
select 1, 1002, 'g', 10
  from dual
union all
select 2, 1002, 'h', 80
  from dual
union all
select 3, 1002, 'i', 20
  from dual
union all
select 1, 1003, 'j', 100 from dual
)
,t3 as  ----从这里开始逻辑处理，对两表数据求累计和和标记
(
select  t.*,sum(载货量)over(partition by 车队编号 order by 顺序) as 累计载货,1 as flag
from t1 t
union all
select  t.*,sum(货物量)over(partition by 车队编号 order by 顺序) as 累计载货,0 as flag
from t2 t
),t4 as   --对数据进行求差，
(
select t.*,t.累计载货-lag(t.累计载货,1,0)over(partition by t.车队编号 order by t.累计载货) as 装入,
sum( flag  )over(partition by 车队编号 order by  累计载货,flag     )+decode(flag,0,1,0) as flag1,
sum( decode(flag,0,1,0)  )over(partition by 车队编号 order by  累计载货,flag     )+flag as flag2
from t3 t 
)   ---下面就是关联两表得到车主姓名和货物堆编号
select t.车队编号,t1.车主姓名,t2.货物堆编号,t.装入
from t4 t   join    t1 on  t.车队编号=t1.车队编号 and t.flag1=t1.顺序  join t2 on  t.车队编号=t2.车队编号 and t.flag1=t2.顺序
where t.装入>0
复制