请教SQL高手。这种算法的SQL如何写？

Question

试试这个，没有测试
select a.no_of,a.CUSTOMER-b.CUSTOMER as CUSTOMER from tab a,tab b where a.no_of=b.no_of and a.status<>b.suatus;

Answer 1

SELECT  A.NO_OF,b.sum2-a.sum1 FROM(
select NO_OF,sum(customer) sum1 FROM test WHERE status='S' group by NO_OF) A,
(select NO_OF,sum(customer) sum2 FROM test WHERE status='E' group by NO_OF) B
WHERE A.NO_OF=B.NO_OF

Answer 2

更完整点的SELECT  A.NO_OF,NVL(b.sum2-a.sum1,0) FROM(
select NO_OF,sum(customer) sum1 FROM test WHERE status='S' group by NO_OF) A,
(select NO_OF,sum(customer) sum2 FROM test WHERE status='E' group by NO_OF) B
WHERE A.NO_OF(+)=B.NO_OF
UNION
SELECT  A.NO_OF,NVL(b.sum2-a.sum1,0) FROM(
select NO_OF,sum(customer) sum1 FROM test WHERE status='S' group by NO_OF) A,
(select NO_OF,sum(customer) sum2 FROM test WHERE status='E' group by NO_OF) B
WHERE A.NO_OF=B.NO_OF(+)

Answer 3

select a.NO_OF,b.CUSTOMER-a.CUSTOMER CUSTOMER from 
(select * from table_name where STATUS='s') a,
(select * from table_name where starus='e') b
where a.NO_OF=b.NO_OF

Answer 4

sorry,更正
SELECT  NVL(A.NO_OF,B.NO_OF),b.sum2-NVL(a.sum1,0) FROM(
select NO_OF,sum(customer) sum1 FROM test WHERE status='S' group by NO_OF) A,
(select NO_OF,sum(customer) sum2 FROM test WHERE status='E' group by NO_OF) B
WHERE A.NO_OF(+)=B.NO_OF
UNION
SELECT  NVL(B.NO_OF,A.NO_OF), NVL(b.sum2,0)-a.sum1 FROM(
select NO_OF,sum(customer) sum1 FROM test WHERE status='S' group by NO_OF) A,
(select NO_OF,sum(customer) sum2 FROM test WHERE status='E' group by NO_OF) B
WHERE A.NO_OF=B.NO_OF(+)