首页 > 代码库 > SQL 查询 之 union ,case ,group

SQL 查询 之 union ,case ,group

 

union all
use myDBSELECT 大于20 as scope,count(*) as 人数FROM student where age>=60union 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 studentselect 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 ; 

SQL 查询 之 union ,case ,group