--如:tableA(id,date1,productname,mount)select productname,sum(mount) from tableA where to_char(date1,'yyyy-mm')>='2008-01' and to_char(date1,'yyyy-mm')<='2008-06' group by productname union (select productname,sum(mount) from tableA where to_char(date1,'yyyy-mm')>='2008-07' and to_char(date1,'yyyy-mm')<='2008-12' group by productname );
select decode(substr(to_char(round(日期字段,'year'),'yyyymmdd'),1,4),2008,'上半年',2009,'下半年') as 年度, count(*) from tablename where substr(to_char(日期字段,'yyyymmdd'),1,4)='2008' group by substr(to_char(round(sysdate,'year'),'yyyymmdd'),1,4)
select rq,count(*) from test where rq between to_date('2008-1-01','yyyy-mm-dd hh24:mi:ss') and to_date('2008-12-31','yyyy-mm-dd hh24:mi:ss') group by case when trunc(rq,'mm')<=6 then 1 else 0 end);
select decode(substr(to_char(round(日期字段,'year'),'yyyymmdd'),1,4),'2008','上半年','2009','下半年') as 年度, count(*) from tablename where substr(to_char(日期字段,'yyyymmdd'),1,4)='2008' group by substr(to_char(round(日期字段,'year'),'yyyymmdd'),1,4)
-- 统计上下半年的记录数 select rq,count(*) from test where rq between to_date('2008-1-01','yyyy-mm-dd hh24:mi:ss') and to_date('2008-12-31','yyyy-mm-dd hh24:mi:ss') group by (case when trunc(rq,'mm')<=6 then 1 else 0 end);
只能把上半年的一个结果和下半年的结果合并, 用union 连接合并结果 select '上半年' 上半年 ,sum(num) total from tb where to_char(日期,'YYYYMM') between '200801' and '200806' union select '下半年' 下半年 ,sum(num) total from tb where to_char(日期,'YYYYMM') between '200807' and '200812'
SELECT decode(to_char(date_column,'mm'),'1','上半年','2','上半年','3','上半年','4','上半年','5','上半年','6','上半年','下半年') FROM table_name WHERE date_column >= '2008-1-1' --根据需要调整为适合你自己的时间格式 AND date_column < '2009-1-1' --根据需要调整为适合你自己的时间格式 GROUP BY DECODE(TO_CHAR(date_column,'mm'),'1','上半年','2','上半年','3','上半年','4','上半年','5','上半年','6','上半年','下半年');
-- 对不起,再来一个SQL> create table test (rq date,sales int);Table createdSQL> select * from test;RQ SALES ----------- --------------------------------------- 2008-1-1 12 2008-11-5 23 2008-11-18 44 2008-6-9 32 2007-11-1 22SQL> select case when to_char(rq,'mm')<=6 then '上半年' else '下半年' end 时间, 2 sum(sales) 销售额 3 from test 4 where rq between to_date('2008-1-01','yyyy-mm-dd hh24:mi:ss') 5 and to_date('2008-12-31','yyyy-mm-dd hh24:mi:ss') 6 group by case when to_char(rq,'mm')<=6 then '上半年' else '下半年' end;时间 销售额 ------ ---------- 下半年 67 上半年 44SQL>
SELECT CASE WHEN TO_CHAR(date_column,'mm')<=6 THEN'上半年' ELSE'下半年' END CASE 时间 FROM table_name WHERE date_column >= '2008-1-1' --根据需要调整为适合你自己的时间格式 AND date_column < '2009-1-1' --根据需要调整为适合你自己的时间格式 GROUP BY CASE WHEN TO_CHAR(date_column,'mm')<=6 THEN'上半年' ELSE'下半年' END CASE;
select count(dd) cu from aaa group by round(dd, 'year') order by 1 加‘上半年’和‘下半年’ select decode(rownum, 1, '上半年', '下半年') year, cu from (select count(dd) cu from aaa group by round(dd, 'year') order by 1)
where to_char(date1,'yyyy-mm')>='2008-01' and
to_char(date1,'yyyy-mm')<='2008-06'
group by productname
union (select productname,sum(mount) from tableA
where to_char(date1,'yyyy-mm')>='2008-07' and
to_char(date1,'yyyy-mm')<='2008-12'
group by productname
);
where substr(to_char(日期字段,'yyyymmdd'),1,4)='2008'
group by substr(to_char(round(sysdate,'year'),'yyyymmdd'),1,4)
from test
where rq between to_date('2008-1-01','yyyy-mm-dd hh24:mi:ss')
and to_date('2008-12-31','yyyy-mm-dd hh24:mi:ss')
group by case when trunc(rq,'mm')<=6 then 1 else 0 end);
where substr(to_char(日期字段,'yyyymmdd'),1,4)='2008'
group by substr(to_char(round(日期字段,'year'),'yyyymmdd'),1,4)
select rq,count(*)
from test
where rq between to_date('2008-1-01','yyyy-mm-dd hh24:mi:ss')
and to_date('2008-12-31','yyyy-mm-dd hh24:mi:ss')
group by (case when trunc(rq,'mm')<=6 then 1 else 0 end);
用union 连接合并结果
select '上半年' 上半年 ,sum(num) total from tb where to_char(日期,'YYYYMM') between '200801' and '200806'
union
select '下半年' 下半年 ,sum(num) total from tb where to_char(日期,'YYYYMM') between '200807' and '200812'
FROM table_name
WHERE date_column >= '2008-1-1' --根据需要调整为适合你自己的时间格式
AND date_column < '2009-1-1' --根据需要调整为适合你自己的时间格式
GROUP BY DECODE(TO_CHAR(date_column,'mm'),'1','上半年','2','上半年','3','上半年','4','上半年','5','上半年','6','上半年','下半年');
----------- ---------------------------------------
2008-1-1 12
2008-11-5 23
2008-11-18 44
2008-6-9 32
2007-11-1 22SQL> select case when to_char(rq,'mm')<=6 then '上半年' else '下半年' end 时间,
2 sum(sales) 销售额
3 from test
4 where rq between to_date('2008-1-01','yyyy-mm-dd hh24:mi:ss')
5 and to_date('2008-12-31','yyyy-mm-dd hh24:mi:ss')
6 group by case when to_char(rq,'mm')<=6 then '上半年' else '下半年' end;时间 销售额
------ ----------
下半年 67
上半年 44SQL>
FROM table_name
WHERE date_column >= '2008-1-1' --根据需要调整为适合你自己的时间格式
AND date_column < '2009-1-1' --根据需要调整为适合你自己的时间格式
GROUP BY CASE WHEN TO_CHAR(date_column,'mm')<=6 THEN'上半年' ELSE'下半年' END CASE;
加‘上半年’和‘下半年’
select decode(rownum, 1, '上半年', '下半年') year, cu
from (select count(dd) cu from aaa group by round(dd, 'year') order by 1)