销售额为0的店铺，不统计到商店店数量中，怎么改

sales_amt_day是当日累计销售额

，shop_id是商店店id。

=============

加上and max(sales_amt_day)>0显示报错

SQL lanst 发布于 2023-11-6 14:13 （编辑于 2023-11-6 14:41）

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共2回答

最佳回答

snrtuemcLv8专家互助
发布于2023-11-6 14:14（编辑于 2023-11-6 14:24）

嵌套一层

select * from (你的sql) where sales_amt_day>0

=======

这边的sql语句嵌套查询，过滤到等于0的数据

举报收起评论(8)

lanst(提问者) 请问怎么嵌套 select ${NVL(SWITCH(showtype_area,32,\"b.shop_id\",999,\"b.city_id\",16,\"b.region_id_ud\",4,\"concat(b.manage_dist_id,b.prov_id)\",2,\"b.manage_dist_id\",512,\"b.support_center_id\",1,\"\'全国\'\"),\"\'空\'\") } area_id, -- 区域id汇总维度 ${nvl(SWITCH(showtype_area,32,\"b.shop_name\",999,\"b.city_name\",16,\"b.region_name_ud\",4,\"b.prov_name\",2,\"b.manage_dist_name\",512,\"b.support_center_name\",1,\"\'全国\'\" ),\"\'空\'\")} area_name, -- 区域名称汇总维度 FORMAT(sum(sales_amt_day) /10000, 2) as sales_amt ,FORMAT(count(distinct shop_id),0) as shop_cnt from ( select event_date ,manage_dist_id ,manage_dist_name ,prov_id ,prov_name ,region_id_ud ,region_name_ud ,shop_id ,shop_name ,max(sales_amt_day) as sales_amt_day from ck_ads.ads_hr_dash_board_realtime b where manage_dist_id <> \'\' and bd_id = \'${bd_id}\' and event_date = current_date() ${if(showtype_area <> 1,SWITCH(showtype_area,2,\" and b.manage_dist_id in (\'\"+replace(replace(manage_dist_id,\"，\",\",\"),\",\",\"\',\'\")+\"\')\",4,\" and concat(b.manage_dist_id,b.prov_id) in (\'\"+replace(replace(prov_id,\"，\",\",\"),\",\",\"\',\'\")+\"\')\",16,\" and b.region_id_ud in (\'\"+replace(replace(region_id,\"，\",\",\"),\",\",\"\',\'\")+\"\')\",32,\" and b.shop_id in (\'\"+replace(replace(shop_id,\"，\",\",\"),\",\",\"\',\'\")+\"\')\"),\"\")} -- 选择区域 group by event_date ,manage_dist_id ,manage_dist_name ,prov_id ,prov_name ,region_id_ud ,region_name_ud ,shop_id ,shop_name )b group by area_id ,area_name

2023-11-06 14:23