先展示一条求最大检修容量的sql:
select sum(maxcapacity) as dayjxrl , pdate from
(
select PP.equid,PP.equname,QQ.pdate,PP.startdate,PP.enddate,PP.maxcapacity from
(
select AA.equid,AA.equname,AA.startdate,AA.enddate,BB.maxcapacity from
(
select * from t_rep_out_yhjg where pdate='2017-01-01' and planid = '1007' and datetype='M' and equtype='JZ'
) AA,
(
select t2.emsname,t1.maxcapacity from
(select unitid,maxcapacity from t_bas_unit) t1,
(select unitid,emsname from t_bas_unit_info) t2
where t1.unitid=t2.unitid and t2.emsname is not null and t2.emsname<>'null'
) BB
where AA.equid=BB.emsname
) PP,
(
select to_char(to_date('2017-01-01','yyyy-mm-dd')+ level-1,'yyyy-mm-dd') as pdate
from dual connect by level <= 31
) QQ
where QQ.pdate>=PP.startdate and QQ.pdate<=PP.enddate
) group by pdate
order by dayjxrl desc,pdate asc
其中:
select to_char(to_date('2017-01-01','yyyy-mm-dd')+ level-1,'yyyy-mm-dd') as pdate
from dual connect by level <= 31
我们来看一下它的执行效果:
将level后面的数字变成365,就会得到该年的sql表。这个在程序中传入一个当月的days,就可以广泛应用于各种各样和日期有关的场景。
突然想起以前老师让写的小程序其实还是很有用的。。。
/**
* 判断是否润年
*
* @param strYear String
* @return boolean
*/
public static boolean isLeapYear(String strYear) {
int Year = Integer.parseInt(strYear);
if ((Year % 400) == 0 || ((Year % 4 == 0) && (Year % 100 != 0))) {
return true;
} else {
return false;
}
}
/**
* 得到某月的天数
*
* @param strYear String
* @return boolean
*/
public static int getDaysOfMonth(int year, int month) {
int days = 0;
switch (month) {
case 1:
days = 31;
break;
case 2:
if (isLeapYear(String.valueOf(year))) {
days = 29;
} else {
days = 28;
}
break;
case 3:
days = 31;
break;
case 4:
days = 30;
break;
case 5:
days = 31;
break;
case 6:
days = 30;
break;
case 7:
days = 31;
break;
case 8:
days = 31;
break;
case 9:
days = 30;
break;
case 10:
days = 31;
break;
case 11:
days = 30;
break;
case 12:
days = 31;
break;
default:
break;
}
return days;
}
/**
* 得到某季度的天数
*
* @param strYear String
* @return boolean
*/
public static int getDaysOfQuarter(int year, int quarter) {
int days = 0;
switch (quarter) {
case 1:
if (isLeapYear(String.valueOf(year))) {
days = 91;
} else {
days = 90;
}
break;
case 2:
days = 91;
break;
case 3:
days = 92;
break;
case 4:
days = 92;
break;
default:
break;
}
return days;
}