MySQL - HAVING 子句

要通过 Node.js 程序过滤表中基于条件的分组行，我们需要使用mysql2库的query()函数执行带有 HAVING 子句的 SELECT 语句，如下所示：

sql= " SELECT column1, column2, aggregate_function(column) 
FROM table_name GROUP BY column1, column2, ... 
HAVING condition ORDER BY column1, column2, ...";   
con.query(sql);

要通过 Java 程序过滤表中基于条件的分组行，我们需要使用JDBC函数executeUpdate()执行带有 HAVING 子句的 SELECT 语句，如下所示：

String sql = "SELECT column1, column2, aggregate_function(column) 
FROM table_name GROUP BY column1, column2, ... 
HAVING condition ORDER BY column1, column2, ...";  
statement.executeQuery(sql);

要通过 Python 程序过滤表中基于条件的分组行，我们需要使用 MySQLConnector/Python的execute()函数执行带有 HAVING 子句的 SELECT 语句，如下所示：

having_clause_query = "SELECT column1, column2, aggregate_function(column) 
FROM table_name GROUP BY column1, column2 HAVING condition"
cursorObj.execute(having_clause_query);

var mysql = require('mysql2');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE CUSTOMERS(ID INT NOT NULL,NAME VARCHAR (20) NOT NULL,AGE INT NOT NULL,GENDER VARCHAR (25),SALARY DECIMAL (18, 2),PRIMARY KEY (ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO CUSTOMERS VALUES(1, 'Ramesh', 25, 'Male', 2000.00),(2, 'Ramesh', 25, 'Male', 1500.00),(3, 'kaushik', 25, 'Female', 2000.00),(4, 'kaushik', 20, 'Male', 6500.00),(5, 'Hardik', 25, 'Male', 8500.00),(6, 'Komal', 20, 'Female', 4500.00),(7, 'Muffy', 25, 'Male', 10000.00);"
  con.query(sql);

  //Using HAVING Clause
  sql = "SELECT NAME, SUM(SALARY) as total_salary FROM CUSTOMERS GROUP BY NAME HAVING SUM(SALARY) < 4540 ORDER BY NAME;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});             

输出

生成的输出如下：

Connected!
--------------------------
[
  { NAME: 'Komal', total_salary: '4500.00' },
  { NAME: 'Ramesh', total_salary: '3500.00' }
]        

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class HavingClause {
  public static void main(String[] args) {
    String url = "jdbc:mysql://127.0.0.1:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT NAME, SUM(SALARY) as total_salary FROM CUSTOMERS GROUP BY NAME HAVING SUM(SALARY) < 4540 ORDER BY NAME;";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()){
              String name = rs.getString("Name");
              String total_salary = rs.getString("total_salary");
              System.out.println("Name: " + name + ", Total_Salary: " + total_salary);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}                          

输出

获得的输出如下所示：

Table records: 
Name: Komal, Total_Salary: 4500.00
Name: Ramesh, Total_Salary: 3500.00        

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
# Creating a cursor object 
cursorObj = connection.cursor()
having_clause_query = """SELECT ADDRESS, SUM(SALARY) as total_salary FROM CUSTOMERS
GROUP BY ADDRESS HAVING SUM(SALARY) < 4540 ORDER BY ADDRESS"""
cursorObj.execute(having_clause_query)
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()                                

输出

以下是上述代码的输出：

('Mumbai', Decimal('1200.00'))