前言
前几年给我哥小破厂写的破旧的生产管理,Mysql的查询长度经过无数次迭代已经长到让人发指。然后就寻思抽空重构一下
一、对联表查询的不满:
比如这种,
select
/*参数区*/
a.*,b.name,c.count as countc
,(select count(1) from d where status=isdo) as countd
from
/*表1*/
(select * from a1 union select * from a2 union select * from a3 union ... ) a
/*表2*/
left join b on b.id=a.bid
/*表3*/
left join (select count(1),xid from c1 group xid ) c on c.xid=a.id
/*条件区*/
where a.createtime>"yyy-MM-dd" order by a.createtime desc
1、很长: 十几个表join,穿插了count()、订单表拆分,阿西吧
2、维护不方便: 跑出来,复制到SQL编辑器,然后敲回车,挨行看很烦
3、可读性差:光是select后的a.*,b.name…这些就长得离谱,可读性差。
4、重用性不高:比如产品的 规格、材质、库存 这些基本信息是必要的,写那么长的TSQL往往只是满足一个详情页或者一个打印需求。其他页面又得另外写。
二、子查询优化思路
我就寻思,是否可以用业务类来写,一表一函数,一个dt 不断往里写,今天没事随便构思了一下。
主表
public static DataTable GetSchools()
{
//string str="select * from Schools";
//string param="*"; string tablename="Schools"; orderby="createtime desc";
//int pagesize=30; int pagecount=x;int startIndex=30*x-1;int endIndex=30*(x+1)-1;
//分页、排序 主表属性
DataTable dt=GetListByPage(param,tabelename,strWhere, orderby, startIndex, endIndex).Tables[0];
//分表往主表join
GetTeachers(ref dt);
//无脑join... Getstudents(ref dt);... Getrooms(ref dt);
return dt
}
子表 往 主表dt里塞东西
public static void GetTeachers(ref DataTable dt)
{
//第一步,取 id in 范围
var _in_ids = dt.AsEnumerable().Select(row => row.Field<int>("id")).ToList();
string _ids = string.Join(",", _in_ids);
//第二部,查出子表数据
DataTable dtsub=//查出老师 $"select count(1) as counts,schoolid from teachers where schoolid in {_ids } group by schoolid"
var sublist = dtsub.AsEnumerable().ToList();//为了linq
//第三部,写入主datatable
dt.Columns.Add("teacounts", typeof(string));//主dt加列
//循环主表 写给主表
dt.AsEnumerable().ToList().ForEach(row =>
{
row["teacounts"] = "0人";//默认个值,相当于ifnull(counts,0)
//匹配写入
var subrow = sublist.Find(p => p.Field<int>("schoolid") == row.Field<int>("id"));
if(subrow!=null)
row["teacounts"] = subrow.Field<int>("counts") + "人";
});
}
public static void GetStudents(ref DataTable dt){}
public static void GetRooms(ref DataTable dt){}
思路:
1、代码结构清晰了:在教师类Teacher.cs, 做主表、取数、取名、取职称、取年龄、入职时间等,写步骤函数。需要教师信息,直接去Teacher.cs类里找函数。
2、重用性高了:比如增加 新功能-课程管理,学生、教师、教室子查询函数现成的,课程查询直接调用
3、可读性高了: 数据接口F12到主查询,然后 GetTeachers、GetStudents、GetRooms,一目了然,层次分明
4、耦合低了:夹杂着join很长的TSQL,改一下可能就废了,耦合性太高。对比之前,独表独改,代码优美了不少