The harvest of MySQL

Today the company's business needs, encountered a very depressed situation.

There is a record of the application, which is missing the reason for the field.

I thought it was easy to use the left jion to get the reason for the application easily.

SELECT A.*,b.realname,c.deptname,d.flowapplynote as n1,e.flowapplynote as N2, F.flowapplynote as N3, G.flowapplynote as N 4, H.flowapplynote as N5 from ' Attapplyrecords ' as a left JOIN ' SysUsers ' as B on a.flowapplyuserid =
B.userid left join ' sysdepts ' as C in C.deptid = B.deptid left join ' flowapplyleaves ' as D on A.flowapplyid = D.flowapplyi D and A.flowid = D.flowid and d.flowapplynote are not null left joins ' Flowapplyoffdays ' as E on a.flowapplyid = e.flowapply Id and A.flowid = E.flowid and e.flowapplynote are not null left JOIN ' Flowapplynotatts ' as f on a.flowapplyid = F.flowappl Yid and A.flowid = F.flowid and f.flowapplynote are not null left joins ' Flowapplyovertimes ' as g on A.flowapplyid = G.flowa Pplyid and A.flowid = G.flowid and g.flowapplynote are not null left joins ' Flowapplytravels ' as h on A.flowapplyid = H.flow Applyid and A.flowid = H.flowid and H.flowapplynote is isn't null WHERE a.applystatus = 2 ORDER by B.realname DESC

The resulting results are as follows

The back of the N1 N2 N3 N4 N5 difficult to take. They should be the reason for the application.

Then I thought of the merge field. MySQL merge can use concat. However, this requires that the field cannot be null, otherwise it returns null directly

Then crawled the MySQL ifnull function. Successful implementation of the door effect.

So the following is the SQL statement

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 F ROM ' Attapplyrecords ' as a left JOIN ' SysUsers ' as B on a.flowapplyuserid =
B.userid left join ' sysdepts ' as C in C.deptid = B.deptid left join ' flowapplyleaves ' as D on A.flowapplyid = D.flowapplyi D and A.flowid = D.flowid and d.flowapplynote are not null left joins ' Flowapplyoffdays ' as E on a.flowapplyid = e.flowapply Id and A.flowid = E.flowid and e.flowapplynote are not null left JOIN ' Flowapplynotatts ' as f on a.flowapplyid = F.flowappl Yid and A.flowid = F.flowid and f.flowapplynote are not null left joins ' Flowapplyovertimes ' as g on A.flowapplyid = G.flowa Pplyid and A.flowid = G.flowid and g.flowapplynote are not null left joins ' Flowapplytravels ' as h on A.flowapplyid = H.flow Applyid and A.flowid = H.flowid and H.flowapplynote is isn't null WHERE a.applystatus = 2 ORDER by B.realname DESC

But the hint is wrong.

Bothered for a while, and think of MySQL two times query.

So the last SQL statement is

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 in C.deptid = B.deptid left join ' flowapplyleaves ' as D on A.flowapplyid = D.flowapplyi D and A.flowid = D.flowid and d.flowapplynote are not null left joins ' Flowapplyoffdays ' as E on a.flowapplyid = e.flowapply Id and A.flowid = E.flowid and e.flowapplynote are not null left JOIN ' Flowapplynotatts ' as f on a.flowapplyid = F.flowappl Yid and A.flowid = F.flowid and f.flowapplynote are not null left joins ' Flowapplyovertimes ' as g on A.flowapplyid = G.flowa Pplyid and A.flowid = G.flowid and g.flowapplynote are not null left joins ' Flowapplytravels ' as h on A.flowapplyid = H.flow Applyid and A.flowid = H.flowid and h.flowapplynote is not null WHERE A.applystatus = 2 ORDER by b.realname DESC) t

After these toss, to the previous table design, quite a complaint. This kind of SQL, adds complexity, has not written the comment, lets the person look.

The harvest of MySQL