select count(distinct consumerno),
sum (case when ccon like '%D%' then 1 else 0 end) as Domestic,
sum (case when ccon like '%B%' then 1 else 0 end) as Business
from consumer left join contract on consumerno = cconsumer
where status = 'Active'
输出
total B D
35952 35694 1669
有两张表:消费者表和合同表
我在这里想要实现的是获取从商务或家庭购买产品的消费者总数。合同表有重复的消费者编号,因为它们从商务变为家庭或从家庭变为商务。从上面可以看出,查询总数是正确的,但家庭和商务的总和是错误的。有没有更好的方法来实现这一点。
任何评论都将受到赞赏
答案1
我假设字段“状态”位于“合同”表中。最简单的方法是创建 3 个单独的查询,确保它们反映每个总数的正确数字,并避免不必要的连接。
SELECT COUNT(DISTINCT consumerno) as TOTAL FROM consumer LEFT JOIN contract ON consumerno = cconsumer WHERE status='Active';
SELECT COUNT (DISTINCT cconsumer) as B FROM contract WHERE ccon LIKE '%B%' AND status='Active';
SELECT COUNT (DISTINCT cconsumer) as D FROM contract WHERE ccon LIKE '%D%' AND status='Active';
如果有必要,您可以在占位符字段上建立一个连接,以将它们全部放在一行中。