规则大小写WHEN THEN ELSE NULL
规则大小写WHEN THEN ELSE NULL
提问于 2017-06-12 18:44:27
我有一个仓库函数，我想使用CASE WHEN THEN ELSE，但是规则不允许在ELSE中使用NULL。当然，我用谷歌搜索过，但我找不到如何避免它的答案。
public function countAverageOdd($user) {
return $this->getEntityManager()
->createQuery('
SELECT
avg(
CASE
WHEN p.homeAway = \'h\' AND p.moneyline IS NOT NULL THEN m.home
WHEN p.homeAway = \'d\' AND p.moneyline IS NOT NULL THEN m.draw
WHEN p.homeAway = \'e\' AND p.moneyline IS NOT NULL THEN m.away
WHEN p.homeAway = \'h\' AND p.spread IS NOT NULL THEN s.home
WHEN p.homeAway = \'e\' AND p.spread IS NOT NULL THEN s.away
WHEN p.homeAway = \'h\' AND p.total IS NOT NULL THEN t.over
WHEN p.homeAway = \'e\' AND p.total IS NOT NULL THEN t.under
ELSE NULL
END
)
FROM
AppBundle:Predictions p
LEFT JOIN p.moneyline m
LEFT JOIN p.spread s
LEFT JOIN p.total t
WHERE p.user = :user ')
->setParameter(":user", $user)
->setMaxResults(1)
->getSingleScalarResult();
}错误：语法错误行0，列900:错误:意外的'NULL'
https://github.com/doctrine/doctrine2/issues/3160
doctrinebot在2013年5月22日发表评论
问题已关闭，解决方案为“with‘t Fix”
请给我一些建议。
回答 3
Stack Overflow用户
回答已采纳
发布于 2017-07-27 23:39:21
通过将null值作为参数传递，我设法绕过了理论逻辑检查。这是一种黑客行为，但似乎是唯一有效的方法。
public function countAverageOdd($user) {
return $this->getEntityManager()
->createQuery('
SELECT
avg(
CASE
WHEN p.homeAway = \'h\' AND p.moneyline IS NOT NULL THEN m.home
WHEN p.homeAway = \'d\' AND p.moneyline IS NOT NULL THEN m.draw
WHEN p.homeAway = \'e\' AND p.moneyline IS NOT NULL THEN m.away
WHEN p.homeAway = \'h\' AND p.spread IS NOT NULL THEN s.home
WHEN p.homeAway = \'e\' AND p.spread IS NOT NULL THEN s.away
WHEN p.homeAway = \'h\' AND p.total IS NOT NULL THEN t.over
WHEN p.homeAway = \'e\' AND p.total IS NOT NULL THEN t.under
ELSE :null
END
)
FROM
AppBundle:Predictions p
LEFT JOIN p.moneyline m
LEFT JOIN p.spread s
LEFT JOIN p.total t
WHERE p.user = :user ')
->setParameters(array(":user"=> $user,":null"=>NULL))
->setMaxResults(1)
->getSingleScalarResult();Stack Overflow用户
发布于 2019-05-10 03:23:56
另一个技巧是使用supported，即NULLIF。e.g
CASE WHEN WHEN p.homeAway = \'h\' THEN 'xyz' ELSE NULLIF(1,1) END这是从https://github.com/doctrine/orm/issues/5801#issuecomment-336132280得到的
谢谢!
Stack Overflow用户
发布于 2017-06-12 18:54:49
查看myql case operator documentation。ELSE是可选的，因此您可以直接删除它
public function countAverageOdd($user) {
return $this->getEntityManager()
->createQuery('
SELECT
avg(
CASE
WHEN p.homeAway = \'h\' AND p.moneyline IS NOT NULL THEN m.home
WHEN p.homeAway = \'d\' AND p.moneyline IS NOT NULL THEN m.draw
WHEN p.homeAway = \'e\' AND p.moneyline IS NOT NULL THEN m.away
WHEN p.homeAway = \'h\' AND p.spread IS NOT NULL THEN s.home
WHEN p.homeAway = \'e\' AND p.spread IS NOT NULL THEN s.away
WHEN p.homeAway = \'h\' AND p.total IS NOT NULL THEN t.over
WHEN p.homeAway = \'e\' AND p.total IS NOT NULL THEN t.under
END
)
FROM
AppBundle:Predictions p
LEFT JOIN p.moneyline m
LEFT JOIN p.spread s
LEFT JOIN p.total t
WHERE p.user = :user ')
->setParameter(":user", $user)
->setMaxResults(1)
->getSingleScalarResult();页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44497483
复制相关文章
相似问题
腾讯云开发者
