具有附加列的同一个表上的多对多

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  content TEXT NOT NULL,
  user_id INTEGER NOT NULL REFERENCES users(id)
);

CREATE TABLE post_views (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id),
  user_id INTEGER NOT NULL REFERENCES users(id),
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

INSERT INTO users (name, email) VALUES ('John Doe', 'john.doe@example.com');
INSERT INTO posts (title, content, user_id) VALUES ('Hello World', 'This is a test post.', 1);
INSERT INTO post_views (post_id, user_id, created_at, updated_at) VALUES (1, 1, NOW(), NOW());

SELECT users.id AS user_id, users.name AS user_name, users.email AS user_email, 
       posts.id AS post_id, posts.title AS post_title, posts.content AS post_content, 
       post_views.created_at AS created_at, post_views.updated_at AS updated_at
FROM users
JOIN (
  SELECT post_id, user_id, created_at, updated_at
  FROM post_views
  WHERE post_id = 1 AND user_id = 1
) post_views ON users.id = post_views.user_id
JOIN posts ON posts.id = post_views.post_id;