更新时间:2023-12-01 09:17:58
除了在ref_types表中具有ReferenceType列之外,您还需要一个Reference_ID列,该列将相应表中的实际ID引用为
In addition to having ReferenceType column in ref_types table, you also need a Reference_ID column that refers to the actual ID in the corresponding table as a foreign key.
//ref_types table
ID Article_ID ReferenceType Reference_ID
1 1 book 1
2 1 article 1
3 1 article 2
4 1 book 2
然后,您可以避免WHILE循环,而让MySQL通过JOIN为您完成工作:
Then, you can avoid a WHILE loop and let MySQL do the work for you with JOINs:
SELECT CONCAT('record ', rt.ID, ': ',
COALESCE(ar.Article_Title, tr.Thesis_Title, br.Book_Title))
FROM ref_types rt
LEFT JOIN article_refs ar
ON rt.ReferenceType = 'article' AND ar.ID = rt.Reference_ID
LEFT JOIN book_refs br
ON rt.ReferenceType = 'book' AND br.ID = rt.Reference_ID
LEFT JOIN thesis_refs tr
ON rt.ReferenceType = 'thesis' AND tr.ID = rt.Reference_ID
得出结果:
record 1: book1
record 2: article1
record 3: article2
record 4: book2