mysql case quchong_处理mysql的查询语句去重案例一则

有一个网友在QQ群中问到：

各位，这条语句还能加个去重不?

SELECT cc.customer_id   as userId,cc.content as content,        cc.logs_rank  as rank, cc.add_time as time,g.goods_thumb_min as url,cc.goods_label ASgoodsLabel,g.cat_id

AS catId, g.goods_id AS goodsId        FROM cc  STRAIGHT_JOIN g ON cc.barcode = g.goods_sn  WHERE cc.status =

1    and g.is_on_sale = 1  ORDER BY cc.add_time DESC LIMIT 0, 10;

order by已经用在add_time了现在要去重userId这个字段。

我模拟测试了一把:

(product)root@localhost [lots]> create table quchong

-> (USER_ID int(4),

-> USER_NAME varchar(200) DEFAULT '')

-> ENGINE=InnoDB DEFAULT CHARSET=gbk;

Query OK, 0 rows affected (0.23 sec)

(product)root@localhost [lots]> insert into quchong values(2045,'zxw1'),(2045,'zxw2'),(2046,'zxw3');

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

现在需要对user_id字段去重：

(product)root@localhost [lots]>  select distinct user_id,user_name from quchong order by user_id limit 0,3;

+---------+-----------+

| user_id | user_name |

+---------+-----------+

|    2045 | zxw1      |

|    2045 | zxw2      |

|    2046 | zxw3      |

+---------+-----------+

3 rows in set (0.00 sec)

----上面返回记录是对user_id和user_name一起去重，就是说两个字段的一条记录要与其它记录的两个字段不同，不是单个字段的去重。

Mysql中引入了group_concat函数，可以与group by一起使用实现单个字段去重。修改操作如下：

(product)root@localhost [lots]> select group_concat(distinct user_id),user_name from quchong

-> group by user_id

-> order by

-> user_id limit 0,3;

+--------------------------------+-----------+

| group_concat(distinct user_id) | user_name |

+--------------------------------+-----------+

| 2045                           | zxw1      |

| 2046                           | zxw3      |

+--------------------------------+-----------+

2 rows in set (0.00 sec)

这样功能就实现了。

于是建议他改成如下：

SELECT group_concat(distinct cc.customer_id)   as userId,cc.content as content,        cc.logs_rank  as rank,  cc.add_time as time,g.goods_thumb_min as

url,cc.goods_label AS goodsLabel,g.cat_id  AS catId,  g.goods_id AS goodsId        FROM cc  STRAIGHT_JOIN  g ON  cc.barcode =

g.goods_sn  WHERE cc.status = 1    and g.is_on_sale = 1

group by cc.customer_id

ORDER BY cc.add_time DESC  LIMIT 0, 20;

代言人14:01:54

刚才那个去重的，行了吗

xxx_深圳14:09:41

可以去重

xxx_深圳14:09:54

查询速度有点慢

代言人14:10:05

再看下执行计划

xxxx_深圳14:11:10

代言人14:20:52

又全表了

xxxx_深圳14:20:59

是啊

代言人14:21:25

还是刚才那个SQL吗

xxxx_深圳14:21:45

是啊

代言人14:25:25

原来是走哪个索引

代言人14:25:44

你用force index强制加索引试下

其实到这里功能已经实现了。