Mysql 左连接案例

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，这样如果“info”列是 biblio，则查询应该左连接与 biblio 并忽略与项目表的 LEFT JOIN。如果“info”列是 item，那么它应该与 item 左连接，然后与 biblio 左连接。使用 LEFT JOIN 是否可以实现这一点？这就是我一直在尝试模仿的： 如何使用 case 语句来确定要离开哪个字段

Answer 1

您无法像此处那样进行案例/加入时。由于您有一种分开的方式来执行 HTML 链接引用，并且每个引用都有自己的 biblio 记录上下文，因此您最好从 biblio 与非 biblio 的两个不同查询中获取片段，并对其进行 UNION。获取该结果并将其应用于您的连接过程。像这样的东西

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