UC知道如何优化这段sql代码
来源：百度知道 编辑：UC知道 时间：2024/05/31 00:28:19
在执行下面这段代码时，如果数据表有10000条记录，就显得比较慢了，明显感觉要等上一段时间，甚至发生连接超时错误。
SELECT depart_id,dateN, planNum,depart_name,recN,(SELECT COUNT(*) AS recn1
FROM vew_timeRecEveryDay AS b
WHERE a.depart_id = left(b.depart_id,3) AND a.dateN = CONVERT(char(10), b.sign_time, 120) AND
DATEPART(hh,b.sign_time) >= 6 AND DATEPART(hh,b.sign_time) <= 18.00
) as dayRecN,(SELECT COUNT(*) AS recn1
FROM vew_timeRecEveryDay AS b
WHERE a.depart_id = left(b.depart_id,3) AND a.dateN = CONVERT(char(10), b.sign_time, 120) AND
DATEPART(hh,b.sign_time) > 18.00 AND DATEPART(hh,b.sign_time) < 24
)+(SELECT COUNT(*) AS recn1
FROM vew_timeRecEveryDay AS b
WHERE a.depart_id = left(b.depart_id,3) AND a.dateN = CONVERT(char(10), b.sign_time, 120) AND
DATEPART(hh,b.sign_time) > =0 AND DATEPART(hh,b.sign_time) < 6
) as nightRecN
FROM vew_plan as a
SELECT depart_id,dateN, planNum,depart_name,recN,(SELECT COUNT(*) AS recn1
FROM vew_timeRecEveryDay AS b
WHERE a.depart_id = left(b.depart_id,3) AND a.dateN = CONVERT(char(10), b.sign_time, 120) AND
DATEPART(hh,b.sign_time) >= 6 AND DATEPART(hh,b.sign_time) <= 18.00
) as dayRecN,(SELECT COUNT(*) AS recn1
FROM vew_timeRecEveryDay AS b
WHERE a.depart_id = left(b.depart_id,3) AND a.dateN = CONVERT(char(10), b.sign_time, 120) AND
DATEPART(hh,b.sign_time) > 18.00 AND DATEPART(hh,b.sign_time) < 24
)+(SELECT COUNT(*) AS recn1
FROM vew_timeRecEveryDay AS b
WHERE a.depart_id = left(b.depart_id,3) AND a.dateN = CONVERT(char(10), b.sign_time, 120) AND
DATEPART(hh,b.sign_time) > =0 AND DATEPART(hh,b.sign_time) < 6
) as nightRecN
FROM vew_plan as a
这个句子是长了些，但这样比较清晰，再优化不少多少，给自己留点清晰度也好。
说句实话，我是无能为力，我也喜欢用这种方式，但比其实人的效率要高一些
用联接比较快join
加上group
我觉得应该对表结构进行优化
强！！！！！
不是人