MySQL 关系查询

Question

我需要查询建议。

我有 3 个表，表架构如下:

clause_type:
-------------
clause_type_id  clause_type_title
--------------  ------------
1               clause type 1
2               clause type 2


clause:
-------
clause_id   clause_type_id      clause_title        position_no
---------   ------------        ------------        -----------
1           1                   clause 1            1
2           1                   clause 2            2
3           2                   clause 3            1


clause_type_link_clause:
-----------------------
clause_id   clause_type_id      position_no
---------   ------------        ------------
3           1                   3

在“clause”表中，“clause_id”列是自增列。一个子句可以有 1:N 个子句类型。所以我有第三个表“clause_type_link_clause”来维护这个 1:n 关系。如您所见，与“条款 1 和 2”一起，“条款 3”也链接到“条款类型 1”。我需要以这样一种方式形成查询，即所有属于特定“clause_type”的“原因”都应该被返回:

Expected result:

clause_id   clause_type_id      clause_title        position_no
---------   ------------        ------------        -----------
1           1                   clause 1            1
2           1                   clause 2            2
3           1                   clause 3            3
3           2                   clause 3            1

我尝试了某种 JOINS，但没有成功。

SELECT 
  clause_type.`clause_type_id`,
  clause.`clause_id` 
FROM
  `clause` AS clause,
  `clause_type` AS clause_type 
  LEFT JOIN `clause_type_link_clause` AS clause_type_link_clause 
    ON (
      clause_type_link_clause.`clause_type_id` = clause_type.`clause_type_id`
    ) 
WHERE clause_type.`clause_type_id` = clause.`clause_type_id`

请帮忙。

Answer 1

我个人会从您的clause 表中删除caluse_type_id 和position_no，并将这些单独存储在clause_type_link_clause 中，这将避免在两个不同的地方存储相似的信息。

要执行此转换，您可以使用:

INSERT INTO clause_type_link_clause (Clause_ID, Clause_Type_ID, Position_No)
SELECT  Clause_ID, Clause_Type_ID, Position_No
FROM    Clause
WHERE   NOT EXISTS
        (   SELECT  1
            FROM    clause_type_link_clause
            WHERE   clause_type_link_clause.Clause_ID = Clause.Clause_ID
            AND     clause_type_link_clause.Clause_Type_ID = Clause.Clause_Type_ID
            AND     clause_type_link_clause.Position_No = Clause.Position_No
        );

ALTER TABLE Clause DROP COLUMN Clause_Type_ID;
ALTER TABLE Clause DROP COLUMN Position_No;

这将使您的架构如下:

clause:
-------
clause_id   clause_title 
---------   ------------ 
1           clause 1     
2           clause 2      
3           clause 3     


clause_type_link_clause:
-----------------------
clause_id   clause_type_id      position_no
---------   ------------        ------------
1           1                   3
2           1                   2
3           2                   1
3           1                   3

因此，为了获得所需的输出，您的查询就变成了

SELECT  Clause.Clause_ID,
        clause_type_link_clause.Clause_Type_ID,
        Clause.Clause_Title,
        clause_type_link_clause.Position_No,
        Clause_Type.clause_type_title
FROM    Clause
        INNER JOIN clause_type_link_clause
            ON clause_type_link_clause.Clause_ID = Clause.Clause_ID
        INNER JOIN Clause_Type
            ON Clause_Type.Clause_Type_ID = clause_type_link_clause.Clause_Type_ID
ORDER BY Clause.Clause_ID, clause_type_link_clause.Clause_Type_ID;

Example on SQL Fiddle

尽管如此，您仍然可以在不更改架构的情况下实现此目的，如下所示:

SELECT  Clause.Clause_ID,
        ctlc.Clause_Type_ID,
        Clause.Clause_Title,
        ctlc.Position_No,
        Clause_Type.clause_type_title
FROM    Clause
        INNER JOIN 
        (   SELECT  Clause_ID, Clause_Type_ID, Position_No
            FROM    clause_type_link_clause
            UNION
            SELECT  Clause_ID, Clause_Type_ID, Position_No
            FROM    Clause
        ) ctlc
            ON ctlc.Clause_ID = Clause.Clause_ID
        INNER JOIN Clause_Type
            ON Clause_Type.Clause_Type_ID = ctlc.Clause_Type_ID
ORDER BY Clause.Clause_ID, ctlc.Clause_Type_ID;

Example on SQL Fiddle