<select id="query_top_structure_bySectionId" resultMap="BaseResultMap">
select distinct *
from FP_00000021( where--也可以有where子句作为约束条件)
start with SPARE_PARTS_SN in
< foreach collection="sectionIds" item="sectionId" index="index"
open="(" close=")" separator=",">
#{sectionId} (此处传进一个以逗号为分割符的字符串,分割出来的元素放到集合sections中进行遍历,作为SPARE_PARTS_SN的值)
</foreach>
connect by prior SPARE_PARTS_SN = PARENT_SN
UNION
select distinct *
from FP_00000021
start with SPARE_PARTS_SN in
<foreach collection="sectionIds" item="sectionId" index="index"
open="(" close=")" separator=",">
#{sectionId}
</foreach>
connect by prior PARENT_SN = SPARE_PARTS_SN
UNION 命令:合并两个或多个select的结果集
select distinct *
from FP_00000021( where--也可以有where子句作为约束条件)
start with SPARE_PARTS_SN in
< foreach collection="sectionIds" item="sectionId" index="index"
open="(" close=")" separator=",">
#{sectionId} (此处传进一个以逗号为分割符的字符串,分割出来的元素放到集合sections中进行遍历,作为SPARE_PARTS_SN的值)
</foreach>
connect by prior SPARE_PARTS_SN = PARENT_SN
UNION
select distinct *
from FP_00000021
start with SPARE_PARTS_SN in
<foreach collection="sectionIds" item="sectionId" index="index"
open="(" close=")" separator=",">
#{sectionId}
</foreach>
connect by prior PARENT_SN = SPARE_PARTS_SN
</select>
UNION 命令:合并两个或多个select的结果集
列出所有在中国和美国的不同的雇员名:
SELECT E_Name FROM Employees_China
UNION
SELECT E_Name FROM Employees_USA
结果
E_Name
|
---|
Zhang, Hua
|
Wang, Wei
|
Carter, Thomas
|
Yang, Ming
|
Adams, John
|
Bush, George
|
Gates, Bill
|
注释:这个命令无法列出在中国和美国的所有雇员。在上面的例子中,我们有两个名字相同的雇员,他们当中只有一个人被列出来了。UNION 命令
只会选取不同的值。
UNION ALL
UNION ALL 命令和 UNION 命令几乎是等效的,不过 UNION ALL 命令
会列出所有的值。
SQL Statement 1 UNION ALL SQL Statement 2
递归删除:
<delete id="deleteClassifyById" parameterType="java.lang.Short" >
delete from APP_PKG_CLASSIFY t1 where id in
( select id from APP_PKG_CLASSIFY t start with t.ID = #{id,jdbcType=DECIMAL} connect by prior t.ID = t.PARENT_ID)
</delete>
递归删除:
<delete id="deleteClassifyById" parameterType="java.lang.Short" >
delete from APP_PKG_CLASSIFY t1 where id in
( select id from APP_PKG_CLASSIFY t start with t.ID = #{id,jdbcType=DECIMAL} connect by prior t.ID = t.PARENT_ID)
</delete>