求一个 SQL 语句不知去重还是分组来实现

[feikeq]

现在学过的东西想再用时却忘干净了，就像解一元二次方程，小时候很会解，现在怎么不知怎么开始解。。。。


原表内容
+--------------------+
| id | name | fid |
+--------------------+
| 101 | aaaa | |
+--------------------+
| 102 | bbbb | 101 |
+--------------------+
| 103 | cccc | 102 |
+--------------------+
| 104 | dddd | 101 |
+--------------------+

查询结果
+--------------------+
| id | name | sum |
+--------------------+
| 101 | aaaa | 2 |
+--------------------+
| 102 | bbbb | 1 |
+--------------------+

[feikeq]

还是一定要子查询？

[Ouyangan]

没看懂提问意思

[birdccc]

看不懂你这结果集是怎么来的啊 。

[techme]

是不是在本表中查询每个 id 与 fid 的大于零的关联数 要用 join 吗？

[delavior]

去重怎么实现？一般都是分组吧

[e1nher1ar]

select distinct(id),distinct(name),sum(*)
from table_name
--where optional
group by distinct(id),distinct(name)

大概是这样吧

[sunchen]

select id, name, count(*) as sum
from
(
select a.id, a.name
from x as a, x as b
where a.id = b.fid
)
group by 1, 2
;

[feikeq]

正确的查询结果
+----------------------+
| 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 这用户。

[feikeq]

能不用子查询做到吗？我想提高查询性能不想用子查询，数据库表设计就是这样的也不能去再修改。

[Martin9]

@feikeq 之前做过这个，是用子查询的。

[feikeq]

select id, name, count(fid) as count from table group by 'fid'


查出来虽然 count 对了，但 id 和 name 不匹配.

[fireapp]

```sql

select
id, name, (select count(*) from table t1 where t1.fid = t.id) as sum
from
table t
where
exists(select 1 from table t2 where t.id = t2.fid)
-- order by sum desc

```

[feikeq]

@Martin9 没别的办法了吗？

[Martin9]

@feikeq 额暂时不知道别的。

[lxy]

子查询统计一下 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

[lxy]

@lxy 漏了个， t2.fid_count 就是 sum 。

[txoooy]

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

[feikeq]

@txoooy 我用你这 SQL 语句查出来不是上面这表的结果呀,我查出来是：
1 aaaa 1
2 bbbb 1
3 cccc 1
4 dddd 1

[txoooy]

你的表结构, 数据 和我一样吗? 数据库是 mysql 吗?

[feikeq]

@txoooy 抱歉，是我 SQL 语句写错了你的方法是可行的，谢谢。
NNER JOIN 对性能影响大吗？

[feikeq]

@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

[txoooy]

这个要分情况, 有些情况中利用子查询构建中间表可以提高性能, 不过你现阶段并不需要考虑这些, sql 优化东西太多了, 多写多看, 自然知道什么时候用什么方法

[wavingclear]

地球思维的写法
SELECT id, name, num FROM test INNER JOIN ( SELECT fid, COUNT(fid) as num FROM `test` GROUP BY fid) AS temp ON ( test.id = temp.fid )

修改之后的写法，不用子查询
SELECT a.id, a.name, count(b.fid) as num FROM test as a INNER JOIN test as b ON (a.id = b.fid) GROUP BY b.fid

[wavingclear]

啊 17 楼已经写过了……