请教分析函数中的一个问题

显示全部楼层 · 2006-9-7 17:01:08

有TEST表一张
SQL> desc test;
NameType Nullable Default Comments
------- ------------ -------- ------- --------
CODENUMBER(16,2) Y

WORKDAY DATE Y
SQL> select * from test;

CODE WORKDAY
------------------ -----------

23.10 2006-1-1

23.10 2006-1-2

23.10 2006-2-1

23.10 2006-3-1

23.10 2006-3-1

23.10 2006-4-1

23.10 2006-4-1
7 rows selected
我想要的结果是
WORKDAY
CODE TOTAL_CODE
------------------------ ------------------ ------------------------------
200601
23.10
23.1
200601
23.10
46.2
200602
23.10
23.1
200603
23.10
23.1
200603
23.10
46.2
200604
23.10
23.1
200604
23.10
46.2
SQL我是这样写的：
SELECTsubstr(to_char(workday,'yyyymmdd'),1,6) workday,

code,

SUM (code) over(PARTITION BY substr(to_char(workday,'yyyymmdd'),1,6) ORDER BY code)total_code
FROMTEST ;
但得出的结果却是：
WORKDAY
CODE TOTAL_CODE
------------------------ ------------------ ------------------------------
200601
23.10
46.2
200601
23.10
46.2
200602
23.10
23.1
200603
23.10
46.2
200603
23.10
46.2
200604
23.10
46.2
200604
23.10
46.2

请高手指教一下怎么回事？

千问 · 2006-9-7 17:01:08

SELECT substr(to_char(workday,'yyyymmdd'),1,6) workday,
code,
SUM (code) over(PARTITION BY substr(to_char(workday,'yyyymm'),1,6) ORDER BY code) total_code
FROM TEST ;

千问 · 2006-9-7 17:01:08

[PHP]
如下：
SQL> select * from test;

CODE WORKDAY
------------------ -----------

23.10 2006-1-1

23.10 2006-1-2

23.10 2006-2-1

23.10 2006-3-1

23.10 2006-3-1

23.10 2006-4-1

23.10 2006-4-1
7 rows selected
SQL>
SQL> SELECT substr(to_char(workday, 'yyyymmdd'), 1, 6) workday,
2 code,
3 SUM(code) over(PARTITION BY substr(to_char(workday, 'yyyymmdd'), 1, 6) ORDER BY code rows between unbounded preceding and current row) total_code
4FROM TEST;
WORKDAY
CODE TOTAL_CODE
------------ ------------------ ----------
200601
23.10 23.1
200601
23.10 46.2
200602
23.10 23.1
200603
23.10 23.1
200603
23.10 46.2
200604
23.10 23.1
200604
23.10 46.2
7 rows selected
SQL>

[/PHP]

千问 · 2006-9-7 17:01:08

SELECTto_char(workday,'yyyymm') workday,

code,

SUM (code) over( PARTITION BY to_char(workday,'yyyymm') ORDER BY code)total_code
FROMTEST;
这样还是没有得出我想要的那个结果，见鬼了

千问 · 2006-9-7 17:01:08

我已经给出了答案！

千问 · 2006-9-7 17:01:08

结贴了
知道原因了
由于CODE的值我给的都一样
ORDER BY 的时候出了问题
谢谢

千问 · 2006-9-7 17:01:08

刚没看见
bell6248
谢谢你啦
从你那里学习了

千问 · 2006-9-7 17:01:08

原帖由 bell6248 于 2007-9-28 11:41 发表
……
SQL>
SQL> SELECT substr(to_char(workday, 'yyyymmdd'), 1, 6) workday,
2 code,
3 SUM(code) over(PARTITION BY substr(to_char(workday, 'yyyymmdd'), 1, 6) ORDER BY code rows between unbounded preceding and current row) total_code
4FROM TEST;
WORKDAY
CODE TOTAL_CODE
------------ ------------------ ----------
200601
23.10 23.1
200601
23.10 46.2
200602
23.10 23.1
200603
23.10 23.1
200603
23.10 46.2
200604
23.10 23.1
200604
23.10 46.2
7 rows selected
……

如果只使用ORDER BY 而不知道后面的窗口的时候，它默认不就是rows between unbounded preceding and current row吗？
结果显示是不是，难道没有默认值？

千问 · 2006-9-7 17:01:08

原帖由 superlxw 于 2007-9-28 11:46 发表
结贴了
知道原因了
由于CODE的值我给的都一样
ORDER BY 的时候出了问题
谢谢

order by rownum 或者
order by code,rownum
[php]
huiyi@HUIYI(192.168.1.100)> select * from test;
CODE WORKDAY
---------- -------------------
23.1 2006-01-01 00:00:00
23.1 2006-01-02 00:00:00
23.1 2006-02-01 00:00:00
23.1 2006-03-01 00:00:00
23.1 2006-03-01 00:00:00
23.1 2006-04-01 00:00:00
23.1 2006-04-01 00:00:00
7 rows selected.
Elapsed: 00:00:00.06
huiyi@HUIYI(192.168.1.100)> select workday,code,sum(code) over(partition by trunc(workday,'mm') order by code,rownum) col01
2from test;
WORKDAY
CODECOL01
------------------- ---------- ----------
2006-01-01 00:00:00 23.1 23.1
2006-01-02 00:00:00 23.1 46.2
2006-02-01 00:00:00 23.1 23.1
2006-03-01 00:00:00 23.1 23.1
2006-03-01 00:00:00 23.1 46.2
2006-04-01 00:00:00 23.1 23.1
2006-04-01 00:00:00 23.1 46.2
7 rows selected.
Elapsed: 00:00:00.07
--
[/php]
[ 本帖最后由 HuiYiSky 于 2008-1-7 09:39 编辑 ]