问题描述：
修改前sql
select tag.*,
hpjb.begindate,
hpjb.enddate,
hpjb.pk_psncl as pkPsncl,
"1" as onWorkFlag
from
(select *
from (select "JTTA" as 'tenantCode',
t.pk_psndoc as "guid",
t.code 'empCode', -- 工号
t.name 'empName', -- 姓名
t.id as 'idNo',
t.shortname 'empNameAb', -- 姓名拼音
case t.sex when '1' then '男' when '2' then '女' else '' end as 'empGender', -- 性别
t.age 'empAge', -- 年龄
op.postname as 'postName', -- 岗位
op.postcode as 'postCode', -- 岗位编码
mz.name as 'empNation' , -- 民族
gj.NAME as 'empNationality', -- 国籍
jg.NAME as 'empNativePlace', -- 籍贯
hk.name as 'empResidentType', -- 户口性质
hkd.name as 'empResidentPlace', -- 户口所在地
hy.name as 'empMarriedStatus', -- 婚姻状况
zz.name as 'empParty', -- 政治面貌
t.joinpolitydate, -- 入党\团时间
t.mobile, -- 手机号码
t.email, -- 电子邮箱
IFNULL(t.workage, '0') AS 'empStartWorkDate', -- 参加工作年限
IFNULL(po.corpworkage,'0') AS 'empJoinGroupDate', -- 集团工龄
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = po.orgglbdef1) AS 'workEmpSource', -- 人员来源
ofn.ORG_ALL_FULL_CODE as 'orgFullCode', -- 组织机构CODE全拼
ofn.ORG_ALL_FULL_NAME as 'orgFullName',
ofn.ORG_CODE as 'orgCode',
xl.name as 'educHighBackground', -- 最高学历
ops.postseriesname as 'postSeries', -- 岗位序列
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef2) as 'positionLevel', -- 职务级别
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef15) as 'postEmpWorkPlace', -- 工作地点
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef15) as 'postWorkPlace', -- 工作地点(用于查询列表展示)
qyx.name as 'educSchoolType', -- 全日制院校
qxl.name as 'educBackground', -- 全日制学历
yx.name as 'educHighSchoolType', -- 最高院校
pe.major, -- 专业
t.glbdef8 as 'workCompanySource', -- 来源单位
t.pk_org,
t.pk_degree,
t.glbdef31 as "titleLevelCode",
pj.pk_psncl,
pj.jobglbdef19 as "department", -- 全级次部门、
pj.pk_dept as "pkDept",
zj.name as "jobLevel", -- 职务职级、
zd.name as "jobRank", -- 职等、
zcdj.name as "titleLevel" -- 职称等级、
from jthr.bd_psndoc t
LEFT JOIN jthr.hi_psndoc_edu pe on t.pk_psndoc = pe.pk_psndoc and pe.glbdef6 = 'Y'
LEFT JOIN jthr.bd_defdoc xl on xl.pk_defdoc = t.edu
left join jthr.bd_defdoc mz on mz.pk_defdoc = t.nationality
join jthr.t_jt_job pj on pj.pk_psndoc = t.pk_psndoc
left join jthr.bd_countryzone gj on gj.PK_COUNTRY = t.country
left join jthr.bd_region jg on jg.PK_REGION = t.nativeplace
left join jthr.bd_defdoc hk on hk.pk_defdoc = t.characterrpr
left join jthr.bd_region hkd on hkd.PK_REGION = t.permanreside
left join jthr.bd_defdoc hy on hy.pk_defdoc = t.marital
left JOIN jthr.bd_defdoc zz on zz.pk_defdoc = t.polity
left join og.t_og_org_full_name ofn on t.pk_org = ofn.ORG_ID
left join jthr.om_post op on op.pk_post = pj.pk_post
left join jthr.om_postseries ops on ops.pk_postseries = op.pk_postseries
left join jthr.hi_psnorg po on po.pk_psndoc = t.pk_psndoc and po.lastflag = 'Y' and po.endflag = 'N'
left join jthr.bd_defdoc zd on zd.pk_defdoc = pj.jobglbdef18
left join jthr.bd_defdoc zj on zj.pk_defdoc = pj.jobglbdef2
left join jthr.bd_defdoc zcdj on zcdj.pk_defdoc = t.glbdef31
left join jthr.bd_defdoc qyx on qyx.pk_defdoc = pe.glbdef12 and pe.studymode = '1001Z710000000016XIZ'
left join jthr.bd_defdoc qxl on qxl.pk_defdoc = t.edu and pe.studymode = '1001Z710000000016XIZ'
left join jthr.bd_defdoc yx on yx.pk_defdoc = pe.glbdef12
where t.dr = 0
) psn
where 1 = 1
and exists(select 1 from (select pk_psncl
from (select @guid idlist,
(select @guid:=group_concat(pk_psncl separator ',')
from jthr.bd_psncl
where find_in_set(parent_id, @guid) and enablestate=2) sub from jthr.bd_psncl,(select @guid:='1001A1100000000005OI') vars
where @guid is not null) tl,
jthr.bd_psncl t
where find_in_set(t.pk_psncl, tl.idlist))t1 where t1.pk_psncl = psn.pk_psncl ))tag left join jthr.hi_psnjob hpjb on tag.guid =hpjb.pk_psndoc
and hpjb.pk_psncl in ('1001A1100000000005OP','1001A1100000000005ON') AND dr = '0'
union ALL
select tag.*,
hpjb.begindate,
hpjb.enddate,
hpjb.pk_psncl as pkPsncl,
"3" as onWorkFlag
from
(select *
from (select "JTTA" as 'tenantCode',
t.pk_psndoc as "guid",
t.code 'empCode', -- 工号
t.name 'empName', -- 姓名
t.id as 'idNo',
t.shortname 'empNameAb', -- 姓名拼音
case t.sex when '1' then '男' when '2' then '女' else '' end as 'empGender', -- 性别
t.age 'empAge', -- 年龄
op.postname as 'postName', -- 岗位
op.postcode as 'postCode', -- 岗位编码
mz.name as 'empNation' , -- 民族
gj.NAME as 'empNationality', -- 国籍
jg.NAME as 'empNativePlace', -- 籍贯
hk.name as 'empResidentType', -- 户口性质
hkd.name as 'empResidentPlace', -- 户口所在地
hy.name as 'empMarriedStatus', -- 婚姻状况
zz.name as 'empParty', -- 政治面貌
t.joinpolitydate, -- 入党\团时间
t.mobile, -- 手机号码
t.email, -- 电子邮箱
IFNULL(t.workage, '0') AS 'empStartWorkDate', -- 参加工作年限
IFNULL(po.corpworkage,'0') AS 'empJoinGroupDate', -- 集团工龄
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = po.orgglbdef1) AS 'workEmpSource', -- 人员来源
ofn.ORG_ALL_FULL_CODE as 'orgFullCode', -- 组织机构CODE全拼
ofn.ORG_ALL_FULL_NAME as 'orgFullName',
ofn.ORG_CODE as 'orgCode',
xl.name as 'educHighBackground', -- 最高学历
ops.postseriesname as 'postSeries', -- 岗位序列
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef2) as 'positionLevel', -- 职务级别
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef15) as 'postEmpWorkPlace', -- 工作地点
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef15) as 'postWorkPlace', -- 工作地点(用于查询列表展示)
qyx.name as 'educSchoolType', -- 全日制院校
qxl.name as 'educBackground', -- 全日制学历
yx.name as 'educHighSchoolType', -- 最高院校
pe.major, -- 专业
t.glbdef8 as 'workCompanySource', -- 来源单位
t.pk_org,
t.pk_degree,
t.glbdef31 as "titleLevelCode",
pj.pk_psncl,
pj.jobglbdef19 as "department", -- 全级次部门、
pj.pk_dept as "pkDept",
zj.name as "jobLevel", -- 职务职级、
zd.name as "jobRank", -- 职等、
zcdj.name as "titleLevel" -- 职称等级、
from jthr.bd_psndoc t
LEFT JOIN jthr.hi_psndoc_edu pe on t.pk_psndoc = pe.pk_psndoc and pe.glbdef6 = 'Y'
LEFT JOIN jthr.bd_defdoc xl on xl.pk_defdoc = t.edu
left join jthr.bd_defdoc mz on mz.pk_defdoc = t.nationality
join jthr.t_jt_job pj on pj.pk_psndoc = t.pk_psndoc
left join jthr.bd_countryzone gj on gj.PK_COUNTRY = t.country
left join jthr.bd_region jg on jg.PK_REGION = t.nativeplace
left join jthr.bd_defdoc hk on hk.pk_defdoc = t.characterrpr
left join jthr.bd_region hkd on hkd.PK_REGION = t.permanreside
left join jthr.bd_defdoc hy on hy.pk_defdoc = t.marital
left JOIN jthr.bd_defdoc zz on zz.pk_defdoc = t.polity
left join og.t_og_org_full_name ofn on t.pk_org = ofn.ORG_ID
left join jthr.om_post op on op.pk_post = pj.pk_post
left join jthr.om_postseries ops on ops.pk_postseries = op.pk_postseries
left join jthr.hi_psnorg po on po.pk_psndoc = t.pk_psndoc and po.lastflag = 'Y' and po.endflag = 'N'
left join jthr.bd_defdoc zd on zd.pk_defdoc = pj.jobglbdef18
left join jthr.bd_defdoc zj on zj.pk_defdoc = pj.jobglbdef2
left join jthr.bd_defdoc zcdj on zcdj.pk_defdoc = t.glbdef31
left join jthr.bd_defdoc qyx on qyx.pk_defdoc = pe.glbdef12 and pe.studymode = '1001Z710000000016XIZ'
left join jthr.bd_defdoc qxl on qxl.pk_defdoc = t.edu and pe.studymode = '1001Z710000000016XIZ'
left join jthr.bd_defdoc yx on yx.pk_defdoc = pe.glbdef12
where t.dr = 0
) psn
where 1 = 1
and exists(select 1 from (select pk_psncl
from (select @guid idlist,
(select @guid:=group_concat(pk_psncl separator ',')
from jthr.bd_psncl
where find_in_set(parent_id, @guid) and enablestate=2) sub from jthr.bd_psncl,(select @guid:='1001A1100000000005OQ') vars
where @guid is not null) tl,
jthr.bd_psncl t
where find_in_set(t.pk_psncl, tl.idlist))t1 where t1.pk_psncl = psn.pk_psncl ))tag left join jthr.hi_psnjob hpjb on tag.guid =hpjb.pk_psndoc
and hpjb.pk_psncl in ('1001A1100000000005OP','1001A1100000000005ON') AND dr = '0'该sql执行第一个sql查询出 10514条数据
执行第二条sql查询出 64条数据
执行整段sql， 却只查出来64条数据
解决方法：
问题原因是用UNION ALL关联的两个结果集都用到了查询人员类别的子字段及其本身，而这个查询用到了变量“@guid”。两个结果集中变量重复，这时候需要将其中一个结果集中的变量名更改，结果才会正常展示。如图：
将第二段sql的@guid改成 @lgid就解决了问题
修改后sql
select tag.*,
hpjb.begindate,
hpjb.enddate,
hpjb.pk_psncl as pkPsncl,
"1" as onWorkFlag
from
(select *
from (select "JTTA" as 'tenantCode',
t.pk_psndoc as "guid",
t.code 'empCode', -- 工号
t.name 'empName', -- 姓名
t.id as 'idNo',
t.shortname 'empNameAb', -- 姓名拼音
case t.sex when '1' then '男' when '2' then '女' else '' end as 'empGender', -- 性别
t.age 'empAge', -- 年龄
op.postname as 'postName', -- 岗位
op.postcode as 'postCode', -- 岗位编码
mz.name as 'empNation' , -- 民族
gj.NAME as 'empNationality', -- 国籍
jg.NAME as 'empNativePlace', -- 籍贯
hk.name as 'empResidentType', -- 户口性质
hkd.name as 'empResidentPlace', -- 户口所在地
hy.name as 'empMarriedStatus', -- 婚姻状况
zz.name as 'empParty', -- 政治面貌
t.joinpolitydate, -- 入党\团时间
t.mobile, -- 手机号码
t.email, -- 电子邮箱
IFNULL(t.workage, '0') AS 'empStartWorkDate', -- 参加工作年限
IFNULL(po.corpworkage,'0') AS 'empJoinGroupDate', -- 集团工龄
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = po.orgglbdef1) AS 'workEmpSource', -- 人员来源
ofn.ORG_ALL_FULL_CODE as 'orgFullCode', -- 组织机构CODE全拼
ofn.ORG_ALL_FULL_NAME as 'orgFullName',
ofn.ORG_CODE as 'orgCode',
xl.name as 'educHighBackground', -- 最高学历
ops.postseriesname as 'postSeries', -- 岗位序列
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef2) as 'positionLevel', -- 职务级别
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef15) as 'postEmpWorkPlace', -- 工作地点
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef15) as 'postWorkPlace', -- 工作地点(用于查询列表展示)
qyx.name as 'educSchoolType', -- 全日制院校
qxl.name as 'educBackground', -- 全日制学历
yx.name as 'educHighSchoolType', -- 最高院校
pe.major, -- 专业
t.glbdef8 as 'workCompanySource', -- 来源单位
t.pk_org,
t.pk_degree,
t.glbdef31 as "titleLevelCode",
pj.pk_psncl,
pj.jobglbdef19 as "department", -- 全级次部门、
pj.pk_dept as "pkDept",
zj.name as "jobLevel", -- 职务职级、
zd.name as "jobRank", -- 职等、
zcdj.name as "titleLevel" -- 职称等级、
from jthr.bd_psndoc t
LEFT JOIN jthr.hi_psndoc_edu pe on t.pk_psndoc = pe.pk_psndoc and pe.glbdef6 = 'Y'
LEFT JOIN jthr.bd_defdoc xl on xl.pk_defdoc = t.edu
left join jthr.bd_defdoc mz on mz.pk_defdoc = t.nationality
join jthr.t_jt_job pj on pj.pk_psndoc = t.pk_psndoc
left join jthr.bd_countryzone gj on gj.PK_COUNTRY = t.country
left join jthr.bd_region jg on jg.PK_REGION = t.nativeplace
left join jthr.bd_defdoc hk on hk.pk_defdoc = t.characterrpr
left join jthr.bd_region hkd on hkd.PK_REGION = t.permanreside
left join jthr.bd_defdoc hy on hy.pk_defdoc = t.marital
left JOIN jthr.bd_defdoc zz on zz.pk_defdoc = t.polity
left join og.t_og_org_full_name ofn on t.pk_org = ofn.ORG_ID
left join jthr.om_post op on op.pk_post = pj.pk_post
left join jthr.om_postseries ops on ops.pk_postseries = op.pk_postseries
left join jthr.hi_psnorg po on po.pk_psndoc = t.pk_psndoc and po.lastflag = 'Y' and po.endflag = 'N'
left join jthr.bd_defdoc zd on zd.pk_defdoc = pj.jobglbdef18
left join jthr.bd_defdoc zj on zj.pk_defdoc = pj.jobglbdef2
left join jthr.bd_defdoc zcdj on zcdj.pk_defdoc = t.glbdef31
left join jthr.bd_defdoc qyx on qyx.pk_defdoc = pe.glbdef12 and pe.studymode = '1001Z710000000016XIZ'
left join jthr.bd_defdoc qxl on qxl.pk_defdoc = t.edu and pe.studymode = '1001Z710000000016XIZ'
left join jthr.bd_defdoc yx on yx.pk_defdoc = pe.glbdef12
where t.dr = 0
) psn
where 1 = 1
and exists(select 1 from (select pk_psncl
from (select @guid idlist,
(select @guid:=group_concat(pk_psncl separator ',')
from jthr.bd_psncl
where find_in_set(parent_id, @guid) and enablestate=2) sub from jthr.bd_psncl,(select @guid:='1001A1100000000005OI') vars
where @guid is not null) tl,
jthr.bd_psncl t
where find_in_set(t.pk_psncl, tl.idlist))t1 where t1.pk_psncl = psn.pk_psncl ))tag left join jthr.hi_psnjob hpjb on tag.guid =hpjb.pk_psndoc
and hpjb.pk_psncl in ('1001A1100000000005OP','1001A1100000000005ON') AND dr = '0'
union ALL
select tag.*,
hpjb.begindate,
hpjb.enddate,
hpjb.pk_psncl as pkPsncl,
"3" as onWorkFlag
from
(select *
from (select "JTTA" as 'tenantCode',
t.pk_psndoc as "guid",
t.code 'empCode', -- 工号
t.name 'empName', -- 姓名
t.id as 'idNo',
t.shortname 'empNameAb', -- 姓名拼音
case t.sex when '1' then '男' when '2' then '女' else '' end as 'empGender', -- 性别
t.age 'empAge', -- 年龄
op.postname as 'postName', -- 岗位
op.postcode as 'postCode', -- 岗位编码
mz.name as 'empNation' , -- 民族
gj.NAME as 'empNationality', -- 国籍
jg.NAME as 'empNativePlace', -- 籍贯
hk.name as 'empResidentType', -- 户口性质
hkd.name as 'empResidentPlace', -- 户口所在地
hy.name as 'empMarriedStatus', -- 婚姻状况
zz.name as 'empParty', -- 政治面貌
t.joinpolitydate, -- 入党\团时间
t.mobile, -- 手机号码
t.email, -- 电子邮箱
IFNULL(t.workage, '0') AS 'empStartWorkDate', -- 参加工作年限
IFNULL(po.corpworkage,'0') AS 'empJoinGroupDate', -- 集团工龄
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = po.orgglbdef1) AS 'workEmpSource', -- 人员来源
ofn.ORG_ALL_FULL_CODE as 'orgFullCode', -- 组织机构CODE全拼
ofn.ORG_ALL_FULL_NAME as 'orgFullName',
ofn.ORG_CODE as 'orgCode',
xl.name as 'educHighBackground', -- 最高学历
ops.postseriesname as 'postSeries', -- 岗位序列
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef2) as 'positionLevel', -- 职务级别
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef15) as 'postEmpWorkPlace', -- 工作地点
(SELECT name FROM jthr.bd_defdoc WHERE pk_defdoc = pj.jobglbdef15) as 'postWorkPlace', -- 工作地点(用于查询列表展示)
qyx.name as 'educSchoolType', -- 全日制院校
qxl.name as 'educBackground', -- 全日制学历
yx.name as 'educHighSchoolType', -- 最高院校
pe.major, -- 专业
t.glbdef8 as 'workCompanySource', -- 来源单位
t.pk_org,
t.pk_degree,
t.glbdef31 as "titleLevelCode",
pj.pk_psncl,
pj.jobglbdef19 as "department", -- 全级次部门、
pj.pk_dept as "pkDept",
zj.name as "jobLevel", -- 职务职级、
zd.name as "jobRank", -- 职等、
zcdj.name as "titleLevel" -- 职称等级、
from jthr.bd_psndoc t
LEFT JOIN jthr.hi_psndoc_edu pe on t.pk_psndoc = pe.pk_psndoc and pe.glbdef6 = 'Y'
LEFT JOIN jthr.bd_defdoc xl on xl.pk_defdoc = t.edu
left join jthr.bd_defdoc mz on mz.pk_defdoc = t.nationality
join jthr.t_jt_job pj on pj.pk_psndoc = t.pk_psndoc
left join jthr.bd_countryzone gj on gj.PK_COUNTRY = t.country
left join jthr.bd_region jg on jg.PK_REGION = t.nativeplace
left join jthr.bd_defdoc hk on hk.pk_defdoc = t.characterrpr
left join jthr.bd_region hkd on hkd.PK_REGION = t.permanreside
left join jthr.bd_defdoc hy on hy.pk_defdoc = t.marital
left JOIN jthr.bd_defdoc zz on zz.pk_defdoc = t.polity
left join og.t_og_org_full_name ofn on t.pk_org = ofn.ORG_ID
left join jthr.om_post op on op.pk_post = pj.pk_post
left join jthr.om_postseries ops on ops.pk_postseries = op.pk_postseries
left join jthr.hi_psnorg po on po.pk_psndoc = t.pk_psndoc and po.lastflag = 'Y' and po.endflag = 'N'
left join jthr.bd_defdoc zd on zd.pk_defdoc = pj.jobglbdef18
left join jthr.bd_defdoc zj on zj.pk_defdoc = pj.jobglbdef2
left join jthr.bd_defdoc zcdj on zcdj.pk_defdoc = t.glbdef31
left join jthr.bd_defdoc qyx on qyx.pk_defdoc = pe.glbdef12 and pe.studymode = '1001Z710000000016XIZ'
left join jthr.bd_defdoc qxl on qxl.pk_defdoc = t.edu and pe.studymode = '1001Z710000000016XIZ'
left join jthr.bd_defdoc yx on yx.pk_defdoc = pe.glbdef12
where t.dr = 0
) psn
where 1 = 1
and exists(select 1 from (select pk_psncl
from (select @lgid idlist,
(select @lgid:=group_concat(pk_psncl separator ',')
from jthr.bd_psncl
where find_in_set(parent_id, @lgid) and enablestate=2) sub from jthr.bd_psncl,(select @lgid:='1001A1100000000005OQ') vars
where @lgid is not null) tl,
jthr.bd_psncl t
where find_in_set(t.pk_psncl, tl.idlist))t1 where t1.pk_psncl = psn.pk_psncl ))tag left join jthr.hi_psnjob hpjb on tag.guid =hpjb.pk_psndoc
and hpjb.pk_psncl in ('1001A1100000000005OP','1001A1100000000005ON') AND dr = '0'
参考文档：MYSQL中使用UNION ALL，只显示第一个查询结果的问题_union all只有一条sql的值-优快云博客
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
