sql 整理(二)

最新推荐文章于 2025-01-02 18:15:00 发布

最新推荐文章于 2025-01-02 18:15:00 发布 · 169 阅读

文章标签：

#SQL #C #C++ #C# #CVS

DATABASE 专栏收录该内容

7 篇文章

订阅专栏

本文提供了多个SQL查询示例，包括按产品统计不同类型的门店数量、计算用户活动指标等。通过这些案例，展示了如何使用CASE语句进行条件判断及聚合函数进行数据汇总。

sql 代码

1、*************************************************************************************************************

sql 代码

select b.id,b.prod_name,sum(aa) as w1,sum(bb) as w2,sum(cc) as w3,sum(dd) as w4,sum(ee) as w5
from (
select a.id,a.prod_name,
case a.iid when 1 then storeCount1 else 0 end as aa,
case a.iid when 2 then storeCount1 else 0 end as bb,
case a.iid when 3 then storeCount1 else 0 end as cc,
case a.iid when 4 then storeCount1 else 0 end as dd,
case a.iid when 5 then storeCount1 else 0 end as ee
from (
SELECT p.id,i.id as iid,p.prod_name,count(s.id) as storeCount1
FROM BIZ_PROD_DISTRIBUTION d inner join
BIZ_PROD_DISTRIBUTION_ITEM di on d.id=di.main_id inner join
dim_store s on s.id=d.store_id inner join
dim_dict_item i on i.id=s.store_type inner join
dim_product p on p.id=di.prod_id
where d.cate_type='1' and s.is_closed='1'
group by p.prod_name,i.id,i.item_name,p.id) a
) b
group by b.id,b.prod_name

2、*************************************************************************************************************

sql 代码

select usercount,uploaduser,storenum,callstore,plancallstore,callstore/(case when plancallstore = 0 then 1 else plancallstore end) as plancallrate,trainNum,storeCheckNum,priceTrack,priceTrack2 from
(
select count(*) as usercount from dim_mobi_user u
inner join dim_employee e on u.employee_id = e.id
inner join dim_org s on e.org_id = s.id
where u.usage_flag='1' and e.usage_flag='1' and s.usage_flag='1'
$P!{queryStringOrg}
) aa,
-----------------
(
select count(*) as uploaduser from
(
select upload_by as upuser from biz_placement p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.upload_by as upuser from biz_storecheck_price p
inner join dim_store s on p.store_id = s.id
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.upload_by as upuser from biz_prod_distribution p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.upload_by as upuser from biz_prod_price p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.upload_by as upuser from biz_storecheck_instore p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.upload_by as upuser from biz_store_stop p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.upload_by as upuser from biz_train_data p
inner join dim_employee e on p.TRAINER_ID = e.id
inner join dim_org s on e.org_id = s.id
where p.usage_flag='1' and e.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringOrg}
)b
)bb,
(
select count(*) as storenum from dim_store u where u.usage_flag='1'
--$P!{queryStringUpdate}
$P!{queryStore}
)cc,
(
select cast(count(*) as float) as callstore from
(
select p.store_id as callstore from biz_placement p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.store_id as callstore from biz_storecheck_price p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.store_id as callstore from biz_prod_distribution p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.store_id as callstore from biz_prod_price p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
union
select p.store_id as callstore from biz_storecheck_instore p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
)c
)dd,
(
select cast(count(up.store_id) as float) as plancallstore from call_plan_item up
inner join biz_call_plan u on up.plan_id = u.id
inner join dim_store s on up.store_id = s.id
and u.usage_flag='1' and u.usage_flag='1' and s.usage_flag='1'
$P!{queryStringCallplan}
$P!{queryStringStore}
)ee
,
(
select count(*) as trainNum from biz_train_data p
inner join dim_employee e on p.TRAINER_ID = e.id
inner join dim_org s on e.org_id = s.id
where p.usage_flag='1' and e.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringOrg}
)ff,
(
select count(p.store_id) as storeCheckNum from BIZ_STORECHECK_INSTORE p
inner join dim_store s on p.store_id = s.id
where p.usage_flag='1' and s.usage_flag='1'
$P!{queryStringUpload}
$P!{queryStringStore}
)jj,
(
select count(*) as priceTrack from DIM_PRICE_TRACK k where k.usage_flag='1'
and k.CHNL_TYPE = '11'
)hh,
(
select count(*) as priceTrack2 from DIM_PRICE_TRACK k where k.usage_flag='1'
and k.CHNL_TYPE = '12'
)ii

