\u5ba2\u6237\u6709\u4e00\u4e2a\u5224\u65ad\u53f7\u7801\u8fde\u7eed\u7684\u95ee\u9898\uff0c\u8fd9\u79cd\u95ee\u9898\u7528\u5206\u6790\u51fd\u6570\u89e3\u51b3\u662f\u6bd4\u8f83\u6709\u4ee3\u8868\u6027\u7684\u3002
\n\u8868\u7ed3\u6784\u548c\u6570\u636e\u4e3a\uff1a<\/p>\n
SQL> select * from tab1;\r\n ID CA REG\r\n---------- -- ---\r\n 100 11 S\r\n 101 11 S\r\n 102 11 N\r\n 103 11 N\r\n 104 11 N\r\n 105 0 S\r\n 106 0 S\r\n 107 0 S\r\n 108 0 N\r\n 109 0 N\r\n 110 0 N\r\n 111 11 S\r\n 112 11 S\r\n 113 11 N\r\n 114 11 N\r\n 115 11 N\r\n 116 0 S\r\n 117 0 S\r\n 118 0 N\r\n 119 0 N\r\n 120 0 N\r\n 121 11 S\r\n 122 11 S\r\n 123 11 S\r\n 124 11 S\r\n 125 11 S\r\n 126 11 S\r\n 127 11 S\r\n 128 11 S\r\n 129 11 S\r\n 130 11 S\r\n 200 11 S\r\n 201 11 S\r\n 202 11 N\r\n 203 11 N\r\n 204 11 N\r\n 205 0 S\r\n 206 0 S\r\n 207 0 S\r\n 208 0 N\r\n 209 0 N\r\n 210 0 N\r\n 211 11 S\r\n 212 11 S\r\n 213 11 N\r\n 214 11 N\r\n 215 11 N\r\n 300 11 S\r\n 301 11 S\r\n 302 11 N\r\n 303 11 N\r\n 304 11 N\r\n 305 0 S\r\n 306 0 S\r\n 307 0 S\r\n 308 0 N\r\n 309 0 N\r\n 310 0 N\r\n 311 11 S\r\n 312 11 S\r\n 313 11 N\r\n 314 11 N\r\n 315 11 N\r\n63 rows selected.<\/pre>\n\u9700\u6c42\u662f\u627e\u5230\u4e00\u7ec4\u8fde\u7eedID\u7684\u6700\u5c0f\u503c\u548c\u6700\u5927\u503c\uff0c\u8981\u6c42\u9664\u4e86\u6570\u503c\u8fde\u7eed\u5916\uff0c\u53e6\u5916\u4e24\u4e2a\u5b57\u6bb5\u4e5f\u5fc5\u987b\u76f8\u540c\uff0c\u5426\u5219\u8ba4\u4e3a\u662f\u65b0\u7684\u53f7\u6bb5\u3002
\n\u7b80\u5355\u7684\u8bf4\uff0c\u6700\u7ec8\u9700\u8981\u7684\u7ed3\u679c\u7c7b\u4f3c\uff1a<\/p>\n
100-101 11 S\r\n102-104 11 N\r\n105-107 00 S\r\n108-110 00 N\r\n... ...\r\n200-201 11 S\r\n202-204 11 N\r\n205-207 00 S\r\n208-210 00 N<\/pre>\n\u5176\u5b9e\u8fd9\u4e2a\u95ee\u9898\u4f7f\u7528\u5206\u6790\u51fd\u6570\u5e76\u4e0d\u590d\u6742\uff0c\u7b2c\u4e00\u6b65\u662f\u901a\u8fc7\u6784\u9020\u5217\u5224\u65ad\u662f\u5426\u8fde\u7eed\uff0c\u7136\u540e\u8fdb\u4e00\u6b65\u6784\u9020\u51fa\u5206\u7ec4\u7684\u4f9d\u636e\uff0c\u8fd9\u91cc\u4f7f\u7528\u4e86\u4e00\u4e2a\u5c0f\u6280\u5de7\uff0c\u5c061\u53d8\u62100\uff0c\u800c\u4ece\u4fdd\u8bc1\u4e00\u4e2a\u53f7\u6bb5\u5185SUM\u6c47\u603b\u7684\u7ed3\u679c\u662f\u76f8\u540c\u7684\uff0c\u6700\u540eGROUP BY\u5c31\u53ef\u4ee5\u5f97\u5230\u6700\u7ec8\u7ed3\u679c\uff1a<\/p>\n
SQL> select min(id) || '-' || max(id) flag,\r\n 2 card_type,\r\n 3 region\r\n 4 from\r\n 5 (\r\n 6 select id, card_type, region, sum(flag) over(order by id) flag\r\n 7 from (\r\n 8 select id, card_type, region,\r\n 9 nvl(id-lag(id, 1) over(order by id)\r\n 10 + case when card_type = lag(card_type, 1) over(order by id) then 0 else 10 end\r\n 11 + case when region = lag(region, 1) over(order by id) then 0 else 10 end\r\n 12 , 1) -1 flag\r\n 13 from tab1\r\n 14 )\r\n 15 )\r\n 16 group by card_type, region, flag\r\n 17 order by 1;\r\nFLAG CA REG\r\n-------------------- -- ---\r\n100-101 11 S\r\n102-104 11 N\r\n105-107 0 S\r\n108-110 0 N\r\n111-112 11 S\r\n113-115 11 N\r\n116-117 0 S\r\n118-120 0 N\r\n121-130 11 S\r\n200-201 11 S\r\n202-204 11 N\r\n205-207 0 S\r\n208-210 0 N\r\n211-212 11 S\r\n213-215 11 N\r\n300-301 11 S\r\n302-304 11 N\r\n305-307 0 S\r\n308-310 0 N\r\n311-312 11 S\r\n313-315 11 N\r\n21 rows selected.<\/pre>\n","protected":false},"excerpt":{"rendered":"\u5ba2\u6237\u6709\u4e00\u4e2a\u5224\u65ad\u53f7\u7801\u8fde\u7eed\u7684\u95ee\u9898\uff0c\u8fd9\u79cd\u95ee\u9898\u7528\u5206\u6790\u51fd\u6570\u89e3\u51b3\u662f\u6bd4\u8f83\u6709\u4ee3\u8868\u6027\u7684\u3002 \u8868\u7ed3\u6784\u548c\u6570\u636e\u4e3a\uff1a SQL> select * from tab1; ID CA REG ———- — — 100 11 S 101 11 S 102 11 N 103 11 N 104 11 N 105 0 S 106 0 S 107 0 S 108 0 N … Continue reading