如下语句,条件中的表yb_mmyb_brzd有几个字段,
zyh fjbname jlid
1 aaaa 1
1 bbbb 2
2 vvvv 2
3 vvvvv 1
其中同一个zyh可能有多条记录,也可能只有一条记录,怎么修改下面的检索条件,使得单有多条记录的时候
取jlid最小的对应那个fjbname,单条记录的时候就取那条的fjbname呢;现在是取jlid=1的fjbname;如果没有1,就取2,没有2就取3;以此类推。
SELECT YB_MMYB_PERSONINFO.JYDJH,
ZY_BRRY.NCYLZ,
ZY_BRRY.ZYHM,
ZY_BRRY.BRXM,
ZY_BRRY.BRBQ,
ZY_BRRY.SFZH,
ZY_BRRY.BRXB,
ZY_BRRY.GZDW,
yb_mmyb_brzd.FJBNAME,
ZY_ZYJS.KSRQ,
ZY_ZYJS.ZZRQ,
CASE WHEN ZY_BRRY.BRXB = 1 THEN '男' WHEN ZY_BRRY.BRXB = 2 THEN '女' ELSE '不祥' END AS BRXB,
ZY_ZYJS.FYHJ,
ZY_ZYJS.czyljz as NCYLHZJZ,
(ZY_ZYJS.FYHJ - ZY_ZYJS.czyljz - ZY_ZYJS.YYDF) AS ZFHJ,
ZY_ZYJS.YYDF,
0 as lsh,
zy_zyjs.jsrq,
case when c.zjje = 0 then 0 when c.zjje <> 0 then c.zjje end AS YFHJ
FROM ZY_ZYJS,ZY_BRRY,YB_MMYB_PERSONINFO,gy_brxz,yb_mmyb_brzd,
(select zy_jsmx.zyh,zy_jsmx.jscs,sum(zy_jsmx.zjje) as zjje
from zy_jsmx,gy_sfxm
where gy_sfxm.fyfl = 2 and
gy_sfxm.sfxm = zy_jsmx.fyxm
group by zy_jsmx.zyh,zy_jsmx.jscs ) c
WHERE ZY_BRRY.ZYH(+) = ZY_ZYJS.ZYH AND
YB_MMYB_PERSONINFO.zyh = zy_brry.zyh and YB_MMYB_PERSONINFO.jlzt <> -1 and
ZY_ZYJS.ZYH = c.ZYH(+) AND
ZY_ZYJS.BRXZ = gy_brxz.brxz AND
gy_brxz.dbpb = 3 and
ZY_ZYJS.JSRQ >= :ADT_S AND
ZY_ZYJS.JSRQ <= :ADT_E AND
ZY_ZYJS.ZFPB = 0 AND
ZY_ZYJS.JSCS = c.JSCS(+) AND
ZY_ZYJS.czyljz <> 0 and
YB_MMYB_PERSONINFO.rylbbm in ('1','2') and
yb_mmyb_brzd.zyh = zy_brry.zyh and
yb_mmyb_brzd.jlid = 1