一个麻烦的统计sql语句，用到了分区试图（t_workcheck联合了26张表）。这样查询的时用到表的数量超过了256个。不知道哪位能够给简化一下

[hblzq]

select t_Department.DepName,t_WorkGroup.GroupName,t.name, t.WorkID,t_WorkType.WorkTypeName,
(select count(*) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '0' and CheckDate between '2007-09-21' and '2007-09-25') as BanC0,
(select sum(WorkMinutes)+sum(PatchTime) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '0' and CheckDate between '2007-09-21' and '2007-09-25') as BanC02,
(select CONVERT(decimal(10,2),avg(convert(float,WorkMinutes))+avg(convert(float,PatchTime))) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '0' and CheckDate between '2007-09-21' and '2007-09-25') as BanC03, (select count(*) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '1' and CheckDate between
'2007-09-21' and '2007-09-25') as BanC4,
(select sum(WorkMinutes)+sum(PatchTime) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '1' and CheckDate between '2007-09-21' and '2007-09-25') as BanC42,
(select CONVERT(decimal(10,2),avg(convert(float,WorkMinutes))+avg(convert(float,PatchTime))) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '1' and CheckDate between '2007-09-21' and '2007-09-25') as BanC43,
(select count(*) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '2' and CheckDate between '2007-09-21' and '2007-09-25') as BanC8,
(select sum(WorkMinutes)+sum(PatchTime) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '2' and CheckDate between '2007-09-21' and '2007-09-25') as BanC82,
(select CONVERT(decimal(10,2),avg(convert(float,WorkMinutes))+avg(convert(float,PatchTime))) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '2' and CheckDate between '2007-09-21' and '2007-09-25') as BanC83,
(select count(*) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '3' and CheckDate between '2007-09-21' and '2007-09-25') as BanCn,
(select sum(WorkMinutes)+sum(PatchTime) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '3' and CheckDate between '2007-09-21' and '2007-09-25') as BanCn2,
(select CONVERT(decimal(10,2),avg(convert(float,WorkMinutes))+avg(convert(float,PatchTime))) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and BanCID = '3' and CheckDate between '2007-09-21' and '2007-09-25') as BanCn3,
(select count(*) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and CheckDate between '2007-09-21' and '2007-09-25') as total,
(select sum(WorkMinutes)+sum(PatchTime) from t_WorkCheck where t_WorkCheck.EmployeID = t.EmployeID and CheckDate between '2007-09-21' and '2007-09-25') as total2,
(select CONVERT(decimal(10,2),avg(convert(float,WorkMinutes))+avg(convert(float,PatchTime))) from t_WorkCheck where
t_WorkCheck.EmployeID = t.EmployeID and CheckDate between '2007-09-21' and '2007-09-25') as total3 from (select distinct EmployeID,WorkID,Name,DepID,WorkTypeID,WorkGroupID,ShowOrder from t_Employe where DelFlag = '0' and CardID <> '' ) t
left join t_Department on t.DepID=t_Department.DepID left join t_WorkType on t.WorkTypeID = t_WorkType.WorkTypeID left join t_WorkGroup on t.DepID = t_WorkGroup.DepID and t.WorkGroupID = t_WorkGroup.GroupID order by
t.DepID,t.WorkGroupID,t.ShowOrder

[hblzq]

我用临时表试过，把在分区视图中的查询到的数据放到临时表中，再对临时表进行统计。这样可以查询。但感觉太麻烦了。不知道大家有没有其他好的办法。

[Limpire]

看起来密密麻麻

[dawugui]

写个代码循环取一下.

你都有这水平了,上百个表的计算,应该自己能解决的.

[yelang771]

还好，不过偶不知道怎么优化

[hblzq]

只是想找个简单的办法，在一个sql语句里面看能不能实现，这样用的时间稍微短一些。循环取那就和用临时表效果差不多了。

[hblzq]

这里面t_workcheck表用的是分区视图。他是26个表连在一起的。所以查询的时候报错超过了256个表的连接限制。
我就想改一下，只要是t_workcheck表用的次数少一些，就可以避免掉对查询语句256个表的连接限制了。

[brother2605]

写个函数，取代t_workcheck

[hblzq]

有没有别的办法？？？

[dawugui]

什么查询要用到200多个表?

不能从业务上优化?

[hblzq]

to：dawugui
t_workcheck是一个联合视图，联合了按月划分的两年的数据，所以实际查询的时候连接的表的数量就很大了。