sql-server - 如何找出同一张表中特定状态的多个计数
问题描述
我有 Orders 和 OrderSubscription 表。表之间的关系是一对多的。我想找出其中一个 OrderSubscription 状态必须为 'Active' 和 'Cancelled' 并且应该有多个 'Pending' 状态的订单。
所以最终结果应该是 order id =2 记录。
编写了以下查询及其工作正常。或者还有其他更好的方法来过滤记录吗?
select o.id,
o.order_number,
o.order_status
from Orders o
where o.order_status = 'Active' AND
1=(select count(*) from OrderSubscription where order_id = o.id and subs_status='Active')
AND
1=( select count(*) from OrderSubscription where order_id = o.id and subs_status='Cancelled')
AND
1 < (select count(*) from OrderSubscription where order_id = o.id and subs_status='Pending')
group by o.id,
o.order_number,
o.order_status
order by o.id;
Create Table Orders(id number(5),order_number number(12),order_status varchar2(20));
insert into Orders Values (1,12345,'Active');
insert into Orders Values (2,5757575,'Active');
insert into Orders Values (3,979797979,'Active');
insert into Orders Values (4,3131313133,'Active');
Create Table OrderSubscription(id number(5),order_id number(5),subs_name varchar2(20),subs_status varchar2(20));
insert into OrderSubscription Values(1,1,'360 fhf','Pending');
insert into OrderSubscription Values (2,1,'720 cef','Active');
insert into OrderSubscription Values (3,1,'540 abc','Cancelled');
insert into OrderSubscription Values (1,2,'360 fhf','Active');
insert into OrderSubscription Values (2,2,'720 cef','Pending');
insert into OrderSubscription Values (3,2,'540 abc','Pending');
insert into OrderSubscription Values (4,2,'adada abc','Cancelled');
insert into OrderSubscription Values (1,3,'720 cef','Pending');
insert into OrderSubscription Values (2,3,'60 ert','Active');
insert into OrderSubscription Values (3,3,'60 ert','Cancelled');
insert into OrderSubscription Values (1,4,'360 fhf','Pending');
insert into OrderSubscription Values (2,4,'560 fhf','Pending');
insert into OrderSubscription Values (3,4,'560 fhf','Active');
解决方案
您似乎想使用条件聚合方法。一种正确的方法是按订单号聚合,然后断言各种状态计数。
SELECT o.order_number
FROM Orders o
INNER JOIN OrderSubscription os
ON os.order_id = o.id
WHERE
o.order_status = 'Active'
GROUP BY
o.order_number
HAVING
COUNT(CASE WHEN os.subs_status = 'Active' THEN 1 END) > 0 AND
COUNT(CASE WHEN os.subs_status = 'Cancelled' THEN 1 END) > 0 AND
COUNT(CASE WHEN os.subs_status = 'Pending' THEN 1 END) > 1;
推荐阅读
- bash - 从关联数组 Bash 中删除所有元素
- javascript - 如何为函数创建 on 和 off 侦听器
- java - 我应该使我的 Executor 服务实例在类级别上定义为静态吗?
- firebase - 如果子集合中存在用户电子邮件,则 firebase 规则
- python - 如何根据另一列中的字符串值删除一列中的重复值?
- google-apps-script - 如何根据变量设置日期?
- linux - SUSE Linux Enterprise Serve (SLES) 的发行版本与 OpenSUSE 的发行版本有什么关系?
- css - React css 样式文件不想被导入
- html - 背景图像引导程序 4.0 顶部的导航栏
- java - 可以使用流 api 重写以下循环,还是我徒劳无功?