我在SQL方面缺乏经验,需要一个将多层过滤器应用于多个表的查询以进行比较。
表1:
订单编号步骤名称数据参数名称
12步骤常量P1
12步骤2 const2 P2
12步4值1 P3
30步骤6常量3 P1
30步骤6常量4 P2
30步进6值2 P3
所有表都具有相同的格式,只是数据值不同。订单号可能因表而异。
我想做:
- 仅搜索步骤名称=步骤1和步骤8之间的行
- 查找匹配的订单号(P1=常量1,P2=常量2)
- 将P3行保存在同一订单号组中
- 显示所有行(每个表中一行):
订单编号表数据参数名称
12表1值1 P3
12表2值2 P3
14表3值3 P3
非常感谢您的帮助!
您需要根据需要添加尽可能多的union all...select
部件:
select
order_number,
'table1' as `table`,
data,
prameter_name
from
table1 t1p3
where
step_name in ('step1', 'step2', 'step3', 'step4', 'step5', 'step6', 'step7', 'step8') and
parameter = 'p3' and
exists (
select
'x'
from
table1 t1p1
where
t1p1.order_number = t1p3.order_number and
t1p1.step_name = t1p3.step_name and
t1p1.parameter = 'p1' and
t1p1.data = 'const1'
) and exists (
select
'x'
from
table1 t1p2
where
t1p2.order_number = t1p3.order_number and
t1p2.step_name = t1p3.step_name and
t1p2.parameter = 'p2' and
t1p2.data = 'const2'
)
union all
select
order_number,
'table2',
data,
prameter_name
from
table2 t2p3
where
step_name in ('step1', 'step2', 'step3', 'step4', 'step5', 'step6', 'step7', 'step8') and
parameter = 'p3' and
exists (
select
'x'
from
table2 t2p1
where
t2p1.order_number = t2p3.order_number and
t2p1.step_name = t2p3.step_name and
t2p1.parameter = 'p1' and
t2p1.data = 'const1'
) and exists (
select
'x'
from
table2 t2p2
where
t2p2.order_number = t2p3.order_number and
t2p2.step_name = t2p3.step_name and
t2p2.parameter = 'p2' and
t2p2.data = 'const2'
)