mysql查询 经典_mysql经典查询

需求：根据任务单编号分组打印四类单据单据

数据库表结构：

(1)四种单据分别在四个不同的表中

(2)每个表中都有一个表示该单据属于哪次任务的任务单编号

查询之前的分析：

(1)如果要分组，可以考虑把这四类表放在一个视图中进行

*** 如何把它们放在同一个视图中呢？

只需要使用连接即可

*** 如何保证连接的时候所有需要的结果都被放到视图中

使用全连接，但是Mysql没有全连接，那就需要使用替代方法 full join =left join+union+right union，即先进性左联接，然后进行右连接，再把连接结果合并

***如何保证全连接后所有表的数据都不在同一行

on后的条件采用主键=主键的形式，而且此主键是UUID形式的(即保证连接条件永远不可能相同)

***四张表的全连接有许多的重复部分，这就会导致修改一处还要修改其它地方，而且其它地方还不止一处，如何减少修改量呢？

使用两两连接的方式，比如四张表abcd，先ab全连接得到e、cd全连接得到f，然后ef全连接，这样做的好处是修改时只需要修改ef的左联接即可，然后把左联接全部复制到把ef的左联接改成右连接即可

(2)如果要分组，可以考虑把它们的任务单编号放在视图中的某一列，然后对此列进行分组

*** 如何把四种表的任务单列放在试图中的同一列？

使用CONCAT即可，准确的说是利用concat和union在一起时的特性来让实现这个目的

