Mysql左联接事例When

Question

我有以下mysql表字段

|action_id|timestamp|user|module|action|object|info|interface|    

其内容如下：

69    |2021-12-06 22:15:43|1 |CATALOGUING|ADD|24   |item  |intranet    
122058|2022-02-23 09:47:17|13|CATALOGUING|ADD|10338|biblio|intranet

以下是我正在处理的mysql查询：

SELECT action_id, i.barcode, CONCAT('<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=',biblio.biblionumber,'">',b.title,'</a>') AS Title, biblio.author, al.timestamp, CONCAT('<a href="/cgi-bin/koha/members/moremember.pl?borrowernumber=',borrowers.borrowernumber,'">',borrowers.surname, ', ', borrowers.firstname,'</a>') AS Librarian, i.permanent_location, (CASE WHEN al.info LIKE "biblio%" THEN 'biblio' ELSE 'item' END) as typeofentry, al.action as type_of_action, al.info from action_logs AS al LEFT JOIN borrowers ON al.user =
CASE WHEN al.info LIKE "biblio%" THEN 
borrowers.borrowernumber LEFT JOIN biblio on al.object=biblio.biblionumber
ELSE borrowers.borrowernumber LEFT JOIN items AS i on al.object=i.itemnumber LEFT JOIN biblio on i.biblionumber=biblio.biblionumber
END

我正试图将CASE WHEN用于LEFT JOIN；信息"；列为biblio，则查询应保留与biblio的联接，并忽略与item表的left join。如果"；信息"；列是项目，那么它应该与项目LEFT JOIN，然后与书目LEFT JOIN。使用LEFT JOIN，这可能吗。这就是我一直在尝试模仿的：如何使用case语句来确定在上左边连接哪个字段

Answer 1

您不能像这里那样执行case/when join。由于您有一种分割的HTML链接引用方式，并且每个链接引用都有自己的书目记录上下文，因此您可能最好从两个不同的查询中获得这些片段，即书目与非书目，并将它们联合起来。把这个结果应用到你的concat过程中。类似的东西

SELECT 
PQ.action_id, 
PQ.barcode, 
CONCAT( '<a href="/cgi-bin/koha/catalogue/detail.pl?biblionumber=', 
PQ.biblionumber, '">', b.title, '</a>') Title, 
PQ.author, 
PQ.timestamp, 
CONCAT( '<a href="/cgi-bin/koha/members/moremember.pl?borrowernumber=', 
b.borrowernumber, '">', b.surname, ', ', 
b.firstname, '</a>') Librarian, 
PQ.permanent_location, 
PQ.typeofentry, 
PQ.action as type_of_action, 
PQ.info 
from
(
SELECT 
action_id, 
i.barcode, 
biblio.biblionumber,
biblio.author, 
al.timestamp, 
i.permanent_location, 
'biblio' typeofentry, 
al.action, 
al.info,
al.user
from 
action_logs al 
LEFT JOIN items i 
on al.object = i.itemnumber 
LEFT JOIN biblio 
on al.object = biblio.biblionumber
where
al.info like 'biblio%'
UNION ALL
SELECT 
action_id, 
i.barcode, 
biblio.biblionumber,
biblio.author, 
al.timestamp, 
i.permanent_location, 
'item' typeofentry, 
al.action, 
al.info,
al.user
from 
action_logs al 
LEFT JOIN items i 
on al.object = i.itemnumber 
LEFT JOIN biblio 
on i.biblionumber = biblio.biblionumber
where
NOT al.info like 'biblio%'
) PQ
LEFT JOIN borrowers b
ON PQ.user = b.borrowernumber