不错的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