３、*************************************************************************************************************

sql 代码

SELECT prod.prod_name,chnl.org_name as chnl,rd.org_name as rd,
bm.org_name as bm,cm.org_name as cm,g1.geography_name as province,
g.geography_name as city,s.store_name_cn,s.store_name_en,s.store_code,
case s.store_type when '1' then '15+' WHEN '2' THEN '4-14'
WHEN '3' THEN '1-3' WHEN '4' THEN 'CVS' WHEN '5' THEN 'W/S' END AS storeType
FROM REF_PROD_STORE_TYPE_5P p inner join
REF_PROD_STORE_TYPE_5P_ITEM pi on pi.ref_id=p.id and p.usage_flag='1'
inner join dim_product prod on pi.prod_id=prod.id
inner join dim_store s on s.store_type=p.store_type and s.usage_flag='1'
left join biz_prod_distribution d on s.id=d.id and d.usage_flag='1'
left join biz_prod_distribution_item di on di.main_id=d.id and di.prod_id=prod.id
inner join dim_org chnl on s.chnl_id=chnl.id
inner join dim_org rd on s.rd_id=rd.id
inner join dim_org bm on s.BM_id=bm.id
inner join dim_org cm on s.cm_id=cm.id
inner join dim_geography g on g.id=s.geo_id
inner join dim_geography g1 ON g.parent_geography_id = g1.id
where (d.store_id is null or di.prod_id is null) and $P!{queryString}
order by prod.prod_name

4、*************************************************************************************************************

sql 代码

SELECT prod.prod_name,case s.store_type when '1' then '15+' WHEN '2' THEN '4-14'
WHEN '3' THEN '1-3' WHEN '4' THEN 'CVS' WHEN '5' THEN 'W/S' END AS storeType,count(s.id) as num
FROM REF_PROD_STORE_TYPE_5P p inner join
REF_PROD_STORE_TYPE_5P_ITEM pi on pi.ref_id=p.id and p.usage_flag='1'
inner join dim_product prod on pi.prod_id=prod.id
inner join dim_store s on s.store_type=p.store_type and s.usage_flag='1'
inner join dim_geography g on g.id=s.geo_id
inner join dim_geography g1 ON g.parent_geography_id = g1.id
left join biz_prod_distribution d on s.id=d.id and d.usage_flag='1'
left join biz_prod_distribution_item di on di.main_id=d.id and di.prod_id=prod.id
where (d.store_id is null or di.prod_id is null) and $P!{queryString}
group by prod.prod_name,s.store_type
order by prod.prod_name

5、*************************************************************************************************************

sql 代码

select sum(aa) as 'a15+',sum(bb) as 'a1-14',sum(cc) as 'a1-3',sum(dd) as 'aCVS',
sum(xx) as 'b15+',sum(yy) as 'b1-14',sum(zz) as 'b1-3',sum(uu) as 'bCVS'
from(
select
case c.storeType when 1 then c.num else 0 end as aa,
case c.storeType when 2 then c.num else 0 end as bb,
case c.storeType when 3 then c.num else 0 end as cc,
case c.storeType when 4 then c.num else 0 end as dd,
case c.storeType when 5 then c.num else 0 end as ee,
case c.storeType when 1 then c.numb else 0 end as xx,
case c.storeType when 2 then c.numb else 0 end as yy,
case c.storeType when 3 then c.numb else 0 end as zz,
case c.storeType when 4 then c.numb else 0 end as uu,
case c.storeType when 5 then c.numb else 0 end as vv
from(
select a.store_type as storeType,a.num as num,b.num as numb from (
select store_type,count(1) as num
from DIM_STORE
where USAGE_FLAG='1' and IS_CLOSED='1'
group by store_type)
a inner join
(
select s.store_type as storeType,count(distinct p.store_id) as num from
dim_store s inner join call_plan_item p on s.id=p.store_id
group by s.store_type
) b on a.store_type=b.storeType
) c
) f

6、*************************************************************************************************************

sql 代码

select t.id as id,dc.id as prodid, dc.prod_name,i.prod_price,CONVERT(varchar(10) ,t.upload_datetime, 120 ) as upload_datetime
from BIZ_PROD_PRICE t
inner join BIZ_PROD_PRICE_ITEM i on t.id = i.main_id
inner join dim_product dc on i.prod_id = dc.id
where i.main_id in
(
select top 8 id from BIZ_PROD_PRICE $P!{queryString} order by upload_datetime desc
)
order by t.upload_datetime desc