@echo醉老仙，啥意思。。你 0 楼 和 11 楼，不都排除 password IS NULL 的用户了嘛（即，这些用户不会出现在结果中了）

@无名啊，说错了，是只要密码不为空的，is not null

@echo醉老仙，你再试试哪个效率高？

SELECT u.id, u.nick, count(uid.user)
  FROM user u
  LEFT JOIN uid ON u.id = uid.user AND uid.state = 0
 WHERE u.password IS NOT NULL
 GROUP BY u.id

SELECT u.id, u.nick, IFNULL(t.cnt, 0)
  FROM (SELECT user, count(*) cnt
          FROM uid
         WHERE state = 0
         GROUP BY user) t
 RIGHT JOIN user u ON u.id = t.user
 WHERE u.password IS NOT NULL

@无名啊，
第二个最快，有空了我放在tp里试试

11楼的速度

@echo醉老仙，你试试 uid 其他的表结构，能不能变快？比如：

（以下SQL建表时，都顺便复制了uid表数据。MySQL和SQLite应该都能用）

CREATE TABLE uid2 (user INT, uid INT, state INT, PRIMARY KEY (user, uid)) AS
SELECT user, uid, state
  FROM uid

CREATE TABLE uid3 (state INT, user INT, uid INT, PRIMARY KEY (state, user, uid)) AS
SELECT state, user, uid
  FROM uid

@无名啊，已经试用，速度很快，感谢