union all
use myDB
SELECT '大于20' as scope,count(*) as 人数
FROM student where age>=60
union all
SELECT '小于20' as scope ,count(*) as 人数
FROM student where age<60
select sum(case when age >=18 and age <19 then 1 else 0 end) as f_cnt,
sum(case when age >=19 and age <20 then 1 else 0 end) as s_cnt,
sum(case when age >=20 then 1 else 0 end) as e_cnt from student
select sum(case when age >=18 and age <19 then age else 0 end) as f_cnt,
sum(case when age >=19 and age <20 then age else 0 end) as s_cnt,
sum(case when age >=20 then age else 0 end) as e_cnt from student
/*
时间限定：2010－03－01到2010－05－01
客户代码
客户名称
期初销售数量(2010－03－01之前销售数)
期初销售金额(2010－03－01之前销售金额)
本期销售数量
本期销售金额
期末销售数量(2010－05－01之前销售数)
期末销售金额(2010－05－01之前销售数)
*/
use BSERP_ZB;
select KEHU.KHDM as 客户代码, KEHU.KHMC as 客户名称 ,
SUM(case when RQ < '2013-05-01' then SL else 0 end) as 期初销售数量,
SUM(case when RQ < '2013-05-01' then sl*DJ else 0 end)as 期初销售金额 ,
SUM(case when RQ between '2013-05-01' and '2013-07-01' then SL else 0 end) as 本期销售数量,
SUM(case when RQ between '2013-05-01' and '2013-07-01' then sl*DJ else 0 end)as 本期销售金额,
SUM(case when RQ < '2013-07-01' then SL else 0 end) as 期末销售数量,
SUM(case when RQ <'2013-07-01' then sl*DJ else 0 end)as 期末销售金额
from VW_PFXHMX ,KEHU,SHANGPIN where
VW_PFXHMX.DM1= KEHU.KHDM and VW_PFXHMX.SPDM=SHANGPIN.SPDM group by KEHU.KHDM ,KEHU.KHMC;
/*
客户订单执行情况分析
客户代码
客户名称
计划订单数 VW_PFJRDMX.SL
终止订单数 VW_PFJRDMX.SL_3
有效订单数(订单数-停用数)
发货数 VW_PFXHMX.SL
欠货数(发货数-有效订单数)
*/
select KEHU.KHDM as 客户代码, KEHU.KHMC as 客户名称,sum(VW_PFJRDMX.SL) as 计划订单数,sum(SL_3) as 终止订单数,SUM(VW_PFJRDMX.SL-SL_3) as 有效订单数,
sum(VW_PFXHMX.SL ) as 发货数 ,sum(VW_PFXHMX.SL-(VW_PFJRDMX.SL-SL_3)) as 欠货数 from VW_PFJRDMX,VW_PFXHMX ,KEHU where KEHU.KHDM=VW_PFJRDMX.DM1 and VW_PFJRDMX.SPDM=VW_PFXHMX.SPDM group by KEHU.KHDM , KEHU.KHMC ;
/*
批发对比统计
本期时间限定：2010－05－01到2010－05－31
上期时间限定：2010－04－01到2010－04－30
商品名称
商品代码
前期发货数
前期发货金额
本期发货数
本期发货金额
增长量:(本期发货金额-前期发货金额)
增长率:(本期发货金额-前期发货金额)*100/前期发货金额
*/
select SHANGPIN.SPDM as 商品代码,SHANGPIN.SPMC as 商品名称,
SUM(case when RQ between '2013-04-01' and '2013-04-30' then VW_PFXHMX.SL else 0 end) as 前期发货数,
SUM(case when RQ between '2013-04-01' and '2013-04-30' then VW_PFXHMX.SL *DJ else 0 end) as 前期发货金额,
SUM(case when RQ between '2013-05-01' and '2013-05-31' then VW_PFXHMX.SL else 0 end) as 本期发货数,
SUM(case when RQ between '2013-05-01' and '2013-05-31' then VW_PFXHMX.SL *DJ else 0 end) as 本期发货金额
from VW_PFXHMX ,SHANGPIN where
VW_PFXHMX.SPDM=SHANGPIN.SPDM group by SHANGPIN.SPDM,SHANGPIN.SPMC ;
