不可能有条件的SQL连接？

Question

我有两个表，参考资料和ChapterMap。

参考资料表包含教育资源的记录。此表有一组与主题相关的列(RES_Topic_Sports、RES_Topic_Politics、RES_Topic_CurrentEvents等)。如果其中一个字段的值为1，则资源与该主题相关。

ChapterMap表将主题链接到书中的章节。本表中的每个记录都有一个主题字段，其值包括(体育、政治、时事等)。它还有指向书籍章节的链接(FR_Ch1_Sec1、FR_Ch1_Sec2、FR_Ch2_Sec3等)。

我想加入这两张表格的主题。例如，如果Resources.RES_Topic_Sports = 1，我想加入ChapterMap，其中ChapterMap.Topic = 'Sports‘。如果Resources.RES_Topic_Sports = 1，Resources.RES_Topic_Politics =1，我想加入ChapterMap，其中ChapterMap.Topic =‘体育’，ChapterMap.Topic =‘政治’等等。

如果这是最优的话，我也愿意对表进行重组。

如有任何建议，我将不胜感激。如果重要的话，这是一个致力于全球人权问题的非营利组织。

提前谢谢！！

*样本数据*

表：资源

字段： RES_ID，RES_Name，RES_Topic_Sports，RES_Topic_Politics，RES_Topic_CurrentEvents

记录： (101，“体育课计划”，1，NULL，1)

解释：是一种名为“体育课程计划”的资源，它与体育和时事主题相关。

表： CHAPTERMAP

字段： ID、主题、Ch1_Sec1、Ch1_Sec2、Ch1_Sec3、Ch1_Sec4、Ch2_Sec1、Ch2_Sec2、Ch3_Sec1、Ch3_Sec2、Ch3_Sec3

记录： (4，‘体育’，空，1，空，空，1，1)

Explanation:：将主题“体育”与第2章第1节、第3章第2节和第3章第3节联系起来的映射。

目标：将资源“体育课计划”链接到第2章第1节、第3章第2节和第3章第3节。

Answer 1

首先需要重构表以使数据库正常化。与其为每个主题有一列，您应该有一个主题表、一个资源表和一个存储分配给哪些资源的主题的联接表。对于将主题分配给章节，您也应该这样做。

例如，请考虑以下表格：

         TOPIC:  ID     NAME
                 ----------------------------------------------------------
                 1      Sports
                 2      Politics


      RESOURCE:  ID     (any other fields you need, i.e. description, etc.)
                 ----------------------------------------------------------
                 1      blah  blah
                 2      blah  blah


RESOURCE_TOPIC:  RESOURCE_ID    TOPIC_ID
                 ----------------------------------------------------------
                 1              2
                 2              1
                 2              2


       CHAPTER:  ID   (any other fields you need)
                 ----------------------------------------------------------
                 Ch1_Sec1      blah blah
                 Ch1_Sec2      blah blah
                 Ch1_Sec1      blah blah


 CHAPTER_TOPIC:  CHAPTER_ID     TOPIC_ID
                 ----------------------------------------------------------
                 Ch1_Sec1       1
                 Ch1_Sec2       2
                 Ch1_Sec1       1

现在，您可以轻松地编写一个JOIN语句，以便将资源与共享任何公共主题的章节相匹配：

SELECT
    r.ID AS RESOURCE_ID,
    c.ID AS CHAPTER_ID
    -- any other fields you need
FROM RESOURCE r
JOIN RESOURCE_TOPIC rt ON rt.RESOURCE_ID = r.ID
JOIN CHAPTER_TOPIC ct ON ct.TOPIC_ID = rt.TOPIC_ID
JOIN CHAPTER c ON c.ID = ct.CHAPTER_ID
GROUP BY
    r.ID AS RESOURCE_ID,
    ct.CHAPTER_ID
    -- any other fields you need

如果您想将资源匹配到所有主题匹配的章节，那么它会更复杂一些，但却是可行的。我不能从你的问题中完全确定这是否是你所需要的，但如果是的话，请告诉我，我也会提供这个查询。

关于规范化数据库的一点信息

有许多很好的理由使数据库正常化，但以下是一些要点：

• 根据当前的设置，每次出现新的主题和/或章节时，都必须继续向表中添加新列。显然，这些表很快就会爆炸成数量惊人的列(正如您所提到的)。
• 此方法允许您在不更改表结构的情况下轻松地添加或删除主题和章节。
• 在当前设置的情况下，根据常见的主题连接资源和章节几乎是不可能的。对表进行规范化使查询更简单。
• 如果要重命名主题，则只需更新“主题”表中的一行，而不必在使用它的任何地方更新它。

Answer 2

在准备“示例数据”时，您需要考虑“这是否代表了所需的所有条件？”，例如，我是否包含了所有的主题？也就是说，每个表有一行是不够的。

一旦确定了一些样本数据，您还需要努力显示“预期结果”--这应该可以从所提供的示例数据中实现。当然，手动准备预期结果是很正常的，因为您可能没有所需的查询。(但是，您还应该证明您已经绑定了一个查询。)

无论如何，下面是我对你的问题的猜测，并补充了样本数据，以涵盖更多的情况。SQL Fiddle

MySQL 5.6架构设置

CREATE TABLE RESOURCES
    (`RES_ID` int, `RES_Name` varchar(18), `RES_Topic_Sports` int, `RES_Topic_Politics` varchar(4), `RES_Topic_CurrentEvents` int)
;

