How to merge about one SQL element?

Question

I have some data in the database:

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

I want to keep order and combine around the same user elements, how to do this? When I use a shell script, I will (data in the test. File):

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

this will produce a result like:

   3 zhangsan 
   3 lisi 
   2 zhangsan 

But how to do it, use sql?

Answer 1

If you try:

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;

This gives:

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

So you can try:

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;

What gives

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

(I called my table foo, and just used the names aand bbecause I was too lazy to write zhangsan, and lisiagain and again).

Answer 2

if in oracle you can do as below.

SELECT NAME,
       num - lagnum
  FROM (SELECT lagname,
               NAME,
               num,
               nvl(lag(num) over(ORDER BY num), 0) lagnum
          FROM (SELECT id,
                       lag(NAME) over(ORDER BY ID) lagname,
                       NAME,
                       lead(NAME) over(ORDER BY ID) leadname,
                       ROWNUM num
                  FROM (SELECT * FROM test ORDER BY ID))
         WHERE (lagname = NAME AND (NAME <> leadname OR leadname IS NULL))
            OR (lagname IS NULL AND NAME <> leadname)
            OR (lagname <> NAME AND leadname IS NULL)
         ORDER BY ID);

Answer 3

sql, oracle, db2...

    with x as(  
select c.*, rn = row_number() over (order by c.id)
  from test c 
  left join test n
    on c.[user] = n.[user]
   and c.[id] + 1 = n.[id]   
 where n.id is null
)
select a.[user], a.id - coalesce(b.id, 0) 
  from x a
  left join x b
    on a.rn = b.rn + 1 

Answer 4

A slight improvement for the selected answer would not necessarily define these variables. Thus, this request can be resolved in only one statement:

SELECT COUNT(*) cnt, user
FROM (
    SELECT @num := @num + (@name != user) as number,
           @name := user as user
    FROM t, (select @num := 0, @name := '') as s
    ORDER BY id
) x
GROUP BY number

Conclusion:

| CNT |     USER |
|-----|----------|
|   3 | zhangsan |
|   3 |     lisi |
|   2 | zhangsan |

Feed here

Answer 5

I think you are looking for COUNT (ID):

SELECT COUNT(ID) FROM table GROUP BY user

Answer 6

You cannot do this in sql without doing some sort of sequential (iterative) analysis. Remember that sql sets the language of operations.