select aaa.*,bbb.xh from(select xn,xq,kcdm,kcmc,xf,xkkh,kcxz from jxrwbview where xn='2017-2018' group by xn,xq,kcdm,kcmc,xf,xkkh,kcxz ) aaainner join (select distinct xkkh,xh from xsxkb where xn='2017-2018' ) bbbon aaa.xkkh=bbb.xkkh