select count(1), count(a.ccid) from ( select cc.ccid from ( select s_member_id from dm.dm_object_member_v1 where reg_datetime >= '2023-01-01' and source_channel_class_id = 'E01' -- CRM(微信&线下) and reg_brand_id = 'B072' -- Yiyang ) a left join ( select * from audience_data_service_v2.sys_user_ccid_all where merge_rule = '13' and dt = 'current' and user_type_id = 5 ) cc on a.s_member_id = cc.user_id ) a inner join ( select cc.ccid from ( select s_member_id from dm.dm_object_member_v1 where reg_datetime >= '2023-01-01' and source_channel_class_id = 'E01' -- CRM(微信&线下) and reg_brand_id = 'B071' -- Aisipei ) a left join ( select * from audience_data_service_v2.sys_user_ccid_all where merge_rule = '13' and dt = 'current' and user_type_id = 5 ) cc on a.s_member_id = cc.user_id ) b on a.ccid = b.ccid

这是一个 SQL 查询语句，其目的是查询在2023年1月1日及以后注册且渠道来源为CRM（微信&线下），且品牌为“Yiyang”的会员数量以及同时在2023年1月1日及以后注册且渠道来源为CRM（微信&线下），且品牌为“Aisipei”的会员数量，并统计这两个数量的总数和其中至少有一个品牌的会员数量。

相关问题

select count(1) from ( SELECT ccid from ( SELECT c.ccid, t0.reg_brand_id FROM ( SELECT ccid,user_id FROM audience_data_service_v2.sys_user_ccid_all WHERE dt = 'current' and merge_rule = '11' and user_type_id in (5) ) c INNER JOIN ( SELECT s_member_id, reg_brand_id FROM dm.dm_object_member_v1 rt GROUP BY s_member_id, reg_brand_id ) t0 on c.user_id = t0.s_member_id ) a where reg_brand_id in ('B071', 'B072') GROUP BY ccid having count(ccid) > 1 ) b;

这也是一个 SQL 查询语句，其目的是查询同时在“Yiyang”和“Aisipei”品牌下注册的会员数量，并返回这个数量。具体实现是通过先将数据源表 audience_data_service_v2.sys_user_ccid_all 和 dm.dm_object_member_v1 进行内连接，得到品牌信息和会员 ID，然后按照会员 ID 进行分组，统计每个会员在哪些品牌下注册，再筛选出同时在“Yiyang”和“Aisipei”品牌下注册的会员，并返回这个数量。

select c.PurchaseNo as DocNo,c.PurchaseDate as DocDate,'' as 'User' ,'采购入库' as 'OperationType',c.Remark,c.Creator,c.CreateDate from It_Equip a join It_PurchaseInList b on a.EquipId=b.EquipId join It_PurchaseIn c on b.PurchaseId=c.PurchaseId where a.FixedNo='CCID-BB-BJB-23058' union select c.ReceiveNo as DocNo,c.ReceiveDate as DocDate,c.Custodian as 'User' ,'资产领用' as 'OperationType',c.Remark,c.Creator,c.CreateDate from It_Equip a join It_GoodsReceiveList b on a.EquipId=b.EquipId join It_GoodsReceive c on b.ReceiveId=c.ReceiveId where a.FixedNo='CCID-BB-BJB-23058' union select c.BackNo as DocNo,c.BackDate as DocDate,c.Applicant as 'User','资产退库' as 'OperationType',c.Remark,c.Creator,c.CreateDate from It_Equip a join It_GoodsBackList b on a.EquipId=b.EquipId join It_GoodsBack c on b.BackId=c.BackId where a.FixedNo='CCID-BB-BJB-23058' union select c.ScrapNo as DocNo,c.ScrapDate as DocDate,c.Applicant as 'User', '资产报废' as 'OperationType',c.Remark,c.Creator,c.CreateDate from It_Equip a join It_GoodsScrapList b on a.EquipId=b.EquipId join It_GoodsScrap c on b.ScrapId=c.ScrapId where a.FixedNo='CCID-BB-BJB-23058' union select b.ChangeNo as DocNo,b.RegDate as DocDate,b.CustodianAfter as 'User','资产变更' as 'OperationType',b.Remark,b.Creator,b.CreateDate from It_Equip a join It_GoodsChange b on a.EquipId=b.EquipId where a.FixedNo='CCID-BB-BJB-23058' 优化sql语句

可以考虑使用UNION ALL代替UNION，因为UNION会去重，而UNION ALL不会去重，从而减少了查询的开销。同时，可以使用子查询来减少重复的代码。优化后的SQL语句如下：

SELECT DocNo, DocDate, User, OperationType, Remark, Creator, CreateDate
FROM (
SELECT c.PurchaseNo AS DocNo, c.PurchaseDate AS DocDate, '' AS User, '采购入库' AS OperationType, c.Remark, c.Creator, c.CreateDate
FROM It_Equip a
JOIN It_PurchaseInList b ON a.EquipId = b.EquipId
JOIN It_PurchaseIn c ON b.PurchaseId = c.PurchaseId
WHERE a.FixedNo = 'CCID-BB-BJB-23058'

UNION ALL

SELECT c.ReceiveNo AS DocNo, c.ReceiveDate AS DocDate, c.Custodian AS User, '资产领用' AS OperationType, c.Remark, c.Creator, c.CreateDate
FROM It_Equip a
JOIN It_GoodsReceiveList b ON a.EquipId = b.EquipId
JOIN It_GoodsReceive c ON b.ReceiveId = c.ReceiveId
WHERE a.FixedNo = 'CCID-BB-BJB-23058'

UNION ALL

SELECT c.BackNo AS DocNo, c.BackDate AS DocDate, c.Applicant AS User, '资产退库' AS OperationType, c.Remark, c.Creator, c.CreateDate
FROM It_Equip a
JOIN It_GoodsBackList b ON a.EquipId = b.EquipId
JOIN It_GoodsBack c ON b.BackId = c.BackId
WHERE a.FixedNo = 'CCID-BB-BJB-23058'

UNION ALL

SELECT c.ScrapNo AS DocNo, c.ScrapDate AS DocDate, c.Applicant AS User, '资产报废' AS OperationType, c.Remark, c.Creator, c.CreateDate
FROM It_Equip a
JOIN It_GoodsScrapList b ON a.EquipId = b.EquipId
JOIN It_GoodsScrap c ON b.ScrapId = c.ScrapId
WHERE a.FixedNo = 'CCID-BB-BJB-23058'

UNION ALL

SELECT b.ChangeNo AS DocNo, b.RegDate AS DocDate, b.CustodianAfter AS User, '资产变更' AS OperationType, b.Remark, b.Creator, b.CreateDate
FROM It_Equip a
JOIN It_GoodsChange b ON a.EquipId = b.EquipId
WHERE a.FixedNo = 'CCID-BB-BJB-23058'
) AS t
ORDER BY DocDate DESC, DocNo DESC