这个特性是 当union的时候，union会把类似CONCAT(wltt) alltt1这样形式同名列合并成一列 SELECT u1.*,u2.*,CONCAT(alltt1) alltt3  FROM  ( SELECT *  ,CONCAT(wltt) alltt1 FROM  ( SELECT wl.id wlid,wl.no wlno,wl.time,wl.truckWeight,wl.totalWeight,wl.goodsWeight,wl.taskTableId wltt,wl.workPoint wlwp,wl.vin,gt.name,gt.no gtno,d.no dno,d.name dname,gm.no gmno,gm.name gmname  FROM weigninglist wl,goodsType gt,driver d,goodsMaster gm WHERE wl.goodsTypeId = gt.goodsTypeId AND wl.driverId=d.driverId AND wl.goodsMasterId = gm.goodsMasterId )  AS wl2 LEFT JOIN  (SELECT  id sid ,s.no sno,location,payType,payAmount,statementTime,taskTableId stt,workPoint swp FROM statement s  ) AS s2 ON wlid=sid   UNION SELECT *  ,CONCAT(stt) alltt1 FROM  ( SELECT wl.id wlid,wl.no wlno,wl.time,wl.truckWeight,wl.totalWeight,wl.goodsWeight,wl.taskTableId wltt,wl.workPoint wlwp,wl.vin,gt.name,gt.no gtno,d.no dno,d.name dname,gm.no gmno,gm.name gmname  FROM weigninglist wl,goodsType gt,driver d,goodsMaster gm WHERE wl.goodsTypeId = gt.goodsTypeId AND wl.driverId=d.driverId AND wl.goodsMasterId = gm.goodsMasterId )  AS wl2 RIGHT JOIN  (SELECT  id sid ,s.no sno,location,payType,payAmount,statementTime,taskTableId stt,workPoint swp FROM statement s  ) AS s2 ON wlid=sid   )u1 LEFT JOIN ( SELECT * ,CONCAT(ttt) alltt2 FROM  (SELECT t.id tid,t.no tno,t.who,t.way ,t.reason ,t.time ttime,t.taskTableId ttt,t.workPoint twp,d.name dname,d.no dno FROM trafficTicket t,driver d WHERE t.driverId = d.driverId) AS t2 LEFT JOIN  (SELECT w.id wid,w.no wno,d.no wdno,d.name wdname ,c.name cname ,w.time wtime,w.taskTableId wtt,w.workPoint wwp,w.guardian FROM workbill w,company c,driver d WHERE w.driverId = d.driverId AND d.companyId = c.companyId) AS w2 ON wid=tid UNION SELECT * ,CONCAT(wtt) alltt2 FROM  (SELECT t.id tid,t.no tno,t.who,t.way ,t.reason ,t.time ttime,t.taskTableId ttt,t.workPoint twp,d.name dname,d.no dno FROM trafficTicket t,driver d WHERE t.driverId = d.driverId) AS t2 RIGHT JOIN  (SELECT w.id wid,w.no wno,d.no wdno,d.name wdname ,c.name cname ,w.time wtime,w.taskTableId wtt,w.workPoint wwp,w.guardian FROM workbill w,company c,driver d WHERE w.driverId = d.driverId AND d.companyId = c.companyId) AS w2 ON wid=tid )u2  ON wlid = wid     UNION(ef 左右连接合并) SELECT u1.*,u2.* ,CONCAT(alltt2) alltt3 FROM  ( SELECT *  ,CONCAT(wltt) alltt1 FROM  ( SELECT wl.id wlid,wl.no wlno,wl.time,wl.truckWeight,wl.totalWeight,wl.goodsWeight,wl.taskTableId wltt,wl.workPoint wlwp,wl.vin,gt.name,gt.no gtno,d.no dno,d.name dname,gm.no gmno,gm.name gmname  FROM weigninglist wl,goodsType gt,driver d,goodsMaster gm WHERE wl.goodsTypeId = gt.goodsTypeId AND wl.driverId=d.driverId AND wl.goodsMasterId = gm.goodsMasterId )  AS wl2 LEFT JOIN  (SELECT  id sid ,s.no sno,location,payType,payAmount,statementTime,taskTableId stt,workPoint swp FROM statement s  ) AS s2 ON wlid=sid   UNION SELECT *  ,CONCAT(stt) alltt1 FROM  ( SELECT wl.id wlid,wl.no wlno,wl.time,wl.truckWeight,wl.totalWeight,wl.goodsWeight,wl.taskTableId wltt,wl.workPoint wlwp,wl.vin,gt.name,gt.no gtno,d.no dno,d.name dname,gm.no gmno,gm.name gmname  FROM weigninglist wl,goodsType gt,driver d,goodsMaster gm WHERE wl.goodsTypeId = gt.goodsTypeId AND wl.driverId=d.driverId AND wl.goodsMasterId = gm.goodsMasterId )  AS wl2 RIGHT JOIN  (SELECT  id sid ,s.no sno,location,payType,payAmount,statementTime,taskTableId stt,workPoint swp FROM statement s  ) AS s2 ON wlid=sid   )u1 RIGHT JOIN ( SELECT * ,CONCAT(ttt) alltt2 FROM  (SELECT t.id tid,t.no tno,t.who,t.way ,t.reason ,t.time ttime,t.taskTableId ttt,t.workPoint twp,d.name dname,d.no dno FROM trafficTicket t,driver d WHERE t.driverId = d.driverId) AS t2 LEFT JOIN  (SELECT w.id wid,w.no wno,d.no wdno,d.name wdname ,c.name cname ,w.time wtime,w.taskTableId wtt,w.workPoint wwp,w.guardian FROM workbill w,company c,driver d WHERE w.driverId = d.driverId AND d.companyId = c.companyId) AS w2 ON wid=tid UNION SELECT * ,CONCAT(wtt) alltt2 FROM  (SELECT t.id tid,t.no tno,t.who,t.way ,t.reason ,t.time ttime,t.taskTableId ttt,t.workPoint twp,d.name dname,d.no dno FROM trafficTicket t,driver d WHERE t.driverId = d.driverId) AS t2 RIGHT JOIN  (SELECT w.id wid,w.no wno,d.no wdno,d.name wdname ,c.name cname ,w.time wtime,w.taskTableId wtt,w.workPoint wwp,w.guardian FROM workbill w,company c,driver d WHERE w.driverId = d.driverId AND d.companyId = c.companyId) AS w2 ON wid=tid )u2  ON wlid = wid       ORDER BY alltt3