求sql语句（类似开关的判断，付详细说明、建表和测试数据插入脚本）

Question

可视化表述：现有以下数据：
-------------------------------------------------------------------------------------
  id                       time                         address 
0202               2010-6-2   8:37:02                       A 
0058               2010-6-2   8:37:02                       A 
0084               2010-6-2   8:37:01                       A 
B300    2010-6-2   8:37:02     A 
0058    2010-6-2   8:37:29                       B 
0084    2010-6-2   8:37:29                       B 
B300    2010-6-2   8:37:06                       A 
8AF6    2010-6-2   8:37:16                       B 
0202    2010-6-2   8:37:57                       B 
0058    2010-6-2   8:37:57                       B 
0084    2010-6-2   8:37:57                       B 
8AF6    2010-6-2   8:37:57                       B 
B300    2010-6-2   8:37:56                       B 
8AF6    2010-6-2   8:38:24                       B 
B300    2010-6-2   8:38:07                       A 
0202    2010-6-2   8:38:24                       A 
0009    2010-6-2   8:38:24                       A 
0058    2010-6-2   8:38:24                       A
-------------------------------------------------------------------------------------想要得到一下结果：（就是同一address第一次出现时新增的type字段值为0，当连续出现多次时，出现偶次数时type字段值为1，反之为0）
--------------------------------------------------------------------------------------------------
  id                       time                         address                  type
0202               2010-6-2   8:37:02                       A                      0   
0058               2010-6-2   8:37:02                       A                      1
0084               2010-6-2   8:37:01                       A                      0
B300    2010-6-2   8:37:02     A                      1
0058    2010-6-2   8:37:29                       B                      0
0084    2010-6-2   8:37:29                       B                      1 
B300    2010-6-2   8:37:06                       A                      0 
8AF6    2010-6-2   8:37:16                       B                      0 
0202    2010-6-2   8:37:57                       B                      1
0058    2010-6-2   8:37:57                       B                      0 
0084    2010-6-2   8:37:57                       B                      1 
8AF6    2010-6-2   8:37:57                       B                      0 
B300    2010-6-2   8:37:56                       B                      1
8AF6    2010-6-2   8:38:24                       B                      0
B300    2010-6-2   8:38:07                       A                      0
0202    2010-6-2   8:38:24                       A                      1
0009    2010-6-2   8:38:24                       A                      0
0058    2010-6-2   8:38:24                       A                      1 
--------------------------------------------------------------------------------------------------建表和插入测试数据的脚本：SQL> create table t3(id char(4), time date, address char(2));表已创建。SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';会话已更改。SQL> insert into t3 values('0202', '2010-6-2 8:37:02', 'A');已创建 1 行。SQL> insert into t3 values('0058', '2010-6-2 8:37:02', 'A');已创建 1 行。SQL> insert into t3 values('0084', '2010-6-2 8:37:01', 'A');已创建 1 行。SQL> insert into t3 values('B300', '2010-6-2 8:37:02', 'A');已创建 1 行。SQL> insert into t3 values('0058', '2010-6-2 8:37:29', 'B');已创建 1 行。SQL> insert into t3 values('0084', '2010-6-2 8:37:29', 'B');已创建 1 行。SQL> insert into t3 values('B300', '2010-6-2 8:37:06', 'A');已创建 1 行。SQL> insert into t3 values('8AF6', '2010-6-2 8:37:16', 'B');已创建 1 行。SQL> insert into t3 values('0202', '2010-6-2 8:37:57', 'B');已创建 1 行。SQL> insert into t3 values('0058', '2010-6-2 8:37:57', 'B');已创建 1 行。SQL> insert into t3 values('0084', '2010-6-2 8:37:57', 'B');已创建 1 行。SQL> insert into t3 values('8AF6', '2010-6-2 8:37:57', 'B');已创建 1 行。SQL> insert into t3 values('B300', '2010-6-2 8:37:56', 'B');已创建 1 行。SQL> insert into t3 values('8AF6', '2010-6-2 8:38:24', 'B');已创建 1 行。SQL> insert into t3 values('B300', '2010-6-2 8:38:07', 'A');已创建 1 行。SQL> insert into t3 values('0202', '2010-6-2 8:38:24', 'A');已创建 1 行。SQL> insert into t3 values('0009', '2010-6-2 8:38:24', 'A');已创建 1 行。SQL> insert into t3 values('0058', '2010-6-2 8:38:24', 'A');已创建 1 行。

