1. 查询字段建索引 如 DictId,DictValue 建复合索引,视具体查询条件 2. select count(*) 用 select sum() case when .. then 1 else 0 end 代替 3. PlanId in (..) 用 exists(select 1 from gs_ModelPlan Where PlanStatus='1' AND ID = PlanID) 代替 4. 提高表结构字段可读性 等补充
常用查询字段用 not null 除非特殊情况. 否则到时索引失效
调用同一个表的次数有点多, 楼主可换作 case when ...then 1 else 0 end...这样的写法
(Select count(*) From (Select Distinct ZA0100 From VU01 Where isnull(FA0128,'')=A.ZA0100 and ZZ0005='D' and ZZ0006='4' and History='1' and PlanId in (Select ID From gs_ModelPlan Where PlanStatus='1')) t3) as grNum,(Select count(*) From (Select Distinct ZA0100 From VU01 Where isnull(FA0128,'')=A.ZA0100 and ZZ0005='C' and ZZ0006='4' and History='1' and PlanId in (Select ID From gs_ModelPlan Where PlanStatus='1')) t1) as frNum,(Select count(*) From (Select Distinct ZA0100 From VU01 Where isnull(FA0128,'')=A.ZA0100 and ZZ0005='B' and ZZ0006='4' and History='1' and PlanId in (Select ID From gs_ModelPlan Where PlanStatus='1')) t1) as zgNum,(Select count(*) From (Select Distinct ZA0100 From VU01 Where isnull(FA0128,'')=A.ZA0100 and ZZ0005='A' and ZZ0006='4' and History='1' and PlanId in (Select ID From gs_ModelPlan Where PlanStatus='1')) t2) as rcNum,------------------ 考虑用中间表,一个分组就完成 了,
2. select count(*) 用 select sum() case when .. then 1 else 0 end 代替
3. PlanId in (..) 用 exists(select 1 from gs_ModelPlan Where PlanStatus='1' AND ID = PlanID) 代替
4. 提高表结构字段可读性
等补充
楼主可换作
case when ...then 1 else 0 end...这样的写法
还不如把重点集中在没有 select count()上面,分别求出合计值来即可。
这种SQL语句很难维护,容易出错,而且很难优化。
“还不如把重点集中在每个单独的 select count()上面,分别求出值来,再加以使用即可。 ”
考虑用中间表,一个分组就完成 了,