使用CASE优化MYSQL LEFT JOIN [英] MYSQL LEFT JOIN optimization with CASE

问题描述

我花了一些时间尝试使用CASE进行此SELECT，但是我失败了...(由于我现在正在使用COLASCE())

I spent some time trying to get working this SELECT with CASE but I failed... (thank to that I'm using COLASCE() now)

如何使用CASE/IF语句优化此SELECT?这是从字段选择的不同表中查询的快速方法吗?

How could I optimize this SELECT by using CASE/IF sentences? Is this a fast way to query from different tables selected by a field?

SELECT a.folderid, a.foldername, a.contenttype, COALESCE(b.descriptor, c.descriptor, d.descriptor, e.descriptor, f.descriptor) as descriptor
FROM t_folders a
LEFT JOIN t_files b
ON a.contenttype = 'file' AND a.contentid = b.fileid
LEFT JOIN t_links c
ON a.contenttype = 'link' AND a.contentid = c.linkid
LEFT JOIN t_extfiles d
ON a.contenttype = 'extfile' AND a.contentid = d.extfileid
LEFT JOIN t_videos e
ON a.contenttype = 'video' AND a.contentid = e.videoid
LEFT JOIN t_exams f
ON a.contenttype = 'exam' AND a.contentid = f.examid
WHERE a.folderid = $folderId
ORDER BY a.folderid DESC

推荐答案

使用case语句不会使查询在您的情况下更快，但是由于您要查询，因此下面是它的外观.

Using case statement will not make the query faster in your case, but since you asked for it, below is how it would look like.

SELECT a.folderid, a.foldername, a.contenttype, 
    (CASE a.contenttype
        WHEN 'file' THEN b.descriptor
        WHEN 'link' THEN c.descriptor
        WHEN 'extfile' THEN d.descriptor
        WHEN 'video' THEN e.descriptor
        ELSE f.descriptor
    END CASE) AS descriptor
FROM t_folders a
LEFT JOIN t_files b ON a.contenttype = 'file' AND a.contentid = b.fileid
LEFT JOIN t_links c ON a.contenttype = 'link' AND a.contentid = c.linkid
LEFT JOIN t_extfiles d ON a.contenttype = 'extfile' AND a.contentid = d.extfileid
LEFT JOIN t_videos e ON a.contenttype = 'video' AND a.contentid = e.videoid
LEFT JOIN t_exams f ON a.contenttype = 'exam' AND a.contentid = f.examid
WHERE a.folderid = $folderId
ORDER BY a.folderid DESC

如果每个t_files，t_links等表都具有folder_id字段，我也将尝试在这些表上执行UNION，然后将结果与t_folders左连接以获取folderid和foldername.

If each of the t_files, t_links, etc tables has the folder_id field, I would also try doing a UNION on these tables and then left join the result with t_folders to get the folderid and foldername.

这篇关于使用CASE优化MYSQL LEFT JOIN的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！