connect by level 的小应用

先展示一条求最大检修容量的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;
    }