两种sql的使用方法 横着和竖着的月份查询
来源:互联网 发布:知敬畏明底线心得体会 编辑:程序博客网 时间:2024/04/30 05:02
两种sql的使用方法
SELECT distinct case when t.c=0 then 0 ELSE(t2.c-t.c)/t.c end*100 as c,'同比' as year,t.month
FROM (select 0 as c,a.m as month from(select 1 as m
UNION
select 2 as m
union
select 3 as m
union
select 4 as m
UNION
select 5 as m
UNION
select 6 as m
union
select 7 as m
UNION
select 8 as m
union
select 9 as m
union
select 10 as m
UNION
select 11 as m
UNION
select 12 as m
) a
where a.m not in
(select distinct month(buildDate) from tm_case where YEAR(buildDate)=2015 )) t
LEFT JOIN (select count(*) as c,month(buildDate) as month from tm_case where YEAR(buildDate)=2016 group by month(buildDate)) t2
ON t.month=t2.month) a
select s.caseProperty,d.ddValue,
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '01' then '1' else 0 end) as '1月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '02' then '1' else 0 end) as '2月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '03' then '1' else 0 end) as '3月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '04' then '1' else 0 end) as '4月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '05' then '1' else 0 end) as '5月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '06' then '1' else 0 end) as '6月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '07' then '1' else 0 end) as '7月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '08' then '1' else 0 end) as '8月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '09' then '1' else 0 end) as '9月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '10' then '1' else 0 end) as '10月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '11' then '1' else 0 end) as '11月',
sum(case when SUBSTRING(S.buildDate, 6 ,2) = '12' then '1' else 0 end) as '12月'
from tm_case S LEFT JOIN pd_datadict_detail d ON s.caseProperty=d.id
WHERE SUBSTRING(s.buildDate,1,4)='2012'
GROUP BY d.ddValue;
0 0
- 两种sql的使用方法 横着和竖着的月份查询
- SQL 如何查询时竖着的数据横着显示
- SQL查询技巧 求某日期所在月份的天数
- sql中如何查询出访问量最多的月份
- 自定义竖着的SeekBar
- 竖着的Seekbar
- 竖着显示的“UIPageControl”
- 竖着的UISlider
- verticalTablayout竖着的tablayout
- 竖着的Tablayout
- SQL中case when的两种使用方法示例
- SQL中case when的两种使用方法示例
- sqlserver中如何取得两位的月份和天数
- sqlserver 显示两位的月份和天数
- 查询给定日期的月份
- Access 2003 的两种SQL 查询模式:ANSI-89 和 ANSI-92
- 常用的两种查询第几行的SQL语句
- HTMLParser的两种使用方法
- ios3 navigationbar 遮挡问题
- Date转换详解
- jquery-ajax-$post方法
- mybatis:Invalid bound statement (not found)
- T语言与快速开发平台之天宇联java开发平台开发指南
- 两种sql的使用方法 横着和竖着的月份查询
- java进阶--面试的知识点
- iOS 国际化的设置大全
- 常用JS正则表达式
- PHP浮点数的一个常见问题的解答
- 指定Webbrowser控件所用IE内核版本
- SQL 错误: ORA-00972: 标识符过长
- mysql更改密码
- ListView的优化。contentView的复用和ViewHolder的使用