子查询 结合 内连接 来消除重复纪录

SELECT A.TEI_ZOKU, A.HAISO_CHIKU_CD,A.MOS_CART_CD, A.SORYO, A.KINSHI_FLG, A.INS_YMD, A.INS_HMS, A.LST_UPDTS,A.UPDT_KAI, A.LST_UPDT_YMD, A.LST_UPDT_HMS, B.MISE_NAME, C.BUMON_NAME,SERVICE_FLG FROM (select chiku_cd,min( service_flg) as service_flg from gcm_haiso where gcm_haiso.mise_cd=1111and bumon_cd=111  group by chiku_cd ) inner join GCM_SORYO A on chiku_cd =HAISO_CHIKU_CD,GCM_MISE B,GCM_BUMON C WHERE A.BUMON_CD =C.BUMON_CD  AND A.MISE_CD=C.MISE_CD AND A.MISE_CD=B.MISE_CD AND A.MISE_CD=1111 AND A.BUMON_CD = 111 AND A.MOS_CART_CD=0   ORDER BY CAST(A.HAISO_CHIKU_CD  AS int) ASC

其中 子查询 select chiku_cd,min( service_flg) as service_flg from gcm_haiso where gcm_haiso.mise_cd=1111and bumon_cd=111  group by chiku_cd  通过分组，取最小值获得不重复的纪录，然后 用 inner join 来连接 另一个表，如果还想查询 子查询 的min（聚集函数）字段，则需要给他取个别名 min( service_flg) as service_flg ，才能在子查询外使用。

24号补充：
发现内联接其实可以不写，结果一样的，就像From A,B一样，后来用到了右连接，以及联合查询。
SELECT NVL(CAST(MAX(CHIKU_CD) AS INT),0) AS CHIKU_CD FROM GCM_HAISO WHERE  MISE_CD = 1111 AND BUMON_CD = 111 UNION (SELECT NVL(CAST(MAX(HAISO_CHIKU_CD) AS INT),0) FROM GCM_SORYO WHERE  MISE_CD =1111 AND BUMON_CD =111 AND MOS_CART_CD=0  ) ORDER BY CHIKU_CD DESC

右连接
SELECT A.MISE_CD, A.BUMON_CD,A.TEI_ZOKU, A.HAISO_CHIKU_CD,A.MOS_CART_CD, A.SORYO, A.KINSHI_FLG, A.INS_YMD, A.INS_HMS, A.LST_UPDTS,A.UPDT_KAI, A.LST_UPDT_YMD, A.LST_UPDT_HMS, SERVICE_FLG FROM (SELECT CHIKU_CD,MIN(SERVICE_FLG) AS SERVICE_FLG FROM GCM_HAISO WHERE GCM_HAISO.MISE_CD=1111 AND GCM_HAISO.BUMON_CD=111 GROUP BY CHIKU_CD) RIGHT OUTER JOIN GCM_SORYO A  ON CHIKU_CD =HAISO_CHIKU_CD WHERE A.MISE_CD=1111 AND A.BUMON_CD =111 AND A.MOS_CART_CD=0   ORDER BY CAST(A.HAISO_CHIKU_CD  AS int) ASC