INSERT INTO RESOURCES
    (`RES_ID`, `RES_Name`, `RES_Topic_Sports`, `RES_Topic_Politics`, `RES_Topic_CurrentEvents`)
VALUES
    (101, 'Sports Lesson Plan', 1, 1, 1),
    (201, 'Sports Lesson Plan', 1, NULL, NULL),
    (301, 'Political treatse', NULL, 1, NULL),
    (401, 'Event unfolding', NULL, NULL, 1)
;

CREATE TABLE CHAPTERMAP
    (`ID` int, `Topic` varchar(60), `Ch1_Sec1` varchar(4), `Ch1_Sec2` varchar(4), `Ch1_Sec3` varchar(4), `Ch1_Sec4` varchar(4), `Ch2_Sec1` int, `Ch2_Sec2` varchar(4), `Ch3_Sec1` varchar(4), `Ch3_Sec2` int, `Ch3_Sec3` int)
;

INSERT INTO CHAPTERMAP
    (`ID`, `Topic`, `Ch1_Sec1`, `Ch1_Sec2`, `Ch1_Sec3`, `Ch1_Sec4`, `Ch2_Sec1`, `Ch2_Sec2`, `Ch3_Sec1`, `Ch3_Sec2`, `Ch3_Sec3`)
VALUES
    (4, 'Sports', NULL, NULL, NULL, NULL, 1, NULL, NULL, 1, 1),
    (104, 'Politics', NULL, NULL, NULL, NULL, 1, NULL, NULL, 1, 1),
    (401, 'Current Events', NULL, NULL, NULL, NULL, 1, NULL, NULL, 1, 1)
;

查询1

select
*
from RESOURCES r
left join CHAPTERMAP c on (r.RES_Topic_Politics = 1      and c.Topic = 'Politics')
                       or (r.RES_Topic_Sports = 1        and c.Topic = 'Sports')
                       or (r.RES_Topic_CurrentEvents = 1 and c.Topic = 'Current Events') 
order by r.res_id, c.id

结果

| RES_ID |           RES_Name | RES_Topic_Sports | RES_Topic_Politics | RES_Topic_CurrentEvents |  ID |          Topic | Ch1_Sec1 | Ch1_Sec2 | Ch1_Sec3 | Ch1_Sec4 | Ch2_Sec1 | Ch2_Sec2 | Ch3_Sec1 | Ch3_Sec2 | Ch3_Sec3 |
|--------|--------------------|------------------|--------------------|-------------------------|-----|----------------|----------|----------|----------|----------|----------|----------|----------|----------|----------|
|    101 | Sports Lesson Plan |                1 |                  1 |                       1 |   4 |         Sports |   (null) |   (null) |   (null) |   (null) |        1 |   (null) |   (null) |        1 |        1 |
|    101 | Sports Lesson Plan |                1 |                  1 |                       1 | 104 |       Politics |   (null) |   (null) |   (null) |   (null) |        1 |   (null) |   (null) |        1 |        1 |
|    101 | Sports Lesson Plan |                1 |                  1 |                       1 | 401 | Current Events |   (null) |   (null) |   (null) |   (null) |        1 |   (null) |   (null) |        1 |        1 |
|    201 | Sports Lesson Plan |                1 |             (null) |                  (null) |   4 |         Sports |   (null) |   (null) |   (null) |   (null) |        1 |   (null) |   (null) |        1 |        1 |
|    301 |  Political treatse |           (null) |                  1 |                  (null) | 104 |       Politics |   (null) |   (null) |   (null) |   (null) |        1 |   (null) |   (null) |        1 |        1 |
|    401 |    Event unfolding |           (null) |             (null) |                       1 | 401 | Current Events |   (null) |   (null) |   (null) |   (null) |        1 |   (null) |   (null) |        1 |        1 |

Answer 3

您感兴趣的主题是数据库规范化。要理解，考虑到您需要在您的章节映射表中的每一章节的每一节的每一本书，你想要数据库涵盖的列。

相反，可以考虑第三个表，称为“主题”。该表中有两列: topicID和topicName。一个典型的条目是'1'，‘体育’('2'，‘政治’等等)。

现在，去掉“参考资料”表中所有与主题相关的列。现在，您在参考资料中的行看起来就像'101'，‘体育课计划’。

还有一个要规范化的表:让我们将您的"Chaptermap“表更改为只有以下列: ID、章节和部分。一个典型的行看起来像'5'，' 2 '，' 1 '，给出ID 5到第2章，第1节。

我们将把主题规范化到另几个表中，以防某个资源或章节涉及多个主题(多到多的关系)。为此，您将再创建两个表: resourceTopics和sectionTopics。resourceTopics将有两列-- resourceID和topicID。sectionTopics也是一样-- sectionID和topicID。

您的应用程序将向resourceTopics表“101”、“1”添加一行，以指示资源101(体育课程计划)与主题1(体育)相关联。同样，您还将向sectionTopics表“5”、“1”添加一行，以显示第5节(第2章第1节)和主题1(体育运动)之间的连接。

现在，如果您查询：

SELECT
r.RES_NAME, 
c.chapter,
c.section
FROM CHAPTERMAP c
INNER JOIN sectionTopics st
ON st.sectionID = c.id
INNER JOIN resourceTopics rt
ON rt.topicID = st.topicID
INNER JOIN Resources r
ON r.RES_ID = rt.resourceID
INNER JOIN Topics t
ON t.topicID = st.topicID
WHERE st.topicName = 'sports'

你应该得到你想要的结果。