select TUPLE_ID,
tuple_element,
item_element,
min_occurs,
max_occurs,
id_attribute
from t_tuple_info t
where 1 = 1
and (t.tuple_element in
(select NAME From T_XBRL_ELEMENT where APPLY_STATE = 'flowC' and ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c') or
t.item_element in
(select NAME
From T_XBRL_ELEMENT
where APPLY_STATE = 'flowC'
and ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'))这是古老的写法?哪位大侠有方法把tuple_element 和 item_element两者组合成一个方法 (select NAME
From T_XBRL_ELEMENT
where APPLY_STATE = 'flowC'
and ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'))
只写一次
(select NAME From T_XBRL_ELEMENT where APPLY_STATE = 'flowC' and ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c') or
t.item_element in
(select NAME
From T_XBRL_ELEMENT
where APPLY_STATE = 'flowC'
and ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'))and这后面一块简化简化
SELECT TUPLE_ID,
tuple_element,
item_element,
min_occurs,
max_occurs,
id_attribute
FROM t_tuple_info t1
WHERE 1 = 1
AND EXISTS
(SELECT 'x'
FROM T_XBRL_ELEMENT t2
WHERE t2.APPLY_STATE = 'flowC'
AND t2.ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'
AND (t1.tuple_element = t2.NAME OR t1.item_element = t2.NAME))
tuple_element,
item_element,
min_occurs,
max_occurs,
id_attribute
from t_tuple_info t, T_XBRL_ELEMENT a
where a.APPLY_STATE = 'flowC'
and a.ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'
and t.tuple_element a.NAME这样做是可以达到你的要求的。
select TUPLE_ID,
tuple_element,
item_element,
min_occurs,
max_occurs,
id_attribute
from t_tuple_info t
where exists(select 1 from T_XBRL_ELEMENT b
where (t.tuple = b.name or t.item_element = b.name)
and APPLY_STATE = 'flowC'
and ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'
);
t.tuple_element,
t.item_element,
t.min_occurs,
t.max_occurs,
t.id_attribute
from t_tuple_info t join T_XBRL_ELEMENT x on t.tuple_element=x.NAME
where x.APPLY_STATE = 'flowC' and x.ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'
union
select t.TUPLE_ID,
t.tuple_element,
t.item_element,
t.min_occurs,
t.max_occurs,
t.id_attribute
from t_tuple_info t join T_XBRL_ELEMENT x on t.item_element=x.NAME
where x.APPLY_STATE = 'flowC' and x.ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c
in的速度是很慢的 尤其是数据量比较大的情况下,你可以用连接查询,我不知道你哪个表数据量较小,可以将数据量较小的表放到后面,因为oracle是从右到左顺序连接查询的
select TUPLE_ID,
tuple_element,
item_element,
min_occurs,
max_occurs,
id_attribute
from t_tuple_info t,(select NAME
From T_XBRL_ELEMENT
where APPLY_STATE = 'flowC'
and ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c') S
where 1 = 1
AND (t.tuple_element=S.NAME OR t.item_element = S.NAME);
tuple_element,
item_element,
min_occurs,
max_occurs,
id_attribute
FROM t_tuple_info t1
WHERE 1 = 1
AND EXISTS
(SELECT 'x'
FROM T_XBRL_ELEMENT t2
WHERE t2.APPLY_STATE = 'flowC'
AND t2.ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'
AND (t1.tuple_element = t2.NAME OR t1.item_element = t2.NAME))
tuple_element,
item_element,
min_occurs,
max_occurs,
id_attribute
from t_tuple_info t
where 1 = 1
and EXISTS (SELECT 1 FROM T_XBRL_ELEMENT B WHERE B.APPLY_STATE = 'flowC'
and B.ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'
AND T.tuple_element = B.NAME)
UNION ALL
select TUPLE_ID,
tuple_element,
item_element,
min_occurs,
max_occurs,
id_attribute
from t_tuple_info t
where 1 = 1
and EXISTS (SELECT 1 FROM T_XBRL_ELEMENT B WHERE B.APPLY_STATE = 'flowC'
and B.ELEMENT_TYPE = '8a814de629f2b0c70129f2b32236000c'
AND T.tuple_element = B.NAME)