Answer 1

没试验  楼主实验下select id,time,address,type,case when rn = '1' then '0'
 when mod(rn,2) = 0 then '0'
 else '1' end
from 
(select id ,time, address,type ,row_number() over(partition by address) rn from your_table)
;

Answer 2

楼上的over分析函数  是能达到效果的

Answer 3

select  id,time,address,decode(mod(rn,2),1,'0',0,'1') type
from
(select  id,time,address,row_number() over(partition by address order by time ) rn
from tb)

Answer 4

可视化表现有以下数据：
  id                       time                         address
0202               2010-6-2   8:37:02                       A 
0058               2010-6-2   8:38:02                       A
0084               2010-6-2   8:39:01                       A
0202               2010-6-2   8:40:02                       A 
0058               2010-6-2   8:41:02                       A
0084               2010-6-2   8:42:01                       A     
0202               2010-6-2   8:43:02                       A
0058               2010-6-2   8:44:02                       A
0084               2010-6-2   8:45:01                       A 
0202               2010-6-2   8:46:02                       B
0058               2010-6-2   8:47:02                       B
0202               2010-6-2   8:48:02                       B   
0058               2010-6-2   8:49:02                       A   
想要得到的结果：（一个id在同一地方，奇数次出现时type为0，偶次数出现时为1；当地方变化之后，规则从新开始：例如上一站是A，当前站是B，下一站如果又是A的话，那么应该看作是第一次到A，即为0），还有就是要严格按照时间升序排序  id                       time                         address                  type
0202               2010-6-2   8:37:02                       A                      0   
0058               2010-6-2   8:38:02                       A                      0
0084               2010-6-2   8:39:01                       A                      0
0202               2010-6-2   8:40:02                       A                      1   
0058               2010-6-2   8:41:02                       A                      1
0084               2010-6-2   8:42:01                       A                      1
0202               2010-6-2   8:43:02                       A                      0  
0058               2010-6-2   8:44:02                       A                      0
0084               2010-6-2   8:45:01                       A                      0
0202               2010-6-2   8:46:02                       B                      0  
0058               2010-6-2   8:47:02                       B                      0
0202               2010-6-2   8:48:02                       B                      1
0058               2010-6-2   8:49:02                       A                      0建表和插入测试数据的脚本：SQL> create table t3(id char(4), time date, address char(2));
SQL> alter session set nls_date_format='YYYY-MM-DD HH24:MI:SS';
SQL> insert into t3 values('0202', '2010-6-2 8:37:02', 'A');SQL> insert into t3 values('0058', '2010-6-2 8:38:02', 'A');SQL> insert into t3 values('0084', '2010-6-2 8:39:01', 'A');SQL> insert into t3 values('0202', '2010-6-2 8:40:02', 'A');SQL> insert into t3 values('0058', '2010-6-2 8:41:02', 'A');SQL> insert into t3 values('0084', '2010-6-2 8:42:01', 'A');SQL> insert into t3 values('0202', '2010-6-2 8:43:02', 'A');SQL> insert into t3 values('0058', '2010-6-2 8:44:02', 'A');SQL> insert into t3 values('0084', '2010-6-2 8:45:01', 'A');SQL> insert into t3 values('0202', '2010-6-2 8:46:02', 'B');SQL> insert into t3 values('0058', '2010-6-2 8:47:02', 'B');SQL> insert into t3 values('0202', '2010-6-2 8:48:02', 'B');SQL> insert into t3 values('0058', '2010-6-2 8:49:02', 'A');