当计数条件大于0时创建一个case语句

I want to make a pretty complex SQL query, which results in 0/1 or TRUE/FALSE depending on my conditions.

我想制作一个非常复杂的SQL查询，根据我的条件，结果为0/1或TRUE / FALSE。

Idea is that I have a customer table, and then I want to make a couple of checks in different tables, and then if either is true, then we return TRUE, and if not, FALSE:

想法是我有一个客户表，然后我想在不同的表中进行几个检查，然后如果其中一个为真，那么我们返回TRUE，如果不是，则返回FALSE：

I was thinking to do this with CASE. See example below:

我想用CASE做这件事。见下面的例子：

select 
    case 
        ((select count(*) from boughtleads bl where bl.customerid = cu.id)>0)
            then 'TRUE' 
            else 'FALSE' 
    end 

from customers cu
left join leadagents la on la.customerid = cu.id
where cu.vatnumber = '30218124'

This returns:

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '>'.

My challenge is then. First of all, the above query fails for some reason, my other problem is I can't use an OR.

那时我的挑战是。首先，上面的查询由于某种原因失败，我的另一个问题是我不能使用OR。

My ideal pseudocode would be something like:

我理想的伪代码是这样的：

select 
    case 
        (
            ((select count(*) from boughtleads bl where bl.customerid = cu.id)>0)
            OR
            ((select count(*) from leadnotifications ln where ln.leadagentid = la.id)>))
                then 'TRUE' 
                else 'FALSE' 
    end 

from customers cu
left join leadagents la on la.customerid = cu.id
where cu.vatnumber = '30218124'

Any ideas on how to attack this?

关于如何攻击这个的任何想法？

1 个解决方案

#1

You are missing when:

你错过了：

select (case when (select count(*) from boughtleads bl where bl.customerid = cu.id) > 0
             then 'TRUE' 
             else 'FALSE' 
        end)
from customers cu left join
     leadagents la
     on la.customerid = cu.id
where cu.vatnumber = '30218124';

That said, from a performance perspective it is better to write such conditions using exists:

也就是说，从性能角度来看，最好使用exists来编写这样的条件：

select (case when exists (select 1 from boughtleads bl where bl.customerid = cu.id)
             then 'TRUE' 
             else 'FALSE' 
        end)
from customers cu left join
     leadagents la
     on la.customerid = cu.id
where cu.vatnumber = '30218124'

The count(*) version has to find all matching rows in boughtleads. The exists version can stop at the first match.

count（*）版本必须在boughtleads中找到所有匹配的行。存在的版本可以在第一场比赛时停止。

#1