表用户
id, name
1, Jay Bob
2, An Other
表页id, name, html
1, 'Welcome', 'Welcome to this page'
2, 'Goodbye', 'Thanks for visiting'
表user_pages **存储用户特定版本的页面**
user_id, page_id, html
1, 1, 'User id 1 Welcome page'
我基本上需要一个查询,将返回我下面的数据集-一行的每一种可能性,即使没有数据存在。
数据集
user_id, page_id, html
1, 1, 'User is 1 Welcome page'
1, 2, ''
2, 1, ''
2, 2, ''
我认为最清晰的方法是使用子查询和cross join
来创建一个驱动表:
select driver.user_id, driver.page_id, up.html
from (select u.id as user_id, p.id as page_id
from users u cross join
pages p
) driver left outer join
user_pages up
on up.user_id = driver.user_id and up.page_id = driver.page_id;
select
子句中的user_id
和page_id
来自驱动表,而不是来自user_pages
,因为后者可能是NULL
。
下面的代码是在MySQL Server 5.6上测试的
我希望/推测你的意图接近如下:
USE test;
CREATE TABLE users (
id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(80) NOT NULL);
CREATE TABLE pages (
id INT UNSIGNED PRIMARY KEY NOT NULL AUTO_INCREMENT,
name VARCHAR(40) NOT NULL,
html VARCHAR(100) NOT NULL);
CREATE TABLE user_pages (
user_id INT UNSIGNED NOT NULL,
page_id INT UNSIGNED NOT NULL,
html VARCHAR(50) DEFAULT '',
PRIMARY KEY (user_id, page_id));
INSERT users (`name`) VALUES
('User 1'), ('User 2'), ('User 3');
INSERT pages (`name`, `html`) VALUES
('Welcome','Welcome to this page'),
('Goodbye','Thanks for visiting');
INSERT user_pages (user_id, page_id, html) VALUES
(1,1,"First user's welcome page"),
(1,2,"First user's goodbye page"),
(2,1,"Second user's welcome page");
SELECT DISTINCT
u.id AS `u_id`,
u.`name` AS `u_name`,
IFNULL(x.page_id, p.id) AS `p_id`,
IFNULL(p.`name`,'') AS `p_name`,
IFNULL(x.html,p.html) AS `p_html`
FROM users AS u
CROSS JOIN pages AS p
LEFT OUTER JOIN user_pages AS x
ON (x.user_id = u.id AND x.page_id = p.id);
上面的代码将为每个用户返回特定(如果存在)或一般页面。您还可以创建一个视图,并从中进行查询,就像它是一个普通的表一样:
CREATE OR REPLACE VIEW `user_page_details` AS
SELECT DISTINCT
u.id AS `u_id`,
u.`name` AS `u_name`,
IFNULL(x.page_id, p.id) AS `p_id`,
IFNULL(p.`name`,'') AS `p_name`,
IFNULL(x.html,p.html) AS `p_html`
FROM users AS u
CROSS JOIN pages AS p
LEFT OUTER JOIN user_pages AS x
ON (x.user_id = u.id AND x.page_id = p.id);
一旦你做了,你的查询将非常简单:
SELECT * FROM user_page_details;
或者
SELECT * FROM user_page_details WHERE u_id = 2;
试试这个:
SELECT up.user_id, up.page_id, up.html
FROM users u,pages p
LEFT OUTER JOIN user_pages up ON u.id=up.user_id AND p.id=up.user_id