将计数函数添加到现有查询中
将计数函数添加到现有查询中
提问于 2022-11-18 05:19:20
我有四张这样的桌子：
create table GROSS(DATE_ACT DATE, SUB_ID BIGINT, PP_ID BIGINT, CUSTOMER_TYPE VARCHAR(50), REACTIVATION int);
insert into GROSS values(2019-11-3, 234, 5, 'Business', 1);
insert into GROSS values(2018-9-2, 131, 8, 'Business', 0);
insert into GROSS values(2018-11-3, 98, 3, 'Private', 1);
create table TARIFF(PP_ID INT, PP_NAME VARCHAR(100), SUB_ID INT, PP_START_DATE DATE, PP_END_DATE DATE);
insert into TARIFF values(3, 'PLAN_1', 98, 2021-5-3, 2021-6-3);
insert into TARIFF values(5, 'Business plan 3.0', 234, 2021-5-6, 2021-6-6);
insert into TARIFF values(8, 'Business plan 4.0', 131, 2021-5-10, 2021-6-10);
create table SERVICE(SERVICE_START_DATE DATE, SERVICE_STOP_DATE DATE, SUB_ID INT, SERVICE_NAME VARCHAR(100));
insert into SERVICE values(2021-5-7, 2021-6-7, 98, 'Unlimited Internet 2');
insert into SERVICE values(2021-5-7, 2021-6-7, 98, 'Internet');
insert into SERVICE values(2021-5-7, 2021-6-7, 98, 'Unlimited Internet 512');
create table SUBSCRIBER(MONTH DATE, COMPANY_NAME VARCHAR(100), SUB_ID INT, CUSTOMER_TYPE VARCHAR(100), STATUS INT, PP_TYPE_ID VARCHAR(50));
insert into SUBSCRIBER values(2022-1-6, 'A1', 98, 'Private', 1, 'Fixed');
insert into SUBSCRIBER values(2022-1-6, 'MTS', 234, 'Business', 1, 'Fixed');
insert into SUBSCRIBER values(2022-1-6, 'Life', 131, 'Business', 1, 'Fixed');
insert into GROSS values(2021-12-15, 228, 5, 'Business', 0);
insert into TARIFF values(5, 'Бизнес-план 3.0', 228, 2021-12-15, 2999-01-01);
insert into SERVICE values(2021-12-15, 2999-01-01, 228, 'Безлимитный Интернет 512');
insert into SUBSCRIBER values(2022-01-01,'MTS', 228, 'Business', 1, 'Voice');我提出了一个问题，我需要这样做：
select GROSS.DATE_ACT, GROSS.SUB_ID, TARIFF.PP_NAME, SERVICE.SERVICE_NAME, SUBSCRIBER.COMPANY_NAME
from GROSS
inner join TARIFF on GROSS.SUB_ID = TARIFF.SUB_ID
inner join SERVICE on TARIFF.SUB_ID = SERVICE.SUB_ID
inner join SUBSCRIBER on SERVICE.SUB_ID = SUBSCRIBER.SUB_ID
where MONTH(GROSS.DATE_ACT) = 12
and GROSS.CUSTOMER_TYPE = 'Business'
and TARIFF.PP_NAME regexp 'Бизнес-план.+'
and GROSS.DATE_ACT = SERVICE.SERVICE_START_DATE
and SERVICE.SERVICE_NAME = 'Безлимитный интернет 512' or 'Безлимитный интернет 1' or 'Безлимитный интернет 2'
and YEAR(SERVICE.SERVICE_STOP_DATE) = 2999;
如何添加count函数，以便它与查询中返回的表一起返回，同时，我需要计算在所有用户中，通常有多少活动用户(SUBSCRIBER.STATUS = 1)的SERVICE.COMPANY_NAME与接收的用户相同，在本例中是MTS。
我浏览了所有的网络，但我的大脑无法帮助我解决这个问题
回答 1
Stack Overflow用户
发布于 2022-11-18 07:56:40
添加case语句进行计数，
select GROSS.DATE_ACT,
GROSS.SUB_ID,
TARIFF.PP_NAME,
SERVICE.SERVICE_NAME,
SUBSCRIBER.COMPANY_NAME,
SUM(CASE WHEN(SUBSCRIBER.STATUS = 1 and SUBSCRIBER.COMPANY_NAME = 'MTS') THEN 1 ELSE 0 END) as CNT
from GROSS
inner join TARIFF on GROSS.SUB_ID = TARIFF.SUB_ID
inner join SERVICE on TARIFF.SUB_ID = SERVICE.SUB_ID
inner join SUBSCRIBER on SERVICE.SUB_ID = SUBSCRIBER.SUB_ID
where MONTH(GROSS.DATE_ACT) = 12
and GROSS.CUSTOMER_TYPE = 'Business'
and TARIFF.PP_NAME regexp 'Бизнес-план.+'
and GROSS.DATE_ACT = SERVICE.SERVICE_START_DATE
and SERVICE.SERVICE_NAME = 'Безлимитный интернет 512' or 'Безлимитный интернет 1' or 'Безлимитный интернет 2'
and YEAR(SERVICE.SERVICE_STOP_DATE) = 2999
group by GROSS.DATE_ACT,
GROSS.SUB_ID,
TARIFF.PP_NAME,
SERVICE.SERVICE_NAME,
SUBSCRIBER.COMPANY_NAME;找不到您所提到的SERVICE.COMPANY_NAME列。在解析时相应地编辑。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74485222
复制相关文章
相似问题
腾讯云开发者
