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
Among them, Silect Chiku_CD, Min (Service_FLG) AS Service_FLG from GCM_HAISO WHERE GCM_HAISO.MISE_CD = 1111and Bumon_CD = 111 Group By Chiku_CD By packet, take the minimum value to obtain a non-repetitive record, then use Inner Join to connect to another table, if still If you want to query the Min (Gathering Function) field of the sub-query, you need to take some min MIN (Service_FLG) as service_flg to use it outside the child.
No. 24 supplements:
It is found that the inner joint can actually do not write, the result is like from A, B, and later use the right connection, and the joint query.
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 (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
Right connection
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)
