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://: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'))