MySQL - 自連線

要透過 JavaScript 程式執行自連線，我們需要使用mysql2庫的query()函式執行 SQL 查詢，如下所示：

sql = "SELECT a.tutorial_id, a.tutorial_title, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b 
WHERE a.tutorial_author = b.tutorial_author";
con.query(sql);  

要透過 Java 程式執行自連線，我們需要使用JDBC函式executeQuery()執行 SQL 查詢，如下所示：

String sql = "SELECT a.tutorial_id, a.tutorial_title, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b 
WHERE a.tutorial_author = b.tutorial_author";
statement.executeQuery(sql);

要透過 python 程式執行自連線，我們需要使用MySQL Connector/Python的execute()函式執行 SQL 查詢，如下所示：

self_join_query = "SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY < b.SALARY"
cursorObj.execute(self_join_query)

var mysql = require("mysql2");
var con = mysql.createConnection({
  host: "localhost",
  user: "root",
  password: "password",
}); //Connecting to MySQL

con.connect(function (err) {
  if (err) throw err;
  //   console.log("Connected successfully...!");
  //   console.log("--------------------------");
  sql = "USE TUTORIALS";
  con.query(sql);

  //Self Join
  sql = "SELECT a.tutorial_id, a.tutorial_title, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b 
  WHERE a.tutorial_author = b.tutorial_author";
  con.query(sql, function (err, result) {
    if (err) throw err;
    console.log(result);
  });
});      

輸出

產生的輸出如下所示：

[
  {
    tutorial_id: 1,
    tutorial_title: 'Learn PHP',
    tutorial_author: 'John Poul',
    tutorial_count: 2
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class SelfJoin {
   public static void main(String[] args) {
      String url = "jdbc:mysql://:3306/TUTORIALS";
      String username = "root";
      String password = "password";
      try {
         Class.forName("com.mysql.cj.jdbc.Driver");
         Connection connection = DriverManager.getConnection(url, username, password);
         Statement statement = connection.createStatement();
         System.out.println("Connected successfully...!");

         //MySQL Self JOIN...!;
         String sql = "SELECT a.tutorial_id, a.tutorial_title, a.tutorial_author, b.tutorial_count FROM tutorials_tbl a, tcount_tbl b 
         WHERE a.tutorial_author = b.tutorial_author";
         ResultSet resultSet = statement.executeQuery(sql);
         System.out.println("Table records after Self Join...!");
         while (resultSet.next()){
            System.out.println(resultSet.getString(1)+ " "+ resultSet.getString(2)+" "+resultSet.getString(3));
         }
         connection.close();
      } catch (Exception e) {
         System.out.println(e);
      }
   }
} 

輸出

獲得的輸出如下所示：

Connected successfully...!
Table records after Self Join...!
1 Learn PHP John Paul
3 JAVA Tutorial Sanjay

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host='localhost',
    user='root',
    password='password',
    database='tut'
)
cursorObj = connection.cursor()
self_join_query = f"""SELECT a.ID, b.NAME as EARNS_HIGHER, a.NAME as EARNS_LESS, a.SALARY as LOWER_SALARY
FROM CUSTOMERS a, CUSTOMERS b WHERE a.SALARY < b.SALARY"""
cursorObj.execute(self_join_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()  

輸出

以下是上述程式碼的輸出：

(4, 'Ramesh', 'Chaital', Decimal('1200.00'))
(6, 'Khilan', 'Komal', Decimal('7000.00'))
(4, 'Khilan', 'Chaital', Decimal('1200.00'))
(1, 'Khilan', 'Ramesh', Decimal('4000.00'))
(7, 'kaushik', 'Muffy', Decimal('10000.00'))
(6, 'kaushik', 'Komal', Decimal('7000.00'))
(5, 'kaushik', 'Hardik', Decimal('10000.00'))
(4, 'kaushik', 'Chaital', Decimal('1200.00'))
(2, 'kaushik', 'Khilan', Decimal('8000.00'))
(1, 'kaushik', 'Ramesh', Decimal('4000.00'))
(6, 'Hardik', 'Komal', Decimal('7000.00'))
(4, 'Hardik', 'Chaital', Decimal('1200.00'))
(2, 'Hardik', 'Khilan', Decimal('8000.00'))
(1, 'Hardik', 'Ramesh', Decimal('4000.00'))
(4, 'Komal', 'Chaital', Decimal('1200.00'))
(1, 'Komal', 'Ramesh', Decimal('4000.00'))
(6, 'Muffy', 'Komal', Decimal('7000.00'))
(4, 'Muffy', 'Chaital', Decimal('1200.00'))
(2, 'Muffy', 'Khilan', Decimal('8000.00'))
(1, 'Muffy', 'Ramesh', Decimal('4000.00'))