捣鼓一上午还写不出一条经常用到的只查一张表的 SQL 语句 求高手赐教

[lxjsmdc]

只查一张表，结构和数据如下


==========================
CREATE TABLE IF NOT EXISTS `member` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL,
`tuijianren_id` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `tuijianren_id` (`tuijianren_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;

INSERT INTO `member` (`id`, `name`, `tuijianren_id`) VALUES
(1, 'aaa', 0),
(2, 'bbb', 1),
(3, 'ccc', 1),
(4, 'ddd', 1),
(5, 'eee', 2),
(6, 'fff', 2),
(7, 'ggg', 3);
=========================

查询结果需求四个字段如下:
[一] id
[二] name
[三] tuijian_name
[四] total

id 和 name 是本表中所有记录的原有字段

tuijianren_name 是表中 tuijianren_id 对应本表 id 得出的 name,如果 tuijianren_id 是 0 或者 tuijianren_id 没有对应的 id 记录则为 null

total 是统计出本表中每条记录的 id 出现在其他记录 tuijianren_id 的总数

最终理想的查询结果如下


我左连接又换右连接怎么写都不对

SELECT m.id, m.name, COUNT( `m`.id ) AS total, m2.name AS tuijian_name
FROM member m
RIGHT JOIN member m2 ON m.tuijianren_id = m2.id
GROUP BY `m2`.id
LIMIT 0 , 30

请问如何写这条语句满足这个需求?
请老司机赐教一下 谢谢！！！

[syyy]

感觉应该是 count 左联

[klesh]

SELECT m.*, m2.name AS tuijian_name, (SELECT COUNT(*) FROM member m3 WHERE m3.tuijianren_id = m2.id) AS total
FROM member m
JOIN member m2 ON (m.tuijianren_id = m2.id)
LIMIT 0, 30

[lxjsmdc]

@klesh 灰常感谢

但是按照你给的语句， tuijianren_id=0 的记录就不显示出来了（记录不全）
能否再帮忙修改一下吗
谢谢

[cxe2v]

@lxjsmdc 你 id 本来就没有=0 的啊

[lynx]

select m.id, m.name, m.tuijianren_id, q.c as total from member as m
left outer join (
select tuijianren_id, count(*) as c from member
group by tuijianren_id
) as q on q.tuijianren_id = m.id

上面的 m.tuijian 再查一层换成 name 就可以了

[lxjsmdc]

@cxe2v 那怎么办 原本总共 7 条数据，现在只能查出 6 条

[chaegumi]

select A.*, B.name as tuijian_name,(select count(C.id) from member C where C.tuijianren_id=A.id) as total from member A LEFT JOIN member B on A.tuijianren_id=B.id

[jy02201949]

要是我肯定会先
INSERT INTO 'member' ('id', 'name', 'tuijianren_id') VALUES (0, 'null', 0);
曲线救国。。。

[sun2920989]

自连接加子查询 但是我应该会选择分拆 sql 在语言层做合并数组

[xuyinan503]

select a.id,a.name,b.name,count(c.id) as count
from member a
left join member b
left join member c
where a.tuijianren_id=b.id
and a.id=c.tuijianren_id

这样子就对了啊
感觉没那么复杂吧

[xuyinan503]

select a.id,a.name,b.name,count(c.id) as count
from member a
left join member b
on a.tuijianren_id=b.id
left join member c
on a.id=c.tuijianren_id
group by a.id,a.name,b.name

好久没写还是写错了

[tSQghkfhTtQt9mtd]

简直无法接受用 id 和 name 的时候突然来一个 tuijianren

[lxjsmdc]

原来要 join 两次或者外查询或者子查询
我想得太简单了
谢谢楼上各位

[billgreen1]

@liwanglin12 是的呀，最讨厌汉语拼音命名的了