left join精要-白红宇

left join精要

阅读量：2445 次

发布时间：2019-05-10

本文共 3375 字，大约阅读时间需要 11 分钟。

SELECT (SELECT NAME FROM reg_users WHERE id = re.friendId) uname,(SELECT number FROM integral WHERE userid=re.friendId),

(SELECT modifiedCount FROM integral WHERE userid=re.friendId),(SELECT COUNT(*)

FROM integrallog log1,integral inte WHERE log1.integralId=l.id AND log1.STATUS=1 AND inte.userid=re.friendId),

(SELECT COUNT(*) FROM integrallog LOG2,integral inte1 WHERE log2.integralId=l.id AND log2.STATUS=0 AND

inte1.userid=re.friendId),

(SELECT onlineStatus FROM reg_users WHERE id=re.friendId),

re.myId

FROM

reg_users r LEFT JOIN integral i ON r.id=i.userid,relation re,

integral LEFT JOIN integrallog l ON integral.id=l.integralId WHERE

i.userid=2 AND i.userid=r.id AND i.userid=re.myId GROUP BY uname ORDER BY i.number DESC

后者

"SELECT (SELECT NAME FROM reg_users WHERE id=e.friendId) AS NAME" +    		",(SELECT number FROM integral WHERE userid=e.friendId) AS number" +    		",(SELECT modifiedCount FROM integral WHERE userid=e.friendId) AS modifiedcount," +    		"(SELECT COUNT(*) FROM integral n LEFT JOIN integrallog log1 ON n.id=log1.integralId WHERE n.userid=e.friendId AND log1.STATUS=1) AS win," +    		"(SELECT COUNT(*) FROM integral n LEFT JOIN integrallog log1 ON n.id=log1.integralId WHERE n.userid=e.friendId AND log1.STATUS=0) AS lost," +    		"(SELECT onlineStatus FROM reg_users WHERE id=e.friendId) AS onlineStatus," +    		"e.friendId" +    		" FROM reg_users r LEFT JOIN integral i ON r.id=i.userid " +    		"LEFT JOIN integrallog l ON i.id=l.integralId," +    		"relation e WHERE r.id=? AND r.id=e.myId GROUP BY r.name";   
    
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
  SELECT (SELECT NAME FROM reg_users WHERE id=e.friendId) AS NAME ,(SELECT sourse FROM murderrecord WHERE userid=e.friendId) AS number ,(SELECT WINQUANTITY+FAILQUANTITY FROM murderrecord WHERE userid=e.friendId) AS gamescount, (SELECT WINQUANTITY FROM murderrecord WHERE userid=e.friendId) AS win, (SELECT FAILQUANTITY FROM murderrecord WHERE userid=e.friendId) AS lost, (SELECT onlineStatus FROM reg_users WHERE id=e.friendId) AS onlineStatus, e.friendId  FROM   relation e LEFT JOIN reg_users r ON e.friendId=r.id LEFT JOIN murderrecord m ON r.id=m.userid   WHERE e.myid=2 GROUP BY r.name ORDER BY m.SOURSE, (m.WINQUANTITY/(m.WINQUANTITY+m.FAILQUANTITY+m.EXITQUANTITY)) DESC,m.EXITQUANTITY ASC,m.WINQUANTITY DESC;  
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
   
  
   
  仔细看会发现一个没有left join一个用了 
  
   
  首先on与where的区别 
  
   
  经过我的理解是他们 分工明确 
 1.  
   
  on 就像拔河比赛 他把各个人员给串连起来 
  
   
  where 就是你要具体分成为哪个队伍 
  
   
  他们 的判断条件互不干扰 
  
   
  而有join与无join区别 
  
   
  这个也很明显 
 2.  
   
  期末考试小明生病了 错过了，而其它学生顺利参加考试 
  
   
  最终成绩打印出来了 
  
   
  但是如果就因为小明没有成绩把他排除在成绩单以外似乎并不符合逻辑 
  
   
  此时加入join以person为左GRADES为右即便小明没有成绩 依然显示在成绩单中，只是影响他的是排名而已 
  
   
   
 3.  
   
  执行的顺序是where优先级大于on 此处您可以纠正我的错误 而on大于select thisarea from table，优先级要大于thisarea，是反着来的 
  
   
   
  
   
  我的第一个sql其实是有错误的 
  
   
   
  
   
  4.找到要匹配的点开始，比如有5个表需要关联的查 从哪开始这个很重要 不要乱用 比如人员必须要显示，比如朋友表中的朋友名称必须要显示这两个结果都是person 
  
   
  表但起点是不一样的， 
  
   
  如第一种情况 from person p left join score s on p.id=s.pid left join relation r on s.pid=r.friendid 
  
   
  第二种情况 from relation r left join person p on r.friendid=p.id left join score s on r.friendid=s.pid 
  
   
  得到的结果往往不一致 这个很重要需要注意的一点