如何通过SQL合并同一项目附近? [英] How to combine near same item by SQL?

问题描述

我的数据库中有一些数据:

I have some data in database:

id user 
1 zhangsan 
2 zhangsan 
3 zhangsan 
4 lisi 
5 lisi 
6 lisi 
7 zhangsan 
8 zhangsan 

我要保持秩序，并合并几乎相同的用户物品，该怎么办? 当我使用Shell脚本时，我将(文件测试中的数据.):

I want keep order, and combine near same user items, how to do it? When I use shell script, I will(data in file test.):

cat test|cut -d " " -f2|uniq -c 

这将得到如下结果:

   3 zhangsan 
   3 lisi 
   2 zhangsan 

但是如何使用sql?

推荐答案

如果您尝试:

SET @name:='',@num:=0;

SELECT id,
       @num:= if(@name = user, @num, @num + 1) as number,
       @name := user as user
FROM foo
ORDER BY id ASC;

这给出了:

+------+--------+------+
| id   | number | user |
+------+--------+------+
|    1 |      1 | a    |
|    2 |      1 | a    |
|    3 |      1 | a    |
|    4 |      2 | b    |
|    5 |      2 | b    |
|    6 |      2 | b    |
|    7 |      3 | a    |
|    8 |      3 | a    |
+------+--------+------+

所以您可以尝试:

SET @name:='',@num:=0;

SELECT COUNT(*) as count, user
FROM (
SELECT @num:= if(@name = user, @num, @num + 1) as number,
       @name := user as user
FROM foo
ORDER BY id ASC
) x
GROUP BY number;

哪个给

+-------+------+
| count | user |
+-------+------+
|     3 | a    |
|     3 | b    |
|     2 | a    |
+-------+------+

(我叫我的表foo，也只是使用了名称a和b，因为我懒得一遍又一遍地写zhangsan和lisi.)

(I called my table foo and also just used names a and b because I was too lazy to write zhangsan and lisi over and over).

这篇关于如何通过SQL合并同一项目附近?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！