如何将两个含多表连接的MySQL查询按指定字段关联？

Got it, let's work through this together. You’ve got two MySQL queries that pull data with multiple joins, and you need to combine their results using the word_pdf_cords.lineNumber field. Here are a couple of easy-to-implement solutions:

Method 1: Using Subqueries #

Wrap each of your original queries as subqueries, assign them aliases, then join them on the shared lineNumber field. We’ll alias duplicate columns to avoid confusion:

SELECT 
    q1.html_path AS clocks_html_path,
    q1.xml_path AS clocks_xml_path,
    q1.word_Value AS clocks_word,
    q1.word_pdf_ID AS clocks_word_pdf_ID,
    q1.word_pdf_cords_ID AS clocks_cords_ID,
    q1.pageNumber AS clocks_pageNumber,
    q1.lineNumber,
    q2.html_path AS pirates_html_path,
    q2.xml_path AS pirates_xml_path,
    q2.word_Value AS pirates_word,
    q2.word_pdf_ID AS pirates_word_pdf_ID,
    q2.word_pdf_cords_ID AS pirates_cords_ID,
    q2.pageNumber AS pirates_pageNumber
FROM (
    -- Subquery for "clocks" results
    SELECT 
        pdf.html_path,
        pdf.xml_path, 
        word.word_Value,
        word_pdf.word_pdf_ID,
        word_pdf_cords.word_pdf_cords_ID, 
        word_pdf_cords.pageNumber, 
        word_pdf_cords.lineNumber 
    FROM word_pdf 
    INNER JOIN pdf ON pdf.PDF_ID = word_pdf.PDF_ID 
    INNER JOIN word ON word.word_ID = word_pdf.word_ID 
    INNER JOIN word_pdf_cords ON word_pdf.word_pdf_ID = word_pdf_cords.word_pdf_ID 
    WHERE word.word_Value = "clocks"
) q1
INNER JOIN (
    -- Subquery for "pirates" results
    SELECT 
        pdf.html_path,
        pdf.xml_path, 
        word.word_Value,
        word_pdf.word_pdf_ID,
        word_pdf_cords.word_pdf_cords_ID, 
        word_pdf_cords.pageNumber, 
        word_pdf_cords.lineNumber 
    FROM word_pdf 
    INNER JOIN pdf ON pdf.PDF_ID = word_pdf.PDF_ID 
    INNER JOIN word ON word.word_ID = word_pdf.word_ID 
    INNER JOIN word_pdf_cords ON word_pdf.word_pdf_ID = word_pdf_cords.word_pdf_ID 
    WHERE word.word_Value = "pirates"
) q2 ON q1.lineNumber = q2.lineNumber;

Notes: #

• We use INNER JOIN here, which only returns rows where lineNumber exists in both query results. If you need to keep rows where only one query has a matching lineNumber, swap INNER JOIN with LEFT JOIN (keeps all q1 rows) or RIGHT JOIN (keeps all q2 rows).

Method 2: Using CTEs (MySQL 8.0+) #

If you’re running MySQL 8.0 or newer, Common Table Expressions (CTEs) make the code cleaner and more readable by defining your query sets upfront:

WITH q1 AS (
    -- Define CTE for "clocks" results
    SELECT 
        pdf.html_path,
        pdf.xml_path, 
        word.word_Value,
        word_pdf.word_pdf_ID,
        word_pdf_cords.word_pdf_cords_ID, 
        word_pdf_cords.pageNumber, 
        word_pdf_cords.lineNumber 
    FROM word_pdf 
    INNER JOIN pdf ON pdf.PDF_ID = word_pdf.PDF_ID 
    INNER JOIN word ON word.word_ID = word_pdf.word_ID 
    INNER JOIN word_pdf_cords ON word_pdf.word_pdf_ID = word_pdf_cords.word_pdf_ID 
    WHERE word.word_Value = "clocks"
),
q2 AS (
    -- Define CTE for "pirates" results
    SELECT 
        pdf.html_path,
        pdf.xml_path, 
        word.word_Value,
        word_pdf.word_pdf_ID,
        word_pdf_cords.word_pdf_cords_ID, 
        word_pdf_cords.pageNumber, 
        word_pdf_cords.lineNumber 
    FROM word_pdf 
    INNER JOIN pdf ON pdf.PDF_ID = word_pdf.PDF_ID 
    INNER JOIN word ON word.word_ID = word_pdf.word_ID 
    INNER JOIN word_pdf_cords ON word_pdf.word_pdf_ID = word_pdf_cords.word_pdf_ID 
    WHERE word.word_Value = "pirates"
)
SELECT 
    q1.html_path AS clocks_html_path,
    q1.xml_path AS clocks_xml_path,
    q1.word_Value AS clocks_word,
    q1.word_pdf_ID AS clocks_word_pdf_ID,
    q1.word_pdf_cords_ID AS clocks_cords_ID,
    q1.pageNumber AS clocks_pageNumber,
    q1.lineNumber,
    q2.html_path AS pirates_html_path,
    q2.xml_path AS pirates_xml_path,
    q2.word_Value AS pirates_word,
    q2.word_pdf_ID AS pirates_word_pdf_ID,
    q2.word_pdf_cords_ID AS pirates_cords_ID,
    q2.pageNumber AS pirates_pageNumber
FROM q1
INNER JOIN q2 ON q1.lineNumber = q2.lineNumber;

Notes: #

• CTEs work exactly like the subquery method under the hood, but they’re easier to maintain if you ever need to tweak the base queries later.

内容的提问来源于stack exchange，提问作者Tim Hunt