阿西河

所有教程

公众号
🌙
阿西河前端的公众号

我的收藏

    最近访问  (文章)

      教程列表

      抓包专区
      测试专区

      PostgreSQL having

      PostgreSQL HAVING 子句

      HAVING 子句可以让我们筛选分组后的各组数据。

      WHERE 子句在所选列上设置条件,而 HAVING 子句则在由 GROUP BY 子句创建的分组上设置条件。

      语法

      下面是 HAVING 子句在 SELECT 查询中的位置:

      SELECT
      FROM
      WHERE
      GROUP BY
      HAVING
      ORDER BY
      

      HAVING 子句必须放置于 GROUP BY 子句后面,ORDER BY 子句前面,下面是 HAVING 子句在 SELECT 语句中基础语法:

      SELECT column1, column2
      FROM table1, table2
      WHERE [ conditions ]
      GROUP BY column1, column2
      HAVING [ conditions ]
      ORDER BY column1, column2
      

      实例

      创建 COMPANY 表,数据内容如下:

      axihedb# select * from COMPANY;
       id | name  | age | address   | salary
      ----+-------+-----+-----------+--------
        1 | Paul  |  32 | California|  20000
        2 | Allen |  25 | Texas     |  15000
        3 | Teddy |  23 | Norway    |  20000
        4 | Mark  |  25 | Rich-Mond |  65000
        5 | David |  27 | Texas     |  85000
        6 | Kim   |  22 | South-Hall|  45000
        7 | James |  24 | Houston   |  10000
      (7 rows)
      

      下面实例将找出根据 NAME 字段值进行分组,并且 name(名称) 字段的计数少于 2 数据:

      SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) < 2;
      

      得到以下结果:

        name
       -------
        Teddy
        Paul
        Mark
        David
        Allen
        Kim
        James
      (7 rows)
      

      我们往表里添加几条数据:

      INSERT INTO COMPANY VALUES (8, 'Paul', 24, 'Houston', 20000.00);
      INSERT INTO COMPANY VALUES (9, 'James', 44, 'Norway', 5000.00);
      INSERT INTO COMPANY VALUES (10, 'James', 45, 'Texas', 5000.00);
      

      此时,COMPANY 表的记录如下:

       id | name  | age | address      | salary
       ----+-------+-----+--------------+--------
         1 | Paul  |  32 | California   |  20000
         2 | Allen |  25 | Texas        |  15000
         3 | Teddy |  23 | Norway       |  20000
         4 | Mark  |  25 | Rich-Mond    |  65000
         5 | David |  27 | Texas        |  85000
         6 | Kim   |  22 | South-Hall   |  45000
         7 | James |  24 | Houston      |  10000
         8 | Paul  |  24 | Houston      |  20000
         9 | James |  44 | Norway       |   5000
        10 | James |  45 | Texas        |   5000
      (10 rows)
      

      下面实例将找出根据 name 字段值进行分组,并且名称的计数大于 1 数据:

      axihedb-# SELECT NAME FROM COMPANY GROUP BY name HAVING count(name) > 1;
      

      得到结果如下:

       name
      -------
       Paul
       James
      (2 rows)
      
      目录
      目录