MYSQL WITH 用法 临时表

WITH customer_orders AS(
	SELECT customer_id,COUNT(*) AS order_count
    FROM orders
    GROUP BY customer_id
)
SELECT customers.*,COALESCE(customer_orders.order_count,0) AS order_count
FROM customers
LEFT JOIN customer_orders ON customers.id = customer_orders.customer_id;

mysql with是指在MySQL中使用WITH语句来创建临时表格的功能，通常也被称为“公共表达式”（common table expressions）。

临时表格是一种可以在查询中使用的虚拟表格，它们在查询结束后会自动删除。WITH语句可以让用户定义这些临时表格，然后在查询中引用它们。

举个例子，假设我们有一个orders表格和一个customers表格，我们想要查询每个客户的订单数量。我们可以使用以下查询：

这个查询中的WITH语句创建了一个名为customer_orders的临时表格，它包含每个客户的订单数量。然后，我们在主查询中使用LEFT JOIN连接这个临时表格，并使用COALESCE函数来确保即使客户没有订单，我们也会显示一个零值。

注意，WITH语句只在查询中引用一次。如果我们需要在多个查询中使用相同的临时表格，我们需要为每个查询都定义一个WITH语句。

WITH语句可以大大简化复杂的查询，并使查询更易于理解和维护。

COALESCE是一个SQL函数，它用于返回其参数列表中第一个非空值。如果所有参数都为NULL，则COALESCE返回NULL。

准备数据：

create database demo1;
use demo1;

-- 创建customers表格
CREATE TABLE customers (
  id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  phone VARCHAR(20)
);

-- 向customers表格插入数据
INSERT INTO customers (id, first_name, last_name, email, phone)
VALUES 
  (1, 'John', 'Doe', 'johndoe@example.com', '555-1234'),
  (2, 'Jane', 'Doe', 'janedoe@example.com', '555-5678'),
  (3, 'Bob', 'Smith', 'bobsmith@example.com', '555-9012'),
  (4, 'Alice', 'Johnson', 'alicejohnson@example.com', '555-3456'),
  (5, 'Mike', 'Williams', 'mikewilliams@example.com', '555-7890');
  
  -- 创建orders表格
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total_amount DECIMAL(10, 2)
);

-- 向orders表格插入数据
INSERT INTO orders (order_id, customer_id, order_date, total_amount)
VALUES 
  (1001, 1, '2022-01-01', 100.50),
  (1002, 2, '2022-02-15', 75.20),
  (1003, 3, '2022-03-21', 200.00),
  (1004, 1, '2022-04-12', 50.00),
  (1005, 4, '2022-05-07', 120.80);