如下表table1：
日期(exportDate) 数量(amount)
-------------- -----------
14-2月 -08 20
10-3月 -08 2
14-4月 -08 6
14-6月 -08 75
24-10月-09 23
14-11月-09 45
04-8月 -10 5
04-9月 -10 44
04-10月-10 88
注意：为了显示更直观，如下查询已皆按相应分组排序
1.按年份分组
select to_char(exportDate,'yyyy'),sum(amount) from table1 group by to_char(exportDate,'yyyy');
年份 数量
-----------------------------
2009 68
2010 137
2008 103
2.按月份分组
select to_char(exportDate,'yyyy-mm'),sum(amount) from table1 group by to_char(exportDate,'yyyy-mm')
order by to_char(exportDate,'yyyy-mm');
月份 数量
-----------------------------
2008-02 20
2008-03 2
2008-04 6
2008-06 75
2009-10 23
2009-11 45
2010-08 5
2010-09 44
2010-10 88
3.按季度分组
select to_char(exportDate,'yyyy-Q'),sum(amount) from table1 group by to_char(exportDate,'yyyy-Q')
order by to_char(exportDate,'yyyy-Q');
季度 数量
------------------------------
2008-1 22
2008-2 81
2009-4 68
2010-3 49
2010-4 88
4.按周分组1
select to_char(exportDate,'yyyy-IW'),sum(amount) from table1 group by to_char(exportDate,'yyyy-IW')
order by to_char(exportDate,'yyyy-IW');
周 数量
------------------------------
2008-07 20
2008-11 2
2008-16 6
2008-24 75
2009-43 23
2009-46 45
2010-31 5
2010-35 44
2010-40 88
补充：
按季度分组还有个比较笨的方法（参考网络资源）
select to_char(exportDate,'yyyy'),
sum(decode(to_char(exportDate,'mm'),'01',amount,'02',amount,'03',amount,0)) as 第一季,
sum(decode(to_char(exportDate,'mm'),'04',amount,'05',amount,'06',amount,0)) as 第二季,
sum(decode(to_char(exportDate,'mm'),'07',amount,'08',amount,'09',amount,0)) as 第三季,
sum(decode(to_char(exportDate,'mm'),'10',amount,'11',amount,'12',amount,0)) as 第四季
from table1
group by to_char(exportDate,'yyyy');
年份 第一季 第二季 第三季 第四季
--------------------------------------------------
2009 0 0 0 68
2010 0 0 49 88
2008 22 81 0 0
// 也可以用这种方法：按周分配 统计
select sum(case when date between to_date('20110508','yyyyMMdd') and to_date('20110508','yyyyMMdd') then 1 else 0 end),
sum(case when tdh = 'B' then 1 else 0 end),
sum(case when tdh = 'C' then 1 else 0 end),
from tab
where nn = tt
SELECT TO_CHAR(SYSDATE,'YYYY'),TO_CHAR(SYSDATE,'WW') FROM DUAL;
WW 表示一年中的第几周
//按自然周统计
select to_char(date,'iw'),sum()
from
where
group by to_char(date,'iw')
9：按周统计
select to_char(datefield, 'IW ') as weeknum, count(*) as
totalnum from t_data,date_list where date_list.日期 between
'2004-01-01 ' and '2004-12-30 ' and
datefield(+)=date_list.日期 group by to_char(datefield, 'IW ')
==============================================
--日期计算，算第n周的第一天及最后一天是几号。 by keynes 2005.04.29
================================================
-- ww的算法为每年1月1日为第一周开始，date+6为每一周结尾
-- 例如20050101为第一周的第一天，而第一周的最后一天为20050101+6=20050107
-- 公式 每周第一天 ：date + 周 * 7 - 7
-- 每周最后一天：date + 周 * 7 - 1
不管怎么编排格式都会跑掉，真气人 ~><~
=========================================================================
--日期计算，算第n周的第一天及最后一天是几号。 by keynes 2005.04.29
=========================================================================
-- ww的算法为每年1月1日为第一周开始，date+6为每一周结尾
-- 例如20050101为第一周的第一天，而第一周的最后一天为20050101+6=20050107
-- 公式 每周第一天 ：date + 周 * 7 - 7
-- 每周最后一天：date + 周 * 7 - 1
-- 如果以ww格式为主，第1、17周的起迄如下
127.0.0.1:asdb:WF>select to_date('20050101','yyyymmdd') + 1*7-7,to_date('20050101','yyyymmdd') + 1*7-1 from dual;
TO_DATE(' TO_DATE('
--------- ---------
01-JAN-05 07-JAN-05
127.0.0.1:asdb:WF>select to_date('20050101','yyyymmdd') + 17*7-7,to_date('20050101','yyyymmdd') + 17*7-1 from dual;
TO_DATE(' TO_DATE('
--------- ---------
23-APR-05 29-APR-05
Elapsed: 00:00:00.00
-- 验证如下
127.0.0.1:asdb:WF>select to_char(to_date('20050422','yyyymmdd'),'ww') as weekn,to_char(to_date('20050423','yyyymmdd'),'ww') as week1,to_char(to_date('20050429','yyyymmdd'),'ww') as week2,to_char(to_date('20050430','yyyymmdd'),'ww') as weekn2 from dual;
WEEK WEEK WEEK WEEK
---- ---- ---- ----
16 17 17 18
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>
-- iw的算法为星期一至星期日算一周，且每年的第一个星期一为第一周，
-- 例如20050101为星期六，所以用iw的算法是前年的53周，而20050103之后才是第一周的开始。
-- 公式 每周第一天 ：next_day(date) + 周 * 7 - 7
-- 每周最后一天：next_day(date) + 周 * 7 - 1
-- 如果以iw格式为主，第1、17周的起迄如下
127.0.0.1:asdb:WF>select next_day(to_date('20050101','yyyymmdd'),'MONDAY')+ 1 * 7 - 7 as first_day,next_day(to_date('20050101','yyyymmdd'),'MONDAY')+ 1 * 7 - 1 as last_day from dual;
FIRST_DAY LAST_DAY
--------- ---------
03-JAN-05 09-JAN-05
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>
127.0.0.1:asdb:WF>select next_day(to_date('20050101','yyyymmdd'),'MONDAY')+ 17 * 7 - 7 as first_day,next_day(to_date('20050101','yyyymmdd'),'MONDAY')+ 17 * 7 - 1 as last_day from dual;
FIRST_DAY LAST_DAY
--------- ---------
25-APR-05 01-MAY-05
Elapsed: 00:00:00.00
127.0.0.1:asdb:WF>
-- 验证如下
127.0.0.1:asdb:WF>select to_char(to_date('20050424','yyyymmdd'),'iw') as weekn,to_char(to_date('20050425','yyyymmdd'),'iw') as week1,to_char(to_date('20050501','yyyymmdd'),'iw') as week2,to_char(to_date('20050502','yyyymmdd'),'iw') as weekn2 from dual;
WEEK WEEK WEEK WEEK
---- ---- ---- ----
16 17 17 18
Elapsed: 00:00:00.00
其它：
--== 查今天是 "本月" 的第几周
SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS "weekOfMon" from dual;
或
SELECT TO_CHAR(SYSDATE,'W') AS "weekOfMon" from dual;
--== 查今天是 "今年" 的第几周
select to_char(sysdate,'ww') from dual;
或
select to_char(sysdate,'iw') from dual;
附注：
上文所提之iw及ww格式在doc内解释如下
IW = Week of year (1-52 or 1-53) based on the ISO standard
WW = Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
参考文件：
Format Models：http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/sql_elements4a.htm#34512
=========================================================================
// 这个是我在做BMP的项目时的对用户的注册的信息按月进行统计，有的用户可以注册多次，就是在同一个网站上注册多次和注册一次进行比//较如下：
bcr_first_registration = 'Y' 表示用户第一次注册
SELECT to_char(bcr_updated_date, 'yyyymm')
,SUM(CASE
WHEN bcr_first_registration = 'Y'
AND to_char(bcr_updated_date, 'yyyymm') BETWEEN '201106' AND
'201107' THEN
1
ELSE
0
END) AS s1
,SUM(CASE
WHEN to_char(bcr_updated_date, 'yyyymm') BETWEEN
'201106' AND
'201107' THEN
1
ELSE
0
END) AS s2
FROM bmp_customer_registration
GROUP BY to_char(bcr_updated_date, 'yyyymm')
--order by to_char(bcr_updated_date, 'yyyymm') desc;
// 这个在
--BMP的项目时统计当前时间前几周的用户可以注册信息。就是在同一个网站上注册多次和注册一次进行比
//较如下：
//
SELECT ((next_day(to_date('20110701', 'yyyymmdd'), 'SUNDAY') - 2 * 7 - 7) ||' - ' || (next_day(to_date('20110701', 'yyyymmdd'), 'SUNDAY') - 2 * 7 - 1)) AS date_range2
,SUM(CASE WHEN cr.bcr_first_registration = 'Y'
AND to_char(cr.bcr_updated_date, 'yyyymmdd') BETWEEN
to_char(next_day(to_date('20110701', 'yyyymmdd'), 'SUNDAY') - 2 * 7 - 7, 'yyyymmdd') AND
to_char(next_day(to_date('20110701', 'yyyymmdd'), 'SUNDAY') - 2 * 7 - 1, 'yyyymmdd') THEN 1 ELSE 0 END) AS S1
,SUM(CASE WHEN to_char(cr.bcr_updated_date, 'yyyymmdd') BETWEEN '20110612' AND '20110618' THEN 1 ELSE 0 END) AS s2
FROM bmp_customer_registration cr
GROUP BY to_char(cr.bcr_updated_date, 'yyyymmdd-iw')
order by date_range2 desc;
---/ 如果要按月统计(2)/
----- 如果是传的参数的是字符串'20110506'要把它转化成yyyymm这种格式
------如果是传的参数是'201106' (yyyymm) 就直接比较
SELECT to_char(bcr_updated_date, 'yyyymm') as d1
,SUM(CASE WHEN bcr_first_registration = 'Y'
AND to_char(bcr_updated_date,'yyyymm') BETWEEN to_char(to_date('20110610', 'yyyymmdd'),'yyyymm') AND to_char(SYSDATE ,'yyyymm') THEN
1
ELSE
0
END) AS S1
,SUM(CASE WHEN to_char(bcr_updated_date,'yyyymm') BETWEEN
to_char(to_date('20110610', 'yyyymmdd'),'yyyymm') AND
to_char(SYSDATE ,'yyyymm') THEN
1
ELSE
0
END) AS S2
FROM bmp_customer_registration
GROUP BY to_char(bcr_updated_date, 'yyyymm')
order by d1;
---/ 如果要按周统计(3)/
-- 是当前时间的上一个完整的周 我把周日到周六看成一个完成的。
--- 如下：当前日期的前四周
SELECT to_char(t.date_in_week - 7, 'dd/MM/yyyy') || ' - ' ||
to_char(t.date_in_week-1, 'dd/MM/yyyy') AS time_slot
,t.date_in_week - 7 AS sunday
,t.date_in_week-1 AS Saturday
FROM (SELECT trunc(SYSDATE - 4 * 7, 'iw') + rownum - 1 AS date_in_week
FROM all_objects
WHERE rownum <= (4 * 7)) t
WHERE to_char(t.date_in_week, 'd') = 1
-------------------------
---/ 如果要按周统计(4)/
select to_char((trunc(to_date('20110701','yyyymmdd'), 'DAY') - 1*7 ),'yyyy-mm-dd') as prdate1
, to_char((trunc(to_date('20110701','yyyymmdd'), 'DAY') - 1*7+6 ),'yyyy-mm-dd') as prdate2
FROM dual;
select trunc(SYSDATE, 'DAY') - 2*7 from dual;
---/ 如果要按周统计(5)/
-- 是当前时间的上一个完整的周 我把周一到周
日
看成一个完成的。
SELECT to_char(t.date_in_week - 6, 'dd/MM/yyyy') || ' - ' ||
to_char(t.date_in_week, 'dd/MM/yyyy') AS time_slot
,t.date_in_week - 6 AS monday
,t.date_in_week AS sunday
FROM (SELECT trunc(SYSDATE - 3 * 7, 'iw') + rownum - 1 AS date_in_week
FROM all_objects
WHERE rownum <= (3 * 7)) t
WHERE to_char(t.date_in_week, 'd') = 1 -- d=1 is sunday
--------------------------------------------------
---/ 如果要按周统计(3)/
这个比较好
-- 是当前时间的上一个完整的周 我把周日到周六看成一个完成的。
SELECT to_char(t.date_in_week , 'dd/MM/yyyy') || ' - ' ||
to_char(t.date_in_week+6, 'dd/MM/yyyy') AS time_slot
,t.date_in_week AS sunday
,t.date_in_week+6 AS Saturday
FROM (SELECT trunc(SYSDATE - 3 * 7, 'iw') + rownum - 2 AS date_in_week
FROM all_objects
WHERE rownum <= (3 * 7)) t
WHERE to_char(t.date_in_week, 'd') = 1 -- d=1 is sunday/
----------------------------------------------------------------
// 获取当前日期的上月 第一天 和最后一天
---当i等于1-n 时 就是前n个月的 日期
select to_char(add_months(last_day(add_months(sysdate,-i))+1,-1),'YYYY-MM-DD HH24:MI:SS') from dual;
select to_char( last_day(add_months(sysdate,-i)),'YYYY-MM-DD HH24:MI:SS') from dual;
--- 20013-02-22 编写引入： wmsys.wm_concat函数，oracle 10g引入的；
作用：连接字符串，也可以用于将多行数据，转换为一行。 把“，” 替换成“|”
select replace( wmsys.wm_concat(ci.bin_interaction_id),',','|' )as ids, ci.bin_cus_id from bmp_customer_interaction ci group by ci.bin_cus_id
ci_bin_interaction_id ci_cust_id
1 95243209|95243211 9999
2 95243197|95243173|95243198 510918350
3 95243175|95243201|95243202|95243200|95243199 510918351
4 95243177|95243203 510918352
5 95243179|95243204 510918353
6 95243181|95243205 510918354
7 95243183|95243206 510918355
8 95243185|95243207 510918356
9 95243187|95243208 510918357
--- 在做budco BMP 项目时 用的表是 bmp_customer_interaction AS ci 一个用户注册可以有多个ci表中有相关多条数据 ，拿这个表‘bmp_customer_interaction’
为列子：一个用户的ID 有多ciID, 把每个用户的分组通过下面的sql就得到上面的查询出的结果：
下面的sql 用到的 oracle 内部函数 ,这些函数【红色字体】我以前没有用过。
下面这个sql 语句 是通用的。
-----
SELECT cusid
,text1
FROM (SELECT row_number() over(PARTITION BY cusid ORDER BY cusid, lvl DESC) rn
,cusid
,REPLACE(ltrim(text,'|'), '|', '|') AS text1
FROM (SELECT b.cusid AS cusid
,LEVEL lvl
,sys_connect_by_path(b.ciid, '|') text
FROM (SELECT ci.bin_cus_id AS cusid
,ci.bin_interaction_id AS ciid
,row_number() over(PARTITION BY ci.bin_cus_id ORDER BY ci.bin_cus_id, ci.bin_interaction_id) n
FROM bmp_customer_interaction ci
ORDER BY ci.bin_cus_id
,ci.bin_interaction_id) b
CONNECT BY b.cusid = PRIOR b.cusid
AND n - 1 = PRIOR n))
WHERE rn = 1
ORDER BY cusid
-------
如果是oracle 9i以下就有点麻烦了。引用http://www.jz123.cn/text/0648483.html
表结构如下：
NAME Null Type
------------------------ --------- -----
N_SEC_CODE NOT NULL CHAR(6)
C_RESEARCHER_CODE NOT NULL VARCHAR2(20)
此表保存了“股票”与“研究员”的对应关系数据，一般而言，对于同一只股票而言，可能有多个研究员
对其进行跟踪研究。所以目前遇到一个要求，需查询出每只股票和对应的研究员（研究员代码间，使用逗号分隔）。
例如有如下数据：
000297 chenpeng
000297 liusu
合并处理后需显示为：
000297 chenpeng,liusu
网上查了很多方法，但通常而言都是编写自定义多行文本合并函数，或者对支持的列数具有局限性。
最后在英文google中搜到如下比较巧的方法。不用在数据库中增加function，纯SQL一次性搞定，
而且扩充性很好，没有列的限制。
ORACLE纯SQL实现多行合并一行
SELECT n_sec_code, TRANSLATE (LTRIM (text, '/'), '*/', '*,') researcherList
FROM (SELECT ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,
lvl DESC) rn,
n_sec_code, text
FROM (SELECT n_sec_code, LEVEL lvl,
SYS_CONNECT_BY_PATH (c_researcher_code,'/') text
FROM (SELECT n_sec_code, c_researcher_code as c_researcher_code,
ROW_NUMBER () OVER (PARTITION BY n_sec_code ORDER BY n_sec_code,c_researcher_code) x
FROM m_researcher_stock_rel
ORDER BY n_sec_code, c_researcher_code) a
CONNECT BY n_sec_code = PRIOR n_sec_code AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY n_sec_code;
预想的结果成功出现，多行数据成功汇总到一行，特此分享与大家。对于你自己的应用中，只需要把SQL中“n_sec_code”
换为你的用来汇总的列，“c_researcher_code”替换为需合并文本的列，“m_researcher_stock_rel”替换为你的表名，就是这么简单。
SQL分析：
1、利用 “ROW_NUMBER () OVER (PARTITION BY……” 为按“股票代码”汇总后的数据行添加组内序号
2、“SYS_CONNECT_BY_PATH” 按组内序号相邻关系，为每一层进行不同行的“研究员代码”叠加
3、再次利用“股票代码”进行组内分组，但按第二部中的层次排倒序，增加调整后等级
4、取所有调整后等级为1的结果，即为所要求的数据行
方法很巧妙，值得学习。:-)
感谢网友@OctoberOne、@ericqliu的指点，在ORACLE10中可使用以下方法：
SELECT n_sec_code, wmsys.wm_concat (c_researcher_code) as result
FROM m_researcher_stock_rel
GROUP BY n_sec_code
又一个示例：
WITH tab AS(
select '01' no,'ss' name, 10 jiner,'麦子' you from dual union all
select '02' no,'dd' name, 11 jiner,'萝卜' you from dual union all
select '03' no,'cc' name, 9 jiner,'大豆' you from dual union all
select '01' no,'ss' name, 10 jiner,'白菜' you from dual)
SELECT a.no, a.name, a.jiner, ltrim(MAX(sys_connect_by_path(you, ',')), ',') you
FROM (SELECT row_number() over(PARTITION BY a.no, a.name, a.jiner
ORDER BY a.no, a.name, a.jiner) rn,a.*
FROM tab a) a
START WITH rn = 1
CONNECT BY PRIOR rn = rn - 1 AND
a.no = PRIOR a.no AND
a.name = PRIOR a.name AND
a.jiner = PRIOR a.jiner
GROUP BY a.no, a.name, a.jiner
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有