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

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

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

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

AAAA

bbbb

name

原表

24 replies • 2016-06-27 16:44:25 +08:00

feikeq

Jun 27, 2016

还是一定要子查询？

Ouyangan

Jun 27, 2016

没看懂提问意思

birdccc

Jun 27, 2016

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

techme

Jun 27, 2016

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

delavior

Jun 27, 2016

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

e1nher1ar

Jun 27, 2016

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

大概是这样吧

sunchen

Jun 27, 2016

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

Jun 27, 2016

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

Jun 27, 2016

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

Martin9

Jun 27, 2016

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

feikeq

Jun 27, 2016

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

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

fireapp

Jun 27, 2016

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

Jun 27, 2016

@Martin9 没别的办法了吗？

Martin9

Jun 27, 2016

@feikeq 额暂时不知道别的。

lxy

Jun 27, 2016

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

Jun 27, 2016

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

txoooy

Jun 27, 2016

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

Jun 27, 2016

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

txoooy

Jun 27, 2016

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

feikeq

Jun 27, 2016

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

feikeq

Jun 27, 2016

@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

Jun 27, 2016

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

wavingclear

Jun 27, 2016

地球思维的写法
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

Jun 27, 2016

啊 17 楼已经写过了……