sql - 计算不同的和非不同的值

Question

我有一个像这样的 sql 表:

+----+-----------+------------+| Id | EnquiryId | PropertyId |+----+-----------+------------+|  1 |         1 |        20  ||  2 |         1 |        25  ||  3 |         1 |        26  ||  4 |         2 |        20  ||  5 |         3 |        20  ||  6 |         4 |        20  |+----+-----------+------------+

I want to count how many enquiries propertyid 20 has on it's own, and how many that is shared with other properties

So the result should be something like this:

Number of single enguiry: 3

Number of shared enquiries: 1

It's perfectly fine if it requires two select statements :)

The attempts so far looks like this:

(select count(distinct [EnquiryId]) 
from [EnquiryProperty] where propertyid=20) as 'SingleEnquiry'

这给了我 4 个结果(我需要它是 3 个)

(select count([PropertyId]) FROM [EnquiryProperty] where propertyid=20 GROUP BY propertyid HAVING COUNT(*) > 1) as 'MultipleEnquiry'

这给了我 0 个结果

Answer 1

一种方法是两级聚合。内层为每个查询分配标志。第二个使用这些来获取您想要的信息:

select sum(is_20 * (1 - is_not_20)) as single_enquiry,
       sum(is_20 * is_not_20) as shared_enquiry
from (select enquiryid,
             max(case when propertyid = 20 then 1 else 0 end) as is_20,
             max(case when propertyid <> 20 then 1 else 0 end) as is_not_20
      from t
      group by enquiryid
     ) e;