首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >规则大小写WHEN THEN ELSE NULL

规则大小写WHEN THEN ELSE NULL
EN

Stack Overflow用户
提问于 2017-06-12 18:44:27
回答 3查看 3.7K关注 0票数 5

我有一个仓库函数,我想使用CASE WHEN THEN ELSE,但是规则不允许在ELSE中使用NULL。当然,我用谷歌搜索过,但我找不到如何避免它的答案。

代码语言:javascript
复制
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”

请给我一些建议。

EN

回答 3

Stack Overflow用户

回答已采纳

发布于 2017-07-27 23:39:21

通过将null值作为参数传递,我设法绕过了理论逻辑检查。这是一种黑客行为,但似乎是唯一有效的方法。

代码语言:javascript
复制
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();
票数 4
EN

Stack Overflow用户

发布于 2019-05-10 03:23:56

另一个技巧是使用supported,即NULLIF。e.g

代码语言:javascript
复制
CASE WHEN WHEN p.homeAway = \'h\' THEN 'xyz' ELSE NULLIF(1,1) END

这是从https://github.com/doctrine/orm/issues/5801#issuecomment-336132280得到的

谢谢!

票数 2
EN

Stack Overflow用户

发布于 2017-06-12 18:54:49

查看myql case operator documentationELSE是可选的,因此您可以直接删除它

代码语言:javascript
复制
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();
票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/44497483

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档