首页 > 代码库 > 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
声明:以上内容来自用户投稿及互联网公开渠道收集整理发布,本网站不拥有所有权,未作人工编辑处理,也不承担相关法律责任,若内容有误或涉及侵权可进行投诉: 投诉/举报 工作人员会在5个工作日内联系你,一经查实,本站将立刻删除涉嫌侵权内容。