数据是verchar型的
表A中的字段是数据项是3011,3012,2001,3001,3005,2002,2003,3006,3009,3010,3013,3014
我需要得到的是2001-2003,3001,3005-3006,3009-3014
这是一个报表,数据没有规律存放并且是中断的。 如果是按从小到大有规律存放,可以解决。
CREATE TABLE A(COL VARCHAR2(10));
INSERT INTO A VALUES('2001');
INSERT INTO A VALUES('2002');
INSERT INTO A VALUES('2003');
INSERT INTO A VALUES('3001');
INSERT INTO A VALUES('3005');
INSERT INTO A VALUES('3006');
INSERT INTO A VALUES('3009');
INSERT INTO A VALUES('3010');
INSERT INTO A VALUES('3011');
INSERT INTO A VALUES('3012');
INSERT INTO A VALUES('3013');
INSERT INTO A VALUES('3014');
COMMIT;SELECT DECODE(MAX(COL),MIN(COL),MIN(COL),MIN(COL) || '-' || MAX(COL)) COL FROM(
SELECT COL,COL-ROWNUM COL1 FROM A ORDER BY COL)
GROUP BY COL1;COL
------------
2001-2003
3001
3005-3006
3009-3014
但是如果无规律呢?
CREATE TABLE A(COL VARCHAR2(10));
INSERT INTO A VALUES('3013');
INSERT INTO A VALUES('3002');
INSERT INTO A VALUES('3014');
INSERT INTO A VALUES('2001');
INSERT INTO A VALUES('3014');
INSERT INTO A VALUES('2002');
INSERT INTO A VALUES('2003');
INSERT INTO A VALUES('3001');
INSERT INTO A VALUES('3005');
INSERT INTO A VALUES('3006');
INSERT INTO A VALUES('3009');
INSERT INTO A VALUES('3010');
INSERT INTO A VALUES('3011');
INSERT INTO A VALUES('3012');COMMIT;
可以这么取
SELECT MIN(COL) || '-' || MAX(COL)
FROM A
WHERE COL >= '2001'
AND COL <= '2003'
UNION ALL
SELECT COL
FROM A
WHERE COL = '3001'
UNION ALL
SELECT MIN(COL) || '-' || MAX(COL)
FROM A
WHERE COL >= '3005'
AND COL <= '3006'
UNION ALL
SELECT MIN(COL) || '-' || MAX(COL)
FROM A
WHERE COL >= '3009'
AND COL <= '3014'
SELECT DECODE(MAX(COL),MIN(COL),MIN(COL),MIN(COL) || '-' || MAX(COL)) COL FROM(
SELECT COL,COL-ROWNUM COL1 from A ORDER by COL desc)
GROUP BY COL1;这样也不行啊!
如果是
SELECT DECODE(MAX(COL),MIN(COL),MIN(COL),MIN(COL) || '-' || MAX(COL)) COL FROM(
SELECT COL,COL-ROWNUM COL1 FROM (select col from A order by col desc) ORDER BY COL)
GROUP BY COL1;这样也不行。不知道怎么弄了
FROM (SELECT col, col - rownum col1 FROM (SELECT col FROM a ORDER BY col))
GROUP BY col1
ORDER BY col1LZ可以结贴了
FROM (SELECT COL, COL - ROWNUM COL1
FROM (SELECT DISTINCT COL FROM A ORDER BY COL))
GROUP BY COL1;