2 luiyuhui LuiYuHui 于 2017.09.09 11:40 提问

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

查询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未对应,不知道具体原因,球解决办法

2个回答

number1killer
number1killer   2017.09.09 13:01

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

LuiYuHui
LuiYuHui 后面的,假如时间段条件是今天0点到23点59,一种情况是开始时间和结束时间都在今天的时间里面,or 后面的情况是开始时间在昨天或者前几天,结束时间在明天或者后几天,只要有便会记录条数,
2 个月之前 回复
LuiYuHui
LuiYuHui   2017.09.09 14:17

后面的,假如时间段条件是今天0点到23点59,一种情况是开始时间和结束时间都在今天的时间里面,or 后面的情况是开始时间在昨天或者前几天,结束时间在明天或者后几天,只要有便会记录条数,

Csdn user default icon
上传中...
上传图片
插入图片