PostgreSQL JOIN 使用数组类型和数组元素

使用 PostgreSQL 的 JOIN 操作结合数组类型和数组元素可以实现更灵活和高效的数据查询和处理。在本篇文章中，我们将介绍如何使用这些功能，并提供一些案例代码来帮助读者更好地理解和应用。

sql
CREATE TABLE students (
   id SERIAL PRIMARY KEY,
   name VARCHAR(100),
   subjects INT[]
);
CREATE TABLE subjects (
   id SERIAL PRIMARY KEY,
   subject_name VARCHAR(100)
);
INSERT INTO students (name, subjects) VALUES
   ('John Doe', ARRAY[1, 2, 3]),
   ('Jane Smith', ARRAY[2, 3, 4]),
   ('Mike Johnson', ARRAY[1, 3, 4]);
INSERT INTO subjects (subject_name) VALUES
   ('Mathematics'),
   ('Physics'),
   ('Chemistry'),
   ('Biology');
SELECT students.name, subjects.subject_name
FROM students
JOIN subjects ON subjects.id = ANY(students.subjects);

sql
SELECT students.name, subjects.subject_name
FROM students
JOIN subjects ON subjects.id = ANY(students.subjects)
WHERE 1 = ANY(students.subjects);

sql
-- 创建表格
CREATE TABLE orders (
   id SERIAL PRIMARY KEY,
   customer_name VARCHAR(100),
   products INT[]
);
CREATE TABLE products (
   id SERIAL PRIMARY KEY,
   product_name VARCHAR(100),
   price DECIMAL(10,2)
);
-- 插入数据
INSERT INTO orders (customer_name, products) VALUES
   ('John Doe', ARRAY[1, 2, 3]),
   ('Jane Smith', ARRAY[2, 3, 4]),
   ('Mike Johnson', ARRAY[1, 3, 4]);
INSERT INTO products (product_name, price) VALUES
   ('Apple', 1.99),
   ('Orange', 0.99),
   ('Banana', 0.49),
   ('Grapes', 2.99);
-- 查询订单详情
SELECT orders.customer_name, products.product_name, products.price
FROM orders
JOIN products ON products.id = ANY(orders.products)
WHERE 1 = ANY(orders.products);