比如 select a.xx from a left join b
on a.xx=b.xx where b.xx is null
和
select a.xx from a left join b
on a.xx=b.xx and b.xx is null这个两个有什么区别
on a.xx=b.xx where b.xx is null
和
select a.xx from a left join b
on a.xx=b.xx and b.xx is null这个两个有什么区别
第1个是JOIN后在WHERE
第2个是JOIN时满足2个条件
a.xx=b.xx and b.xx is null你测试一下结果
select a.xx ,b.xx ,充值,其他字段 from a left join b
on a.xx=b.xx where b.xx is null 这个可以区分a.xx为两类 一类为充值 ,另外一类为未充值,后面跟的其他字段??
on a.xx=b.xx where b.xx is null 假设xx为唯一标识的字段,上述SQL语句结果为A有B没有的记录
from
(SELECT b.player_id,right(type,1) as pz,position,gem1,gem2,magic3,reduce_dmg,monopoly
from cq_user a INNER JOIN cq_item b
on a.id=b.player_id and a.`level`>=120 and a.last_login>=20120301 and position>0 and position<19
) qq
LEFT JOIN
(select id_target,sum(case when number=120 then 1.9 when number=530 then 7.99 when number= 1075 then 15.99 when number=60 then 0.99 when number=125 then 1.99 when number=320 then 4.99 else 29.99 end ) as sum_cz
from e_money where time_stamp>=1203010000 and type in (6,8) and id_source>10000
group by id_target
) pp
on pp.id_target=qq.player_id
group by qq.player_id,sum_cz,pz,position,gem1,gem2,magic3,reduce_dmg,monopoly
select a.id,sum(case when number=120 then 1.9 when number=530 then 7.99 when number= 1075 then 15.99 when number=60 then 0.99 when number=125 then 1.99 when number=320 then 4.99 else 29.99 end ) as sum_cz ,
right(b.type,1) as pz,position,gem1,gem2,magic3,reduce_dmg,monopoly
FROM
cq_user a inner join cq_item b
on a.id=b.player_id and a.level>=120 and a.last_login>=20120301 and position>0 and position<=18
LEFT JOIN e_money c
on a.id=c.id_target and time_stamp>=1203010000 and c.type in (6,8) and id_source>10000
GROUP BY a.id,right(b.type,1),position,gem1,gem2,magic3,reduce_dmg,monopoly
这连个语句帮忙看看是不是一个意思? 还有就是前面的执行会出现错误 ([Err] 1064 - You have an error in your SQL syntax near 'SELECT b.player_id,right(type,1) as pz,position,gem1,gem2,magic3,reduce_dmg,mon' at line 3) 是在MYSQL上执行的