SQL 查询 之 union ,case ,group

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 ;