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

2016-06-27 11:13:51 +08:00
 feikeq
现在学过的东西想再用时却忘干净了,就像解一元二次方程,小时候很会解,现在怎么不知怎么开始解。。。。


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

查询结果
+--------------------+
| id | name | sum |
+--------------------+
| 101 | aaaa | 2 |
+--------------------+
| 102 | bbbb | 1 |
+--------------------+
5290 次点击
所在节点    MySQL
24 条回复
feikeq
2016-06-27 11:14:51 +08:00
还是一定要子查询?
Ouyangan
2016-06-27 11:18:50 +08:00
没看懂提问意思
birdccc
2016-06-27 11:19:21 +08:00
看不懂你这结果集是怎么来的啊 。
techme
2016-06-27 11:24:32 +08:00
是不是在本表中查询每个 id 与 fid 的大于零的关联数 要用 join 吗?
delavior
2016-06-27 11:24:47 +08:00
去重怎么实现?一般都是分组吧
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)

大概是这样吧
sunchen
2016-06-27 11:36:36 +08:00
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
2016-06-27 11:38:02 +08:00
正确的查询结果
+----------------------+
| 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
2016-06-27 11:40:27 +08:00
能不用子查询做到吗?我想提高查询性能不想用子查询,数据库表设计就是这样的也不能去再修改。
Martin9
2016-06-27 11:41:05 +08:00
@feikeq 之前做过这个,是用子查询的。
feikeq
2016-06-27 11:43:50 +08:00
select id, name, count(fid) as count from table group by 'fid'


查出来虽然 count 对了,但 id 和 name 不匹配.
fireapp
2016-06-27 11:44:13 +08:00
```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
2016-06-27 11:44:23 +08:00
@Martin9 没别的办法了吗?
Martin9
2016-06-27 11:46:06 +08:00
@feikeq 额暂时不知道别的。
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
lxy
2016-06-27 12:02:54 +08:00
@lxy 漏了个, t2.fid_count 就是 sum 。
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
feikeq
2016-06-27 15:02:02 +08:00
@txoooy 我用你这 SQL 语句查出来不是上面这表的结果呀,我查出来是:
1 aaaa 1
2 bbbb 1
3 cccc 1
4 dddd 1
txoooy
2016-06-27 15:07:23 +08:00
你的表结构, 数据 和我一样吗? 数据库是 mysql 吗?
feikeq
2016-06-27 15:12:13 +08:00
@txoooy 抱歉,是我 SQL 语句写错了你的方法是可行的,谢谢。
NNER JOIN 对性能影响大吗?

这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。

https://www.v2ex.com/t/288542

V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。

V2EX is a community of developers, designers and creative people.

© 2021 V2EX