1.所用到的函数
(1)CONCAT(a,b)——>拼接字符串
(2)nvl(a,0)——>a为空赋值为空
(3)select tt.rn, tt.*
from(select t.*, rownum rn from tableName t) tt——>记录在数据库中是第几行
(4)to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')——>获取当前年的的1月1号日期格式
(5)sign(a-b)结果值情况:a>b——>1 a<b——>-1 a=b——>0
(6)DECODE(value,if1,then1,if2,then2,if3,then3,...,else),value等于if1,则返回then1,if都不满足则返回else值
(7)select to_char(date,'iw'),sum()
from tablename
group by to_char(date,'iw') ——>按自然周统计
(8)select to_char(date,'mm'),sum()
from tablename
group by to_char(date,'mm') ——>按自然月统计
(9)select to_char(date,'q'),sum()
from tablename
group by to_char(date,'q') ——>按自然季统计
(10)TO_CHAR (to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd')-1 , 'WW')——当年的总周数
(11)FLOOR(date1,date2)——>获得两时间相差天数
(12)mapper的大于和小于号的表示
大于:> 小于:< 大于等于:>= 小于等于:< =
(13)两个不同查询结果合并
select id id,sum(num) num
from
(select a.STATIONID id,
count(1) num
from YPT_CGUESTER a
group by a.STATIONID
union
select a.STATIONID id,
count(1) num
from YPT_EGUESTER a
group by a.STATIONID
)
group by id
select id id,sum(money) money,sum(num) num
from
(select a.STATIONID id,
sum(b.MONEY)
money,
count(1) num
from YPT_CGUESTER a
LEFT JOIN ROOM_MONEY b
on (a.STATIONID=b.STATIONID and a.DJ_RUZHUFANGHAO=b.ROOMNUMBER)
where a.STATIONID= '5101220047'
group by a.STATIONID
union
select a.STATIONID id,
sum(b.MONEY)
money,
count(1) num
from YPT_EGUESTER a
LEFT JOIN ROOM_MONEY b
on (a.STATIONID=b.STATIONID and a.F_ROOM=b.ROOMNUMBER)
where a.STATIONID= '5101220047'
group by a.STATIONID
)
group by id
2.按当年季,月,周,统计mapper文件如下
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.taxsearch.dao.StatisticsMapper">
<!-- 查询季收入 -->
<select id="selectSeason" resultType="HashMap">
select CONCAT(tt.rn,'季') season,nvl(z.season_money,0) season_money
from(select t.*, rownum rn from YPT_CGUESTER t) tt
LEFT JOIN
(select to_char(DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN),'q') season,
sum((DECODE(FLOOR(DECODE(a.DJ_TUIFANGSHIJIAN,null,to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01'), 'yyyy-mm-dd'),a.DJ_TUIFANGSHIJIAN)
-DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN))
,0,1,FLOOR(DECODE(a.DJ_TUIFANGSHIJIAN,null,to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01'), 'yyyy-mm-dd'),a.DJ_TUIFANGSHIJIAN)
-DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN))))*b.MONEY)
season_money
from YPT_CGUESTER a
LEFT JOIN ROOM_MONEY b
on (a.DJ_LVGUANDAIMA=b.STATIONID and a.DJ_RUZHUFANGHAO=b.ROOMNUMBER)
where a.DJ_LVGUANDAIMA= #{DJ_LVGUANDAIMA,jdbcType=VARCHAR}
and ((a.DJ_RUZHUSHIJIAN>=to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01') , 'yyyy-mm-dd')
and a.DJ_RUZHUSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd'))
or (a.DJ_RUZHUSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd') and a.DJ_TUIFANGSHIJIAN is null))
or (a.DJ_RUZHUSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd') and a.DJ_TUIFANGSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd'))
group by to_char(DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN),'q')
ORDER BY season) z
on tt.rn=z.season
where tt.rn<=4
</select>
<!-- 查询月收入 -->
<select id="selectMonth" resultType="HashMap">
select CONCAT(tt.rn,'月') month,nvl(z.month_money,0) month_money
from(select t.*, rownum rn from YPT_CGUESTER t) tt
LEFT JOIN
(select to_char(DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN),'mm') month,
sum((DECODE(FLOOR(DECODE(a.DJ_TUIFANGSHIJIAN,null,to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01'), 'yyyy-mm-dd'),a.DJ_TUIFANGSHIJIAN)
-DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN))
,0,1,FLOOR(DECODE(a.DJ_TUIFANGSHIJIAN,null,to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01'), 'yyyy-mm-dd'),a.DJ_TUIFANGSHIJIAN)
-DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN))))*b.MONEY)
month_money
from YPT_CGUESTER a
LEFT JOIN ROOM_MONEY b
on (a.DJ_LVGUANDAIMA=b.STATIONID and a.DJ_RUZHUFANGHAO=b.ROOMNUMBER)
where a.DJ_LVGUANDAIMA= #{DJ_LVGUANDAIMA,jdbcType=VARCHAR}
and ((a.DJ_RUZHUSHIJIAN>=to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01') , 'yyyy-mm-dd')
and a.DJ_RUZHUSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd'))
or (a.DJ_RUZHUSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd') and a.DJ_TUIFANGSHIJIAN is null))
or (a.DJ_RUZHUSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd') and a.DJ_TUIFANGSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd'))
group by to_char(DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN),'mm')
ORDER BY month) z
on tt.rn=z.month
where tt.rn<=12
</select>
<!-- 查询周收入 -->
<select id="selectWeek" resultType="HashMap">
select CONCAT(tt.rn,'周') week,nvl(z.week_money,0) week_money
from(select t.*, rownum rn, TO_CHAR (to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd')-1 , 'WW') total from YPT_CGUESTER t) tt
LEFT JOIN
(select to_char(DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN),'iw') week,
sum((DECODE(FLOOR(DECODE(a.DJ_TUIFANGSHIJIAN,null,to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01'), 'yyyy-mm-dd'),a.DJ_TUIFANGSHIJIAN)
-DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN))
,0,1,FLOOR(DECODE(a.DJ_TUIFANGSHIJIAN,null,to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01'), 'yyyy-mm-dd'),a.DJ_TUIFANGSHIJIAN)
-DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN))))*b.MONEY)
week_money
from YPT_CGUESTER a
LEFT JOIN ROOM_MONEY b
on (a.DJ_LVGUANDAIMA=b.STATIONID and a.DJ_RUZHUFANGHAO=b.ROOMNUMBER)
where a.DJ_LVGUANDAIMA= #{DJ_LVGUANDAIMA,jdbcType=VARCHAR}
and ((a.DJ_RUZHUSHIJIAN>=to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01') , 'yyyy-mm-dd')
and a.DJ_RUZHUSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd'))
or (a.DJ_RUZHUSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd') and a.DJ_TUIFANGSHIJIAN is null))
or (a.DJ_RUZHUSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd') and a.DJ_TUIFANGSHIJIAN<to_date(CONCAT(to_char(sysdate,'yyyy')+1,'-01-01') , 'yyyy-mm-dd'))
group by to_char(DECODE(sign(to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd')-a.DJ_RUZHUSHIJIAN),1,to_date(CONCAT(to_char(sysdate,'yyyy'),'-01-01'), 'yyyy-mm-dd'),a.DJ_RUZHUSHIJIAN),'iw')
ORDER BY week) z
on tt.rn=z.week
where tt.rn<=tt.total
</select>
</mapper>