现有2个表如下create table svcloginfo (
ID varchar2(32) not null,
STATIONID varchar2(32) not null,
INPROTECT number(*,0) not null,
ISVISITED number(*,0) not null,
ISWATERED number(*,0) not null,
FIXTYPE number(*,0) not null);
create table svcstation (
ID varchar2(32) not null,
CODE varchar2(20) not null,
NAME varchar2(20) not null);
svcloginfo 模拟数据数据
id stationid inprotect isvisited iswatered fixtype
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 0 0 0 1
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid2 1 0 1 2
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 0 0 0 3
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid3 1 1 1 1
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid2 1 0 1 1
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid3 0 0 0 2
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 1 1 0 1
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 0 0 0 3
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 0 1 0 1svcstation 模拟数据
id code name
stationid1 222222 aaaaaa
stationid2 333333 bbbbbb
stationid3 444444 cccccc想统计并输出如下条件的格式数据:
code | name | inprotect=0 and isvisited =0 | inprotect=0 and isvisited =0 | inprotect=1 and isvisited =0 | inprotect=1 and isvisited =1 | fixtype=2 | fixtype=3
222222 aaaaaa 2 0 1 1 1 2
333333 bbbbbb 1 1 2 2 2 3
444444 cccccc 1 2 0 1 1 1
ID varchar2(32) not null,
STATIONID varchar2(32) not null,
INPROTECT number(*,0) not null,
ISVISITED number(*,0) not null,
ISWATERED number(*,0) not null,
FIXTYPE number(*,0) not null);
create table svcstation (
ID varchar2(32) not null,
CODE varchar2(20) not null,
NAME varchar2(20) not null);
svcloginfo 模拟数据数据
id stationid inprotect isvisited iswatered fixtype
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 0 0 0 1
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid2 1 0 1 2
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 0 0 0 3
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid3 1 1 1 1
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid2 1 0 1 1
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid3 0 0 0 2
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 1 1 0 1
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 0 0 0 3
04d4ae9a4c32dc8cc7360f61eafcb7a8 stationid1 0 1 0 1svcstation 模拟数据
id code name
stationid1 222222 aaaaaa
stationid2 333333 bbbbbb
stationid3 444444 cccccc想统计并输出如下条件的格式数据:
code | name | inprotect=0 and isvisited =0 | inprotect=0 and isvisited =0 | inprotect=1 and isvisited =0 | inprotect=1 and isvisited =1 | fixtype=2 | fixtype=3
222222 aaaaaa 2 0 1 1 1 2
333333 bbbbbb 1 1 2 2 2 3
444444 cccccc 1 2 0 1 1 1
ID int not null,
SID int not null,
con1 number(*,0) not null,
con2 number(*,0) not null,
con3 number(*,0) not null);
create table svcstation (
ID int not null,
CODE varchar2(20) not null,
NAME varchar2(20) not null);
svcloginfo 模拟数据数据
id stationid con1 con2 con3
1 1 0 0 0
2 2 1 0 1
3 3 0 0 0svcstation 模拟数据
id code name
1 222222 aaaaaa
2 333333 bbbbbb
3 444444 cccccc想统计并输出如下条件的格式数据:
code | name | con1==0 and con2==0 | con1==0 and con2==1 | con1==1 and con2==0 | con2==0 and con3==1
222222 aaaaaa 2 0 1 1
333333 bbbbbb 1 1 2 2
444444 cccccc 1 2 0 1
when condition3 and condition4 then result2
else result 3
end