SQL报错 子查询返回的值不止一个。

Question

子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后，或子查询用作表达式时，这种情况是不允许的。
select * FROM
(select tmpppp.考勤类型,isnull(tmppp.人数,0) 人数 from
(select CASE WHEN isnull((select FACTORYNO FROM tblSMDFactoryBasis), 0)=1 THEN '正常' end 考勤类型)tmpppp
left join
(select '正常' as 考勤类型,sum(tmpp.正常) 人数
from (
select TMP.USERNO,MIN(TMP.上工时间) 最早上工时间,'考勤' as 考勤,
'迟到'=case WHEN CONVERT(varchar(20),MIN(TMP.上工时间) ,13)>CONVERT(varchar(20),'07:40:00',13) THEN 1 END,
'正常'=case WHEN CONVERT(varchar(20),MIN(TMP.上工时间) ,13)<=CONVERT(varchar(20),'07:40:00',13) THEN 1 END
FROM
(select A.LOGINPLACENO,C.equipmentname 设备名称,B.USERNO,B.USERNAME 员工姓名,A.LOGINDATE
,CONVERT(varchar(5) , A.LOGINDATE, 101) 上工日期,CONVERT(varchar(5) , A.LOGINDATE, 108) 上工时间
FROM TBLWIPOPERATORSTATE A,tblUSRUserBasis B,tblEQPEquipmentBasis C
WHERE A.USERNO=B.USERNO AND C.EQUIPMENTNO=A.LOGINPLACENO
AND CONVERT(varchar(5) , A.LOGINDATE, 101)=CONVERT(varchar(5) , getdate(), 101)
AND B.DEPARTMENTNO='109200') TMP
GROUP BY TMP.USERNO ) tmpp group by tmpp.考勤)tmppp on tmpppp.考勤类型=tmppp.考勤类型
UNION ALL
select tmpppp.考勤类型,isnull(tmppp.人数,0) 人数 from
(select CASE WHEN isnull((select FACTORYNO FROM tblSMDFactoryBasis), 0)=1 THEN '迟到' end 考勤类型)tmpppp
left join
(select '迟到' as 考勤类型,sum(tmpp.迟到) 人数
from (
select TMP.USERNO,MIN(TMP.上工时间) 最早上工时间,'考勤' as 考勤,
'迟到'=case WHEN CONVERT(varchar(20),MIN(TMP.上工时间) ,13)>CONVERT(varchar(20),'07:40:00',13) THEN 1 END,
'正常'=case WHEN CONVERT(varchar(20),MIN(TMP.上工时间) ,13)<=CONVERT(varchar(20),'07:40:00',13) THEN 1 END
FROM
(select A.LOGINPLACENO,C.equipmentname 设备名称,B.USERNO,B.USERNAME 员工姓名,A.LOGINDATE
,CONVERT(varchar(5) , A.LOGINDATE, 101) 上工日期,CONVERT(varchar(5) , A.LOGINDATE, 108) 上工时间
FROM TBLWIPOPERATORSTATE A,tblUSRUserBasis B,tblEQPEquipmentBasis C
WHERE A.USERNO=B.USERNO AND C.EQUIPMENTNO=A.LOGINPLACENO
AND CONVERT(varchar(5) , A.LOGINDATE, 101)=CONVERT(varchar(5) , getdate(), 101)
AND B.DEPARTMENTNO='109200') TMP
GROUP BY TMP.USERNO ) tmpp group by tmpp.考勤)tmppp on tmpppp.考勤类型=tmppp.考勤类型
UNION ALL
select total.考勤类型 ,(sum(a.人数)/2-sum(total.人数))人数
from
(select '缺勤' as 考勤类型,isnull(tmppp.人数,0) 人数 from
(select CASE WHEN isnull((select FACTORYNO FROM tblSMDFactoryBasis), 0)=1 THEN '正常' end 考勤类型)tmpppp
left join
(select '正常' as 考勤类型,sum(tmpp.正常) 人数
from (
select TMP.USERNO,MIN(TMP.上工时间) 最早上工时间,'考勤' as 考勤,
'迟到'=case WHEN CONVERT(varchar(20),MIN(TMP.上工时间) ,13)>CONVERT(varchar(20),'07:40:00',13) THEN 1 END,
'正常'=case WHEN CONVERT(varchar(20),MIN(TMP.上工时间) ,13)<=CONVERT(varchar(20),'07:40:00',13) THEN 1 END
FROM
(select A.LOGINPLACENO,C.equipmentname 设备名称,B.USERNO,B.USERNAME 员工姓名,A.LOGINDATE
,CONVERT(varchar(5) , A.LOGINDATE, 101) 上工日期,CONVERT(varchar(5) , A.LOGINDATE, 108) 上工时间
FROM TBLWIPOPERATORSTATE A,tblUSRUserBasis B,tblEQPEquipmentBasis C
WHERE A.USERNO=B.USERNO AND C.EQUIPMENTNO=A.LOGINPLACENO
AND CONVERT(varchar(5) , A.LOGINDATE, 101)=CONVERT(varchar(5) , getdate(), 101)
AND B.DEPARTMENTNO='109200') TMP
GROUP BY TMP.USERNO ) tmpp group by tmpp.考勤)tmppp on tmpppp.考勤类型=tmppp.考勤类型
UNION ALL
select '缺勤' as 考勤类型,isnull(tmppp.人数,0) 人数 from
(select CASE WHEN isnull((select FACTORYNO FROM tblSMDFactoryBasis), 0)=1 THEN '迟到' end 考勤类型)tmpppp
left join
(select '迟到' as 考勤类型,sum(tmpp.迟到) 人数
from (
select TMP.USERNO,MIN(TMP.上工时间) 最早上工时间,'考勤' as 考勤,
'迟到'=case WHEN CONVERT(varchar(20),MIN(TMP.上工时间) ,13)>CONVERT(varchar(20),'07:40:00',13) THEN 1 END,
'正常'=case WHEN CONVERT(varchar(20),MIN(TMP.上工时间) ,13)<=CONVERT(varchar(20),'07:40:00',13) THEN 1 END
FROM
(select A.LOGINPLACENO,C.equipmentname 设备名称,B.USERNO,B.USERNAME 员工姓名,A.LOGINDATE
,CONVERT(varchar(5) , A.LOGINDATE, 101) 上工日期,CONVERT(varchar(5) , A.LOGINDATE, 108) 上工时间
FROM TBLWIPOPERATORSTATE A,tblUSRUserBasis B,tblEQPEquipmentBasis C
WHERE A.USERNO=B.USERNO AND C.EQUIPMENTNO=A.LOGINPLACENO
AND CONVERT(varchar(5) , A.LOGINDATE, 101)=CONVERT(varchar(5) , getdate(), 101)
AND B.DEPARTMENTNO='109200') TMP
GROUP BY TMP.USERNO ) tmpp group by tmpp.考勤)tmppp on tmpppp.考勤类型=tmppp.考勤类型) total
,(select '缺勤' as 考勤类型,count(*) 人数 from tblUSRUserBasis where departmentno=109200 group by departmentno/*考勤总人数表*/) a
where a.考勤类型=total.考勤类型
group by total.考勤类型)A
WHERE 1=1

Answer 1

太长，sql写得这么复杂看不下去。。。