SELECT
b.SRLXDM,
b.FJ
FROM
SRGL_SRXMB a
JOIN SRGL_SRLXB b ON b.srlxdm = a.srly
where a.ZGBM = '113400'
GROUP BY
b.SRLXDM,
b.FJ
ORDER BY
b.SRLXDM
查询结果,如图
可以看到SRLXDM003是有子级的,需求是去掉有子级的父级
例子:
SELECT
*
FROM
table1 t1
WHERE
NOT EXISTS ( SELECT 1 FROM table1 t2 WHERE t2.parent_id = t1.id );
table1会查出所有的父子级关系的结果集,子查询中是找出有子级的的父级,NOT EXISTS将t1中有子级的父级进行过滤
修改后:
SELECT * FROM
(SELECT
b.SRLXDM,
b.FJ
FROM
SRGL_SRXMB a
JOIN SRGL_SRLXB b ON b.srlxdm = a.srly
where a.ZGBM = '113400'
GROUP BY
b.SRLXDM,
b.FJ ) t1
WHERE
NOT EXISTS(SELECT * FROM (SELECT
b.SRLXDM,
b.FJ
FROM
SRGL_SRXMB a
JOIN SRGL_SRLXB b ON b.srlxdm = a.srly
where a.ZGBM = '113400'
GROUP BY
b.SRLXDM,
b.FJ ) t2 where t2.FJ = t1.SRLXDM )
ORDER BY
SRLXDM