sql 整理(二)

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

分享到：

sql 整理(三) | sql 整理(一)

• 2007-06-05 11:10
• 浏览 1381
• 评论(0)
• 查看更多

相关推荐

sql语句整理工具

标题中的“sql语句整理工具”指的是用于优化和格式化SQL查询语句的软件或应用程序。这类工具的主要目的是帮助数据库管理员和开发人员更好地管理和理解复杂的SQL代码，使其更规范、可读性更强，从而提高工作效率。 ...

SQL语句格式整理器

SQL语句格式整理器是一款非常实用的工具，它专门针对SQL查询语句的排版混乱问题，通过自动化处理，帮助用户快速将杂乱无章的SQL代码转化为整洁、易读的格式。这样的工具在数据库管理和开发过程中尤为重要，因为它...

sql跟踪语句整理工具

SQL跟踪后，用该工具能够快速整理出整洁的SQL，减少人工的复制、帖贴

SQL文自动整理格式工具

sqllice是一款专为解决SQL代码格式混乱问题而设计的工具，它能够帮助开发人员快速、高效地整理SQL脚本，使其更符合编程规范，提高代码可读性和团队协作效率。无论你是使用Java、Python、C#还是其他语言进行开发，...

Sql数据库索引碎片整理脚本

SqlServer索引碎片整理脚本，提据库查询效率，很有用。

oracle常用sql整理

本文将基于"oracle常用sql整理"的主题，深入探讨Oracle SQL的一些核心概念、语句及其实用技巧，适合初级到中级水平的学习者。 一、SQL基础 SQL是标准化的查询语言，分为DDL（Data Definition Language）、DML（Data...

自制sql文整理

自制SQL文整理，非开源，非完整，写得很挫，有使用时限，强烈不建议下载

SQLSERVER基础实用必会SQL语句整理.docx

二、SQLSERVER数据库的备份和还原 备份SQL数据库的命令是：backup database [数据库名] to disk='备份文件路径' with name='备份文件名'。例如：backup database [3D-DATA] to disk='D:\Program Files\Microsoft ...

SQL格式化整理

SQL格式整理工具，而且还可以把SQL语句转换成VB DELPHI C# JAVA里面用的格式

《SQL》语句整理

在《SQL》语句整理中，我们涉及了数据库的创建与删除、表的创建与数据操作、查询语句以及聚合函数等核心概念。 首先，创建数据库是数据库管理的基础。在SQL Server 2005中，可以使用`CREATE DATABASE`语句来创建新...

sql语句整理

sql语句整理 sql增删改查 更新数据记录 删除数据记录

SQL数据库整理工具

标题中的“SQL数据库整理工具”指的是专门用于管理和优化SQL Server数据库的应用程序，这些工具通常包含一系列功能，旨在提升数据库性能，确保数据的准确性和一致性。SQL Server是Microsoft开发的一款关系型数据库...

DBA日常维护SQL整理

DBA日常维护SQL整理,涵盖操作系统、存储、数据库各个层面

SQL面试经典版-整理篇

在SQL面试中，掌握核心概念和技术至关重要。这里我们将深入探讨SQL、Oracle、MySQL以及SQL Server等数据库相关的知识点，这些内容都是面试中常见的考察点。 1. SQL基础： - 数据库概念：理解关系型数据库的基本...

刚刚整理的SQL语句

尽管描述和部分内容提供的信息有限，但我们可以基于标题“刚刚整理的SQL语句”来展开一些重要的SQL知识点，帮助读者更好地理解和掌握SQL的基础及进阶用法。 ### SQL简介 SQL（Structured Query Language）是一种...

Sql数据库知识整理

这个压缩包“SQL整理”很可能包含了关于SQL的各种概念、语法、操作和最佳实践的详细资料。以下是一些可能涵盖在其中的重要知识点： 1. **SQL基本概念**：SQL分为DDL（Data Definition Language，数据定义语言）、...

SQL Server面试常用sql整理

SQL Server 面试常用 SQL 整理 在 SQL Server 面试中，数据库部分的知识是非常重要的，本文将整理一些常用的 SQL 查询语句，以便更好地应对面试。 数据库设计 在创建数据库时，需要考虑到数据库的设计，包括表...

SQL Server 2005索引碎片整理

SQL Server 2005索引碎片整理

一般SQL语句优化整理

### 一般SQL语句优化整理 #### 概述 在数据库操作中，SQL查询语句是数据检索的核心，其性能直接影响到应用系统的响应时间和资源消耗。对于一般的SQL语句优化，不仅能够提升查询效率，还能改善用户体验。本文将对...

• 数据库设计3NF

  2007-08-12 17:47 2782
  数据库的设计范式是数据库设计所需要满足的规范，满足这些规范的数 ...

• jdbc事务处理

  2007-08-08 23:44 12047
  在JDBC的数据库操作中，一项事务是由一条或是多条表达式所组成 ...

• SQL SERVER 数据库无法连接

  2007-07-07 19:53 2730
      本来运行一切正常的程序，换了一台服务器，一连报错： 2 ...

• SQL Server安装:以前的某个程序安装已在安装计算机上创建挂起的文件操作

  2007-07-06 14:20 1803
  打开注册表编辑器，在HKEY_LOCAL_MACHINE\SY ...

• sql 整理(三)

  2007-06-05 11:16 1203
  sql 代码 select * from ( s ...

• sql 整理(一)

  2007-06-05 10:59 1122
  1、***************************** ...

Global site tag (gtag.js) - Google Analytics