We have a table as below:
create table goods ( barcode number(15,0) not null primary key, category number(15,0) not null, num number(15,0) not null, mark varchar2(10) not null )
The data of the table as below:
barcode category num mark 9679839 100003894 212 E 9687165 100003894 213 E 9680883 100003894 214 I 9710863 100003894 515 E 9681246 100003894 516 E 9682695 100003894 517 E 9681239 100003894 518 E 9685409 100003894 519 E 9679843 100003894 520 C 9679844 100003894 521 C 9714882 100003894 522 E 9679845 100003894 523 I 9681211 100003894 524 E 9681216 100003894 525 E
The expected query result is as follows:
category mark start end 100003894 E 212 213 100003894 I 214 100003894 E 515 519 100003894 C 520 521 100003894 E 522 100003894 I 523 100003894 E 524 525
This is the classic "runs" problem.
select category , mark , start , case when start = end then null else end end as end from ( select dt.category , dt.mark , min(dt.num) as start , max(dt.num) as end from ( select s1.category , s1.mark , s1.num , s1.num - ( select count(*) from goods as s2 where s2.category = s1.category and s2.mark = s1.mark and s2.num <= s1.num ) as diff_rank from goods as s1 ) as dt group by dt.category , dt.mark , dt.diff_rank ) as dt2 order by category , start
For the solution, I am indebted to Joe Celko's SQL for Smarties, 3rd edition, page 556. Please do not ask me to explain it. Instead, test it yourself, by building up your query starting at the DT subquery. Analyze the results carefully. The outermost query is merely for cosmetic purposes.
Incidentally, this is the first time I can recall ever writing ELSE END END AS END in SQL. This may not work in every database system, so use identifier delimiters as necessary.
Dig Deeper on Oracle and SQL
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading