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

› MySQL 5.5 Community Server

› MySQL 5.6 Community Server

› Percona Configuration Wizard

› XtraBackup 搭建主从复制

Great Sites on MySQL

› Percona

› MySQL Performance Blog

› Severalnines

推荐管理工具

› Sequel Pro

› phpMyAdmin

推荐书目

› MySQL Cookbook

MySQL 相关项目

› MariaDB

› Drizzle

参考文档

› http://mysql-python.sourceforge.net/MySQLdb.html

This topic created in 3853 days ago, the information mentioned may be changed or developed.

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

==========================
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

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

14 replies • 2015-12-10 21:34:20 +08:00

syyy

Dec 10, 2015

感觉应该是 count 左联

klesh

Dec 10, 2015

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

Dec 10, 2015

@klesh 灰常感谢

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

cxe2v

Dec 10, 2015

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

lynx

Dec 10, 2015

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

Dec 10, 2015

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

chaegumi

Dec 10, 2015

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

Dec 10, 2015

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

sun2920989

Dec 10, 2015

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

xuyinan503

Dec 10, 2015

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

Dec 10, 2015

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

Dec 10, 2015

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

lxjsmdc

Dec 10, 2015

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

billgreen1

Dec 10, 2015

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

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

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