表c_table(id,xh)中的记录
a,1
a,2
a,3
a,4
b,1
b,2
b,4
c,1
c,4
字段xh最大为4,
求视图v_view(id,xh1,xh2,xh3,xh4),得到的结果为
a,1,1,1,1
b,1,1,0,1
c,1,0,0,1
a,1
a,2
a,3
a,4
b,1
b,2
b,4
c,1
c,4
字段xh最大为4,
求视图v_view(id,xh1,xh2,xh3,xh4),得到的结果为
a,1,1,1,1
b,1,1,0,1
c,1,0,0,1
SELECT 'a'id,1 xh FROM dual
UNION ALL
SELECT 'a',2 FROM dual
UNION ALL
SELECT 'a',3 FROM dual
UNION ALL
SELECT 'a',4 FROM dual
UNION ALL
SELECT 'b',1 FROM dual
UNION ALL
SELECT 'b',2 FROM dual
UNION ALL
SELECT 'b',4 FROM dual
UNION ALL
SELECT 'c',1 FROM dual
UNION ALL
SELECT 'c',4 FROM dual
)
SELECT
id,
sum(Decode(xh,1,1,0))xh1,
sum(Decode(xh,2,1,0))xh2,
sum(Decode(xh,3,1,0))xh3,
sum(Decode(xh,4,1,0))xh4
FROM t
GROUP BY t.id
ORDER BY t.idoutput:
a 1 1 1 1
b 1 1 0 1
c 1 0 0 1