Mysql的收获

今天公司的业务需求中，遇到一个很郁闷的情况。

有一个申请记录，缺少申请原因的字段。

我以为很简单，采用left jion 很容易获取到申请原因。

SELECT a.*,b.RealName,c.DeptName,d.FlowApplyNote as n1,e.FlowApplyNote as n2, f.FlowApplyNote as n3 ,g.FlowApplyNote as n4, h.FlowApplyNote as n5 FROM `AttApplyRecords` as a LEFT JOIN `SysUsers` as b ON a.FlowApplyUserId =
b.UserId LEFT JOIN `SysDepts` as c ON c.DeptId = b.DeptId LEFT JOIN `FlowApplyLeaves` as d ON a.FlowApplyId = d.FlowApplyId and a.FlowId = d.FlowId and d.FlowApplyNote is not null LEFT JOIN `FlowApplyOffDays` as e ON a.FlowApplyId = e.FlowApplyId and a.FlowId = e.FlowId and e.FlowApplyNote is not null LEFT JOIN `FlowApplyNotAtts` as f ON a.FlowApplyId = f.FlowApplyId and a.FlowId = f.FlowId and f.FlowApplyNote is not null LEFT JOIN `FlowApplyOverTimes` as g ON a.FlowApplyId = g.FlowApplyId and a.FlowId = g.FlowId and g.FlowApplyNote is not null LEFT JOIN `FlowApplyTravels` as h ON a.FlowApplyId = h.FlowApplyId and a.FlowId = h.FlowId and h.FlowApplyNote is not null WHERE a.ApplyStatus = 2 ORDER BY b.RealName DESC

 

产生的结果如下

 

后面的n1 n2 n3 n4 n5 不好取。他们应该是申请理由。

于是想到合并字段。mysql中合并可以采用concat 。但是这要求字段不能为Null,否则直接返回Null

于是又抓取mysql的 ifNULL函数。成功实现上门的效果。

于是产生下面的是sql语句

 

SELECT a.*,b.RealName,c.DeptName,IFNULL(d.FlowApplyNote,'') as n1, IFNULL(e.FlowApplyNote,'') as n2, IFNULL(f.FlowApplyNote,'') as n3 , IFNULL(g.FlowApplyNote,'') as n4, IFNULL(h.FlowApplyNote,'') as n5,concat(n1,n2,n3,n4,n5) as n FROM `AttApplyRecords` as a LEFT JOIN `SysUsers` as b ON a.FlowApplyUserId = 
b.UserId LEFT JOIN `SysDepts` as c ON c.DeptId = b.DeptId LEFT JOIN `FlowApplyLeaves` as d ON a.FlowApplyId = d.FlowApplyId and a.FlowId = d.FlowId and d.FlowApplyNote is not null LEFT JOIN `FlowApplyOffDays` as e ON a.FlowApplyId = e.FlowApplyId and a.FlowId = e.FlowId and e.FlowApplyNote is not null LEFT JOIN `FlowApplyNotAtts` as f ON a.FlowApplyId = f.FlowApplyId and a.FlowId = f.FlowId and f.FlowApplyNote is not null LEFT JOIN `FlowApplyOverTimes` as g ON a.FlowApplyId = g.FlowApplyId and a.FlowId = g.FlowId and g.FlowApplyNote is not null LEFT JOIN `FlowApplyTravels` as h ON a.FlowApplyId = h.FlowApplyId and a.FlowId = h.FlowId and h.FlowApplyNote is not null WHERE a.ApplyStatus = 2 ORDER BY b.RealName DESC

 

但是提示错误。

 

困扰了一会，又想到Mysql 的二次查询。

 

于是最后 的sql语句是

 

select *,concat(t.n1,t.n2,t.n3,t.n4,t.n5) as FlowApplyNote from (SELECT a.*,b.RealName,c.DeptName,IFNULL(d.FlowApplyNote,'') as n1, IFNULL(e.FlowApplyNote,'') as n2, IFNULL(f.FlowApplyNote,'') as n3 , IFNULL(g.FlowApplyNote,'') as n4, IFNULL(h.FlowApplyNote,'') as n5 FROM `AttApplyRecords` as a LEFT JOIN `SysUsers` as b ON a.FlowApplyUserId =
b.UserId LEFT JOIN `SysDepts` as c ON c.DeptId = b.DeptId LEFT JOIN `FlowApplyLeaves` as d ON a.FlowApplyId = d.FlowApplyId and a.FlowId = d.FlowId and d.FlowApplyNote is not null LEFT JOIN `FlowApplyOffDays` as e ON a.FlowApplyId = e.FlowApplyId and a.FlowId = e.FlowId and e.FlowApplyNote is not null LEFT JOIN `FlowApplyNotAtts` as f ON a.FlowApplyId = f.FlowApplyId and a.FlowId = f.FlowId and f.FlowApplyNote is not null LEFT JOIN `FlowApplyOverTimes` as g ON a.FlowApplyId = g.FlowApplyId and a.FlowId = g.FlowId and g.FlowApplyNote is not null LEFT JOIN `FlowApplyTravels` as h ON a.FlowApplyId = h.FlowApplyId and a.FlowId = h.FlowId and h.FlowApplyNote is not null WHERE a.ApplyStatus = 2 ORDER BY b.RealName DESC ) t

 

经过这些折腾，对以前的表设计，颇有怨言。这样的一个sql，增加了复杂度，还没写注释，让人怎么看啊。