表结构如下
其中 recomm_phone
可为空 若不为空 其值来源自phone字段(自身或者其它条数据的phone字段)
我们要取得数据是name
recomm_phone
num
(该phone
在recomm_phone
中出现的次数)
@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 |
可能我问的问题没有问清楚的原因,木有人回答,我发现我问问题都是自问自答了,奉上
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