是否有一种方法可以在DB2中实现多一对一的关系

Question

我需要创建这样的数据结构：

Table 1
Code, Value, Offer_ID

我正在创建一项服务，对于给定的"代码"one_answers" value"的组合，必须返回我预先配置的要约_id。

例如：

Code    Value  Offer_ID
------ ------- ----------
Age       30     OFF1
Age       30     OFF2
Province  RM     OFF2
Age       40     OFF3
Province  TO     OFF3
Age       40     OFF4
Province  TO     OFF4
Operator  TIM    OFF4 

呼叫服务总是打电话给我通过年龄，省和运营商价值观。如果我一起找到三个值的特定报价_ID(AS off4(，或者是2(as off3(或年龄是唯一的强制性(OFF1(。

，我必须在此表中查看。

所以，如果客户经过我的省省和操作员风，我必须退回1

我该怎么做？我如何构建表和查询？

我希望我能够揭露问题...

感谢1000个帮助我的人...我们要疯了...

Answer 1

尝试以下：

with tab (age, province, operator, offer_id) as (values
  (30, null,  null, 'OFF1')
, (30, 'RM',  null, 'OFF2')
, (40, 'TO',  null, 'OFF3')
, (40, 'TO', 'TIM', 'OFF4')
)
, op_inp (age, province, operator) as (values
--(40, 'TO', 'TIM') --'OFF4'
(40, 'TO', 'VODAFONE') --'OFF3'
--(30, 'RM', 'VODAFONE') --'OFF2'
--(30, 'TO', 'VODAFONE') --'OFF1'
)
select offer_id /*Just for info*/, order_flag
from 
(
select t.*, 3 as order_flag
from tab t
join op_inp o on o.age=t.age and o.province=t.province and o.operator=t.operator
  union all
select t.*, 2 as order_flag
from tab t
join op_inp o on o.age=t.age and o.province=t.province --and t.operator is null
  union all
select t.*, 1 as order_flag
from tab t
join op_inp o on o.age=t.age --and t.province is null and t.operator is null
)
order by order_flag desc
fetch first 1 row only
;