oracle计算工资的题,【大话IT】用SQL做出这道题工资任你开(限时5.1假期)

with temp as

(select 'cust1' cust,1 "雪碧",1 "可口可乐",1 "橙汁",1 "脉动" from dual union all

select 'cust2' cust,1 "雪碧",1 "可口可乐",0 "橙汁",0 "脉动" from dual union all

select 'cust3' cust,0 "雪碧",0 "可口可乐",0 "橙汁",1 "脉动" from dual union all

select 'cust4' cust,1 "雪碧",1 "可口可乐",1 "橙汁",0 "脉动" from dual union all

select 'cust5' cust,0 "雪碧",1 "可口可乐",1 "橙汁",1 "脉动" from dual

),

t as

(select cust col1,yl col2,xz col3

from temp

unpivot include nulls (xz for yl in ( "雪碧","可口可乐","橙汁","脉动"))

),

t1 as

(select '雪碧,可口可乐,橙汁,脉动' col from dual

),

t2 as

(select regexp_substr(col,'[^,]+',1,level) col1,null col2,null col3,null col4 from t1 connect by level<=4 union all

select regexp_substr(col,'[^,]+',1,1) col1, regexp_substr(col,'[^,]+',1,level+1) col2,null col3,null col4 from t1 connect by level<=3 union all

select regexp_substr(col,'[^,]+',1,2) col1, regexp_substr(col,'[^,]+',1,level+2) col2,null col3,null col4 from t1 connect by level<=2 union all

select regexp_substr(col,'[^,]+',1,3) col1, regexp_substr(col,'[^,]+',1,level+3) col2,null col3,null col4 from t1 connect by level<=1 union all

select regexp_substr(col,'[^,]+',1,1) col1, regexp_substr(col,'[^,]+',1,2) col2, regexp_substr(col,'[^,]+',1,level+2) col3,null col4 from t1 connect by level<=2 union all

select regexp_substr(col,'[^,]+',1,2) col1, regexp_substr(col,'[^,]+',1,3) col2,regexp_substr(col,'[^,]+',1,level+3) col3,null col4 from t1 connect by level<=1 union all

select regexp_substr(col,'[^,]+',1,1) col1, regexp_substr(col,'[^,]+',1,2) col2,regexp_substr(col,'[^,]+',1,3) col3,regexp_substr(col,'[^,]+',1,level+3) col4 from t1 connect by level<=1

)

select t3.col1||decode(t3.col2,null,t3.col2,','||t3.col2)||decode(t3.col3,null,t3.col3,','||t3.col3)||decode(t3.col4,null,t3.col4,','||t3.col4) col,

status,

count(distinct t3.col5)/5cnt

from

(select t2.col1 col1,

t2.col2 col2,

t2.col3 col3,

t2.col4 col4,

'且' status,

tt.col5 col5

from t2 left join

(

select t2.col1 col1,

t2.col2 col2,

t2.col3 col3,

t2.col4 col4,

t.col1 col5

from t,t2

where (t.col2 = t2.col1 or t.col2 = t2.col2 or t.col2=t2.col3 or t.col2=t2.col4)

and t.col3 = 1

group by t2.col1,t2.col2,t2.col3,t2.col4,t.col1

having count(1)=(case when t2.col2 is null then 1

when t2.col2 is not null and t2.col3 is null then 2

when t2.col2 is not null and t2.col3 is not null and t2.col4 is null then 3

else 4

end) )tt on t2.col1 = tt.col1 and nvl(t2.col2,-1) = nvl(tt.col2,-1) and nvl(t2.col3,-1) = nvl(tt.col3,-1) and  nvl(t2.col4,-1) = nvl(tt.col4,-1)

union all

select    t2.col1 col1,

t2.col2,

t2.col3 col3,

t2.col4 col4,

'或' status,

t.col1 col5

from t,t2

where (t.col2 = t2.col1 or t.col2 = t2.col2 or t.col2=t2.col3 or t.col2=t2.col4)

and t.col3 = 1

) t3

group by  t3.col1||decode(t3.col2,null,t3.col2,','||t3.col2)||decode(t3.col3,null,t3.col3,','||t3.col3)||decode(t3.col4,null,t3.col4,','||t3.col4),status