create table temp
(
id char(10),
type char(3),
amount int
);
insert temp select
'001', 'I', 3 union select
'002', 'I', 4 union select
'003', 'I', 5 union select
'001', 'O', 3 union select
'002', 'O', 2;select a.id,a.aQ-b.bQ as result from
(select id,sum(amount) aQ from temp where type = 'I' group by id ) a
left join (select id,sum(amount) bQ from temp where type = 'O' group by id) b
on a.id = b.id/*drop table temp*/
我想出来的结果是
id result
002 2
003 5
(
id char(10),
type char(3),
amount int
);
insert temp select
'001', 'I', 3 union select
'002', 'I', 4 union select
'003', 'I', 5 union select
'001', 'O', 3 union select
'002', 'O', 2;select a.id,a.aQ-b.bQ as result from
(select id,sum(amount) aQ from temp where type = 'I' group by id ) a
left join (select id,sum(amount) bQ from temp where type = 'O' group by id) b
on a.id = b.id/*drop table temp*/
我想出来的结果是
id result
002 2
003 5
(select id,sum(amount) aQ from temp where type = 'I' group by id ) a
INNER join (select id,sum(amount) bQ from temp where type = 'O' group by id) b
on a.id = b.id
002 2
第三条没出来
(select id,sum(amount) aQ from temp where type = 'I' group by id ) a
left join (select id,sum(amount) bQ from temp where type = 'O' group by id) b
on a.id = b.id and a.aQ<>isnull(b.bQ,0) 看错,修改
(select id,sum(amount) aQ from temp where type = 'I' group by id ) a
left join (select id,sum(amount) bQ from temp where type = 'O' group by id) b
on a.id = b.id and a.aQ<>isnull(b.bQ,0) [Err] 1582 - Incorrect parameter count in the call to native function 'isnull'
(
id char(10),
type char(3),
amount int
);
insert temp select
'001', 'I', 3 union select
'002', 'I', 4 union select
'003', 'I', 5 union select
'001', 'O', 3 union select
'002', 'O', 2;select a.id,a.aQ-ISNULL(b.bQ,0) as result
from (select id,sum(amount) aQ from temp where type = 'I' group by id ) a
left join
(select id,sum(amount) bQ from temp where type = 'O' group by id ) bon a.id = b.id WHERE a.aQ<>ISNULL(b.bQ ,0)id result
---------- -----------
002 2
003 5(2 行受影响)
select a.id,a.aQ-isnull(b.bQ,0) as q from
(select id,sum(amount) aQ from tempa where type = 'I' group by id ) a
left join (select id,sum(amount) bQ from tempa where type = 'O' group by id) b
on a.id = b.id
) a where a.q!=0
发错版区了 不好意思 77
我的是MYSQL
(
id char(10),
type char(3),
amount int
);
insert temp select
'001', 'I', 3 union select
'002', 'I', 4 union select
'003', 'I', 5 union select
'001', 'O', 3 union select
'002', 'O', 2;select id,SUM(aQ-bQ ) as result
from
( select id, sum(amount) aQ,0 BQ from temp where type = 'I' group by id
UNION ALL
select id,0,sum(amount) bQ from temp where type = 'O' group by id
)T
GROUP BY ID HAVING SUM(aQ-bQ )<>0id result
---------- -----------
002 2
003 5(2 行受影响)