MySQL查询,其中JOIN依赖于CASE [英] MySQL query where JOIN depends on CASE
问题描述
我现在知道,CASE只能在WHERE上下文中使用.虽然,我需要根据column
值使用不同的表.我尝试过的内容如下:
As I am now aware, CASE can be used only in WHERE context. Though, I need to use different table depending on column
value. What I've tried looks like this:
SELECT
`ft1`.`task`,
COUNT(`ft1`.`id`) `count`
FROM
`feed_tasks` `ft1`
CASE
`ft1`.`type`
WHEN
1
THEN
(INNER JOIN `pages` `p1` ON `p1`.`id` = `ft1`.`reference_id`)
WHEN
2
THEN
(INNER JOIN `urls` `u1` ON `u1`.`id` = `ft1`.`reference_id`)
WHERE
`ft1`.`account_id` IS NOT NULL AND
`a1`.`user_id` = {$db->quote($user['id'])}
现在我知道这是无效的语法,最接近的替代方法是什么?
Now that I know this is invalid syntax, what's the closest alternative?
推荐答案
可能需要进行调整以返回正确的结果,但我希望您能明白:
It probably needs tweaking to return the correct results but I hope you get the idea:
SELECT ft1.task, COUNT(ft1.id) AS count
FROM feed_tasks ft1
LEFT JOIN pages p1 ON ft1.type=1 AND p1.id = ft1.reference_id
LEFT JOIN urls u1 ON ft1.type=2 AND u1.id = ft1.reference_id
WHERE COALESCE(p1.id, u1.id) IS NOT NULL
AND ft1.account_id IS NOT NULL
AND a1.user_id = :user_id
关于CASE...END
的一点说明.您的原始代码无法运行,因为与PHP或JavaScript不同,SQL CASE
并不是一种流控制结构,它无法选择将运行代码的哪一部分.而是返回一个表达式.因此,您可以执行以下操作:
A little note about CASE...END
. Your original code does not run because, unlike PHP or JavaScript, the SQL CASE
is not a flow control structure that allows to choose which part of the code will run. Instead, it returns an expression. So you can do this:
SELECT CASE
WHEN foo<0 THEN 'Yes'
ELSE 'No'
END AS is_negative
FROM bar
...但不是这样:
-- Invalid
CASE
WHEN foo<0 THEN SELECT 'Yes' AS is_negative
ELSE SELECT 'No' AS is_negative
END
FROM bar
这篇关于MySQL查询,其中JOIN依赖于CASE的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!