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
其中 子查询 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