（mySQL）无法正确查询2个表的数据

问题描述：

我有2个表。一个是'page_links'，另一个是'rpp'。表page_links是表rpp的超集。（mySQL）无法正确查询2个表的数据

以下是我的表的架构：

-- Table structure for table `page_links` 
-- 

CREATE TABLE IF NOT EXISTS `page_links` (
    `page` varchar(255) NOT NULL, 
    `page_link` varchar(100) NOT NULL, 
    `heading_id` tinyint(3) unsigned NOT NULL, 
    PRIMARY KEY (`page`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

-- 
-- Dumping data for table `page_links` 
-- 

INSERT INTO `page_links` (`page`, `page_link`, `heading_id`) VALUES 
('a1.php', 'A1', 8), 
('b1.php', 'B1', 8), 
('c1.php', 'C1', 5), 
('d1.php', 'D1', 5), 
('e1.php', 'E1', 8), 
('f1.php', 'F1', 8), 
('g1.php', 'G1', 8), 
('h1.php', 'H1', 1), 
('i1.php', 'I1', 1), 
('j1.php', 'J1', 8), 
('k1.php', 'K1', 8), 
('l1.php', 'L1', 8), 
('m1.php', 'M1', 8), 
('n1.php', 'N1', 8), 
('o1.php', 'O1', 8), 
('p1.php', 'P1', 4), 
('q1.php', 'Q1', 5), 
('r1.php', 'R1', 4); 


-- Table structure for table `rpp` 
-- 

CREATE TABLE IF NOT EXISTS `rpp` (
    `role_id` tinyint(3) unsigned NOT NULL, 
    `page` varchar(255) NOT NULL, 
    `is_allowed` tinyint(1) NOT NULL, 
    PRIMARY KEY (`role_id`,`page`) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1; 

-- 
-- Dumping data for table `rpp` 
-- 

INSERT INTO `rpp` (`role_id`, `page`, `is_allowed`) VALUES 
(3, 'a1.php', 1), 
(3, 'b1.php', 1), 
(3, 'c1.php', 1), 
(3, 'd1.php', 1), 
(3, 'e1.php', 1), 
(3, 'f1.php', 1), 
(3, 'h1.php', 1), 
(3, 'i1.php', 1), 
(3, 'l1.php', 1), 
(3, 'm1.php', 1), 
(3, 'n1.php', 1), 
(4, 'a1.php', 1), 
(4, 'b1.php', 1), 
(4, 'q1.php', 1), 
(5, 'r1.php', 1);

我所试图做的事：

我想查询上面两个表（在单个查询）以这种方式，来自page_links的所有页面都将与来自rpp的is_allowed值一起显示给特定角色。例如，我想从rpp中为role_id = 3获取所有页面的is_allowed值，同时列出page_links中的所有可用页面。我期望的结果的一个明显的例子是：

page  is_allowed role_id 
---------------------------------------- 
a1.php 1    3 
b1.php 1    3 
c1.php 1    3 
d1.php 1    3 
e1.php 1    3 
f1.php 1    3 
g1.php NULL   NULL 
h1.php 1    3 
i1.php 1    3 
j1.php NULL   NULL 
k1.php NULL   NULL 
l1.php 1    3 
m1.php 1    3 
n1.php 1    3 
o1.php NULL   NULL 
p1.php NULL   NULL 
q1.php NULL   NULL 
r1.php NULL   NULL

我想要的结果的又一个例子可以通过做一个LEFT JOIN RPP ON page_links.page = rpp.page来实现，但是我们需要用省略ROLE_ID = 3 （或任何值）能够得到。但我也想指定role_id并获得结果。 我需要查询才能够得到这个结果。我将不胜感激任何回复，可以帮助我。如果您可以建议我对桌子设计进行任何更改以达到预期效果，那也是很好的做法。提前致谢。

+1用于提供创建脚本，测试数据和预期输出。我希望我能给更多。 – 2010-01-02 05:00:10

@马克，非常感谢。我希望我也能接受你的回答，但它不会让我。也许下次.. :) – Devner 2010-01-02 05:16:46

答

用途：

SELECT pl.page, 
      r.is_allowed, 
      r.role_id 
    FROM PAGE_LINKS pl 
LEFT JOIN RPP r ON r.page = pl.page 
       AND OR r.role_id = 3

以前：

SELECT pl.page, 
      r.is_allowed, 
      r.role_id 
    FROM PAGE_LINKS pl 
LEFT JOIN RPP r ON r.page = pl.page 
    WHERE r.role_id IS NULL OR r.role_id = 3

这个查询不会返回比3以外的ROLE_ID并且不为空页。

谢谢你的解决方案。这工作！ – Devner 2010-01-02 05:12:57

@OMG：我们的解决方案给出了不同的结果。我认为你错过了q1和r1，因为WHERE子句不匹配。 – 2010-01-02 05:21:12

@OMG：感谢您的更正。当我检查查询时，它运行良好。也许我仍然需要彻底测试它。我仍在使用非常有限的测试数据。但就马克的解决方案而言，我感觉好像是答案为这个问题所做的那样。感谢OMG，Mark。 – Devner 2010-01-02 06:57:25

答

试试这个：

SELECT page_links.page, is_allowed, role_id 
FROM page_links 
LEFT JOIN rpp 
ON rpp.page = page_links.page AND rpp.role_id = 3

嗨，谢谢你的解决方案。你的解决方案也可以。我也试着接受你的回答，但是它取消了我已经选择的以前的答案，并且不会让我选择你的答案。我认为该系统不允许我同时接受2个答案。但我确实得到了你的解决方案，并感谢你的努力，因此，我投了你的答复。非常感谢。 – Devner 2010-01-02 05:15:00

答

SELECT page_links.page, new_rpp.is_allowed, new_rpp.role_id 
FROM page_links LEFT JOIN (SELECT * FROM rpp where role_id = 3) as new_rpp USING(page)

这会给你你想要的结果，它不仅会显示对应ROLE_ID = 3的页面也将显示所有不具有该页面在rpp表中为role_id = 3的对应项目所以这应该会产生你所期望的结果集。

（mySQL）无法正确查询2个表的数据

相关推荐