SQL查询报错：子查询返回的值不止一个是什么意思

[小野马1209]

--将QH1表的合同号与QH2表的合同号拼接起来,最终得到项目对应的合同号
--请问子查询返回的值不只一个是什么意思
select STUFF((SELECT  '/' + QH3.CU_ContractNo 
FROM(
SELECT  CU_ContractNo FROM CU_DesignValueBeginDetail AS QH1 WHERE QH1.CU_Project=A.ProjectDataId  
UNION ALL
SELECT ContractNo FROM salSalesContractMaster AS QH2
WHERE QH2.PermitState=2 and QH2.X_SignAppointState=1 and QH2.TypeId='0002' and QH2.BillDate>20200331 and QH2.X_ContractAttr=2
AND QH2.X_ProjectDataId=A.ProjectDataId) AS QH3),1,1,'')
From X_prjProjectData A

[lich2005]

STUFF 只能对一个值操作，不能对一列操作。你改写这样试试 select STUFF((SELECT '/' + QH3.CU_ContractNo FROM( SELECT CU_ContractNo FROM CU_DesignValueBeginDetail AS QH1 ) AS QH3),1,1,'') From X_prjProjectData A union all select STUFF((SELECT '/' + QH3.ContractNo FROM( SELECT ContractNo FROM salSalesContractMaster AS QH2 ) AS QH3),1,1,'') From X_prjProjectData A

[小野马1209]

引用 楼主 kaijie_wu1209 的回复:

--将QH1表的合同号与QH2表的合同号拼接起来,最终得到项目对应的合同号
--请问子查询返回的值不只一个是什么意思
select STUFF((SELECT  '/' + QH3.CU_ContractNo 
FROM(
SELECT  CU_ContractNo FROM CU_DesignValueBeginDetail AS QH1 WHERE QH1.CU_Project=A.ProjectDataId  
UNION ALL
SELECT ContractNo FROM salSalesContractMaster AS QH2
WHERE QH2.PermitState=2 and QH2.X_SignAppointState=1 and QH2.TypeId='0002' and QH2.BillDate>20200331 and QH2.X_ContractAttr=2
AND QH2.X_ProjectDataId=A.ProjectDataId) AS QH3),1,1,'')
From X_prjProjectData A

这个也会报错，我处理的字段只有CU_ContractNo 合同编号这一个字段，QH1和QH2UNUON 后的结果

[小野马1209]

引用 1 楼 lich2005 的回复:

STUFF 只能对一个值操作，不能对一列操作。你改写这样试试

select STUFF((SELECT '/' + QH3.CU_ContractNo
FROM(
SELECT CU_ContractNo FROM CU_DesignValueBeginDetail AS QH1
) AS QH3),1,1,'')
From X_prjProjectData A

union all

select STUFF((SELECT '/' + QH3.ContractNo
FROM(
SELECT ContractNo FROM salSalesContractMaster AS QH2
) AS QH3),1,1,'')
From X_prjProjectData A

这个也会报错，我处理的字段只有CU_ContractNo 合同编号这一个字段，QH1和QH2UNUON 后的结果

[Yole]

SELECT ContractNo FROM salSalesContractMaster AS QH2 WHERE QH2.PermitState=2 and QH2.X_SignAppointState=1 and QH2.TypeId='0002' and QH2.BillDate>20200331 and QH2.X_ContractAttr=2 AND QH2.X_ProjectDataId=A.ProjectDataId 这两个联完之后有多少条数据？是不是有多条？

[小野马1209]

引用 4 楼 Yole 的回复:

SELECT ContractNo FROM salSalesContractMaster AS QH2
WHERE QH2.PermitState=2 and QH2.X_SignAppointState=1 and QH2.TypeId='0002' and QH2.BillDate>20200331 and QH2.X_ContractAttr=2
AND QH2.X_ProjectDataId=A.ProjectDataId

这两个联完之后有多少条数据？是不是有多条？

SELECT QH2.ContractNo FROM salSalesContractMaster AS QH2   --合同表
LEFT JOIN X_prjProjectData AS A ON A.ProjectDataId=QH2.X_ProjectDataId   --项目表
WHERE QH2.PermitState=2 and QH2.X_SignAppointState=1 and QH2.TypeId='0002' 
and QH2.BillDate>20200331 and QH2.X_ContractAttr=2
AND QH2.X_ProjectDataId=A.ProjectDataId

这么查有3条记录

[小野马1209]

引用 4 楼 Yole 的回复:

SELECT ContractNo FROM salSalesContractMaster AS QH2
WHERE QH2.PermitState=2 and QH2.X_SignAppointState=1 and QH2.TypeId='0002' and QH2.BillDate>20200331 and QH2.X_ContractAttr=2
AND QH2.X_ProjectDataId=A.ProjectDataId

这两个联完之后有多少条数据？是不是有多条？

有问题的数据已经找出，就是你截图语法中满足结果的的3条数据，我单独用了一个项目分析,这个项目QH1和QH2表都各只有一条数据，为什么不可以这么写呢

[RINK_1]

你这得配合 FOR XML PATH来用 试试下面的

select STUFF((SELECT  '/' + QH3.CU_ContractNo 
FROM(
SELECT  CU_ContractNo,QH1.CU_Project FROM CU_DesignValueBeginDetail  
UNION ALL
SELECT ContractNo,X_ProjectDataId FROM salSalesContractMaster AS QH2
WHERE QH2.PermitState=2 and QH2.X_SignAppointState=1 and QH2.TypeId='0002' and QH2.BillDate>20200331 and QH2.X_ContractAttr=2
) AS QH3 WHERE QH3.CU_Project=A.ProjectDataId FOR XML PATH('')),1,1,'')
From X_prjProjectData A