mysql - 一条sql语句 获取下列需求。

Question

表结构如下

其中 recomm_phone 可为空 若不为空 其值来源自phone字段(自身或者其它条数据的phone字段)
我们要取得数据是name recomm_phone num(该phone在recomm_phone中出现的次数)

Answer 1

@stoneworld 不知道你哪个性能如何，估计数据量大的时候有点慢。

---

SQL Fiddle

MySQL 5.6 Schema Setup:

CREATE TABLE `reg` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `phone` varchar(11) NOT NULL,
  `recomm_phone` varchar(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
;

insert into `reg`
( name, phone, recomm_phone)
values
( 'james', '12345678901', null ),
( 'fancy', '12345678901', '12345678901'),
( 'jessy', '97969808707', '12345678901'),
( 'jack', '91724917324', null),
( 'luke', '19247123472', '91724917324')
;

Query 1:

select  name,
        phone,
        a.recomm_phone,
        ifnull(b.num, 0) as num
from    reg a
    left join (
        select  recomm_phone,
                count(recomm_phone) as num
        from    reg
        where   recomm_phone is not null
        group
            by  recomm_phone
    ) b on a.phone = b.recomm_phone
order
    by  name

Results:

name	phone	recomm_phone	num
fancy	12345678901	12345678901	2
jack	91724917324	(null)	1
james	12345678901	(null)	2
jessy	97969808707	12345678901	0
luke	19247123472	91724917324	0

Answer 2

可能我问的问题没有问清楚的原因，木有人回答，我发现我问问题都是自问自答了，奉上

SELECT name,phone,recomm_phone,(SELECT COUNT(*) FROM reg WHERE recomm_phone=o.phone) 
as num FROM reg AS o 

SELECT COUNT( recomm_phone) AS num, recomm_phone FROM reg
GROUP BY recomm_phone ORDER BY num DESC