sql example 9 -- group, having

user1; insert into user1 (id, username) values (1, ‘test1‘); insert into user1 (id, username) values (2, ‘test1‘); insert into user1 (id, username) values (3, ‘test1‘); insert into user1 (id, username) values (4, ‘test1‘); insert into user1 (id, username) values (5, ‘test1‘); insert into user1 (id, username) values (1, ‘test2‘); insert into user1 (id, username) values (2, ‘test2‘); insert into user1 (id, username) values (3, ‘test2‘); insert into user1 (id, username) values (4, ‘test2‘); insert into user1 (id, username) values (5, ‘test2‘); insert into user1 (id, username) values (1, ‘test3‘); insert into user1 (id, username) values (2, ‘test3‘); insert into user1 (id, username) values (3, ‘test3‘); insert into user1 (id, username) values (4, ‘test3‘); insert into user1 (id, username) values (5, ‘test3‘);

select * from user1 group by username;

| id | username |
+----+----------+
|  1 | test1    |
|  1 | test2    |
|  1 | test3    |

select * from user1 group by id;

| id | username |
+----+----------+
|  1 | test1    |
|  2 | test1    |
|  3 | test1    |
|  4 | test1    |
|  5 | test1    |

分组实际上就是求集合?

select username, id from user1 group by 1;

1 指的是 username, group by 2 指的是 id (一般不这样用, 好丑陋的用法)

• having
  相当于 where
  

  select username, id from user1 group by id;
  

  | username | id |
  +----------+----+
  | test1    |  1 |
  | test1    |  2 |
  | test1    |  3 |
  | test1    |  4 |
  | test1    |  5 |
  

  select username, id from user1 group by id having id in (1, 2, 3);
  

  | username | id |
  +----------+----+
  | test1    |  1 |
  | test1    |  2 |
  | test1    |  3 |
  

  select username from user1 group by id having id in (1, 2, 3);
  

  select username from user1 group by username having id in (1, 2, 3);   # 报错, 没有 id 这个字段
  

sql example 9 -- group, having

标签：