记录一段mysql代码-JZTXT

SELECT f . * , tmp.userid, tmp.cishu
FROM fx_user f, (

SELECT a.id, b.userid AS userid, COUNT( * ) AS cishu
FROM `fx_user` AS a
LEFT JOIN `fx_plan` AS b ON a.id = b.userid
AND b.fxtype =0
GROUP BY a.id
)tmp
WHERE f.id = tmp.id
AND priority =1
ORDER BY id ASC
LIMIT 0 , 50

　通过括号内左连接查询，得到一个结果集，这个结果集可以看作是一张虚拟表tmp：

 (

SELECT a.id, b.userid AS userid, COUNT( * ) AS cishu
FROM `fx_user` AS a
LEFT JOIN `fx_plan` AS b ON a.id = b.userid
AND b.fxtype =0
GROUP BY a.id
)tmp

　得到包含左表user表的全部行数据（tmp），类似于

1------a

2------b

3------c

4------NULL

即，左表全部列出，右边不存在的信息用null填空。

然后再用 select * from 表1，表2 where 表1.字段 = 表2.字段这种多表查询方式取得自己想要的结果。