V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MySQL 5.5 Community Server
MySQL 5.6 Community Server
Percona Configuration Wizard
XtraBackup 搭建主从复制
Great Sites on MySQL
Percona
MySQL Performance Blog
Severalnines
推荐管理工具
Sequel Pro
phpMyAdmin
推荐书目
MySQL Cookbook
MySQL 相关项目
MariaDB
Drizzle
参考文档
http://mysql-python.sourceforge.net/MySQLdb.html
feikeq
V2EX  ›  MySQL

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

  •  
  •   feikeq ·
    feikeq · 2016-06-27 11:13:51 +08:00 · 5291 次点击
    这是一个创建于 3073 天前的主题,其中的信息可能已经有所发展或是发生改变。
    现在学过的东西想再用时却忘干净了,就像解一元二次方程,小时候很会解,现在怎么不知怎么开始解。。。。


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

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

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


    查出来虽然 count 对了,但 id 和 name 不匹配.
    fireapp
        12
    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
        13
    feikeq  
    OP
       2016-06-27 11:44:23 +08:00
    @Martin9 没别的办法了吗?
    Martin9
        14
    Martin9  
       2016-06-27 11:46:06 +08:00
    @feikeq 额暂时不知道别的。
    lxy
        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
    lxy
        16
    lxy  
       2016-06-27 12:02:54 +08:00
    @lxy 漏了个, t2.fid_count 就是 sum 。
    txoooy
        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
    feikeq
        18
    feikeq  
    OP
       2016-06-27 15:02:02 +08:00
    @txoooy 我用你这 SQL 语句查出来不是上面这表的结果呀,我查出来是:
    1 aaaa 1
    2 bbbb 1
    3 cccc 1
    4 dddd 1
    txoooy
        19
    txoooy  
       2016-06-27 15:07:23 +08:00
    你的表结构, 数据 和我一样吗? 数据库是 mysql 吗?
    feikeq
        20
    feikeq  
    OP
       2016-06-27 15:12:13 +08:00
    @txoooy 抱歉,是我 SQL 语句写错了你的方法是可行的,谢谢。
    NNER JOIN 对性能影响大吗?
    feikeq
        21
    feikeq  
    OP
       2016-06-27 15:20:03 +08:00
    @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
        22
    txoooy  
       2016-06-27 15:22:20 +08:00
    这个要分情况, 有些情况中利用子查询构建中间表可以提高性能, 不过你现阶段并不需要考虑这些, sql 优化东西太多了, 多写多看, 自然知道什么时候用什么方法
    wavingclear
        23
    wavingclear  
       2016-06-27 16:41:37 +08:00
    地球思维的写法
    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
        24
    wavingclear  
       2016-06-27 16:44:25 +08:00
    啊 17 楼已经写过了……
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   实用小工具   ·   3596 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 04:55 · PVG 12:55 · LAX 20:55 · JFK 23:55
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.