三表连接，有一列是子查询查出列数据，后有重复数据

Question

查询KQLeaveRecord在某时间段里的某个ClassID的记录条数
(select COUNT(*) from KQLeaveRecord where classid=1100
and ((BigenDate between '2017-09-07 0:0:0' and '2017-09-07 23:59:0' and
EndDate between '2017-09-07 0:0:0' and '2017-09-07 23:59:0' )
or (BigenDate<='2017-09-07 0:0:0' and EndDate >='2017-09-07 23:59:0')))

连接查出数据
SELECT a.CABID, a.ClassID,(select Count(*) from XSClassStudent where ClassID=a.ClassID and StuState=1) ClassNumber,

(select COUNT(*) from KQLeaveRecord where classid=a.ClassID
and (BigenDate between '2017-09-07 0:0:0' and '2017-09-07 23:59:0' and
EndDate between '2017-09-07 0:0:0' and '2017-09-07 23:59:0' )
or (BigenDate<='2017-09-07 0:0:0' and EndDate >='2017-09-07 23:59:0')) qq,

a.State AS ClassState,
b.KQClassID, b.KQDate, b.KQClassNumber,b.KQAttendanceNumber, b.KQAttendanceLv, b.State AS KQState,
c.NUMCLASSID FROM
KQClassAttendanceBase a LEFT OUTER JOIN KQClassRecord b ON
a.ClassID = b.ClassID and b.KQDate='2017-09-07'
LEFT OUTER JOIN XSClassInfo c ON
a.ClassID = c.ClassID

where a.State=1

子查询QQ一列为重复数据，猜测是ClassID未对应，不知道具体原因，球解决办法

Answer 1

个人建议用select新表来改写and后面的语句。顺便请问一下，你写and后面的语句的逻辑是什么，你自己会不会觉得不太符合语法？