不错的mysql联表多表查询_MySQL

bitsCN.com

不错的mysql联表多表查询

 

mysql联表多表查询，以前查询的时候，可以直接select * from tablea as a ,tableb.as b where a.cc=b.cc.今天分享一个新奇的写法，而且对于百W数据执行效率可是相当的快，下面分享一下联表多表查询的几个语句：

     

SQL代码

#在Tbl_User中删除非法积分数据的QQ的邀请好友QQ资料  

delete from Tbl_User where FQQ IN  

(select FDesQQ from Tbl_InviteHistory where FSrcQQ IN  

(select DISTINCT(FSrcQQ) from Tbl_ScoreDetail where FScore>'250' and FStrategy='4'))  

  

#在Tbl_User中删除非法积分数据的QQ资料  

delete from Tbl_User where FQQ IN  

(select DISTINCT(FSrcQQ) from Tbl_ScoreDetail where FScore>'250' and FStrategy='4')  

  

#在Tbl_InviteHistory中删除所有非法积分数据的邀请记录  

delete from Tbl_InviteHistory where FSrcQQ IN  

(select DISTINCT(FSrcQQ) from Tbl_ScoreDetail where FScore>'250' and FStrategy='4')  

 

#在Tbl_Score中删除非法积分数据的总积分  

delete from Tbl_Score where FQQ IN  

(select DISTINCT(FSrcQQ) from Tbl_ScoreDetail where FScore>'250' and FStrategy='4')  

  

#在Tbl_ScoreDetail中删除非法积分明细  

delete from Tbl_ScoreDetail where FScore>'250' and FStrategy='4'  

 

#删除Tbl_Score表里没有Tbl_User的用户  

delete from Tbl_Score where FQQ not IN (select FQQ from Tbl_User)  

delete from Tbl_ScoreDetail where FSrcQQ not IN (select FQQ from Tbl_User) 

 

//查兑换码大于20的用户明细表

SELECT FQQ,FCode,FApplyTime FROM Tbl_Code where FQQ in (SELECT a.FQQ FROM (SELECT COUNT(*) as num,FQQ FROM `Tbl_Code` GROUP BY FQQ HAVING num>20) as a) and FStatus='1' 

 

bitsCN.com