oracle sql 取连续数段
bigzhu
posted @ Aug 18, 2010 01:02:10 AM
in oracle
, 3713 阅读
-
需求
表结构如下,作了简化
CREATE TABLE "INVOICE"."POOL"
(
"TAX_NBR" VARCHAR2(20 BYTE),--税务发票号
"STAFF_ID" NUMBER(20,0),--工号
)
tax_nbr 为税务发票号,staff_id 为持有该发票号的工号. tax_nbr在一段内连续.
要求统计时,按工号持有的号段统计出来
可能有这样的数据:
staff_id tax_nbr 1 1 1 2 1 3 1 7 1 8 1 9
要求统计出:
staff_id tax_begin_nbr tax_end_nbr 1 1 3 1 7 9
-
思路一
写程序把每条记录按顺序取出,比较每条是否等于上一条+1,不等于,那就反回一条统计.
然后接着比较......
-
思路二
一条select sql 就搞定了
SELECT staff_id, min(tax_nbr), max(tax_nbr)
FROM (SELECT staff_id,
tax_nbr,
TO_NUMBER(tax_nbr) -
(ROW_NUMBER() OVER(PARTITION BY staff_id ORDER BY TO_NUMBER(tax_nbr))) DIF
FROM pool)
GROUP BY staff_id, DIF;
Aug 18, 2010 06:06:17 PM
发现一个问题:
因为tax_nbr原本是varchar2的关系,"OVER (PARTITION BY staff_id ORDER BY tax_nbr)"并不能完全得到想要的结果,在tax_nbr的值上两位数后排序就会以字符排序的方式进行,而非数字.也就是说,"1"后面会是"10","11",而不是"2".
要想达到预期效果,需要改成"ORDER BY TO_NUMBER(tax_nbr)",或者干脆直接把这个字段设置成number类型,而不是varchar2。
Aug 19, 2010 06:31:31 AM
谢谢指正,已经先改了,等有空好好研究下 verchar2 的排序.
不能改成 number 类型是因为业务需要,税务发票有些前置0,例如 0012 之类,还不能将前面的0去掉,只能用varchar2
Sep 29, 2022 07:53:09 PM
The 10th class studying in all Central & State Boards of the country can download NCERT STD-10 Sanskrit Sample Paper 2023 with answers that’s supports for all formats of exams such as SA1, SA2, FA1, FA2, FA3, FA4 and Assignments held under Term-1 & Term-2 of the course. NCERT Sanskrit Sample Paper Class 10 By downloading and practice these NCERT 10th Class Sanskrit model papers 2023 every student may get an analysis on the question paper pattern, important questions and the way of asking.The 10th class studying in all Central & State Boards of the country can download NCERT STD-10 Sanskrit Sample Paper 2023 with answers that’s supports for all formats of exams such as SA1, SA2, FA1, FA2, FA3, FA4 and Assignments held under Term-1 & Term-2 of the course.
Apr 23, 2023 07:43:11 PM
crediblebh