1
feikeq OP 还是一定要子查询?
|
2
Ouyangan 2016-06-27 11:18:50 +08:00
没看懂提问意思
|
3
birdccc 2016-06-27 11:19:21 +08:00
看不懂你这结果集是怎么来的啊 。
|
4
techme 2016-06-27 11:24:32 +08:00
是不是在本表中查询每个 id 与 fid 的大于零的关联数 要用 join 吗?
|
5
delavior 2016-06-27 11:24:47 +08:00
去重怎么实现?一般都是分组吧
|
6
dxfree 2016-06-27 11:27:45 +08:00
select distinct(id),distinct(name),sum(*)
from table_name --where optional group by distinct(id),distinct(name) 大概是这样吧 |
7
sunchen 2016-06-27 11:36:36 +08:00
|
8
feikeq OP 正确的查询结果
+----------------------+ | id | name | count | +----------------------+ | 101 | aaaa | 2 | +----------------------+ | 102 | bbbb | 1 | +----------------------+ select id, name, count(fid) as sum from table group by 'fid' 直接 GROUP BY 结果 +----------------------+ | id | name | count | +----------------------+ | 104 | dddd | 2 | +----------------------+ | 103 | cccc | 1 | +----------------------+ 这样是不对的,我要的是 101 用户 aaaa 带来 2 个人,而不是 104 这用户。 |
9
feikeq OP 能不用子查询做到吗?我想提高查询性能不想用子查询,数据库表设计就是这样的也不能去再修改。
|
11
feikeq OP select id, name, count(fid) as count from table group by 'fid'
查出来虽然 count 对了,但 id 和 name 不匹配. |
12
fireapp 2016-06-27 11:44:13 +08:00
|
15
lxy 2016-06-27 12:00:00 +08:00
子查询统计一下 fid 数量,然后跟原表链接起来。我设原表为 t1 。
select id, name, t2.fid_count from t1 left join ( select fid, count(fid) as fid_count from t1 where fid is not null group by fid ) as t2 on t1.id=t2.fid where t2.fid is not null order by id |
17
txoooy 2016-06-27 13:07:59 +08:00
mysql> select * from ref1;
+----+------+-----+ | id | name | fid | +----+------+-----+ | 1 | aaa | 0 | | 2 | bbb | 1 | | 3 | ccc | 2 | | 4 | ddd | 1 | +----+------+-----+ 4 rows in set mysql> SELECT r1.id AS user_id, r1. NAME AS user_name, count(DISTINCT r2. NAME) AS ref_count FROM ref1 r1 INNER JOIN ref1 r2 ON r1.id = r2.fid GROUP BY r1. NAME; +---------+-----------+-----------+ | user_id | user_name | ref_count | +---------+-----------+-----------+ | 1 | aaa | 2 | | 2 | bbb | 1 | +---------+-----------+-----------+ 2 rows in set |
18
feikeq OP |
19
txoooy 2016-06-27 15:07:23 +08:00
你的表结构, 数据 和我一样吗? 数据库是 mysql 吗?
|
21
feikeq OP @txoooy 的方法查询要 0.07 秒,采用下面方法只需 0.02 秒
SELECT T1.`uid`,T1.`headimg`, T1.`nickname` ,T1.`referer` ,T2.`num` FROM `user_center` AS T1 LEFT JOIN ( SELECT C.`referer` AS uid, count(`referer`) AS num FROM `user_center` C, `user_open` O WHERE C.`uid`= O.`uid` AND O.`subscribe`=1 GROUP BY C.`referer` ) AS T2 ON T1.`uid`= T2.`uid` WHERE T2.`num` IS NOT NULL ORDER BY T2.`num` DESC |
22
txoooy 2016-06-27 15:22:20 +08:00
这个要分情况, 有些情况中利用子查询构建中间表可以提高性能, 不过你现阶段并不需要考虑这些, sql 优化东西太多了, 多写多看, 自然知道什么时候用什么方法
|
23
wavingclear 2016-06-27 16:41:37 +08:00
|
24
wavingclear 2016-06-27 16:44:25 +08:00
啊 17 楼已经写过了……
|