I'm anxious for a question !!

fanfan1980

--I don't know right

create table A (ID int, GX char (1), NUM int, NUM1 int)
insert A select 1, 'A', 100, 50
union all select 2, 'A', 200, 30
union all select 3, 'A', 300, 40
union all select 3, 'B', 250, 20
union all select 4, 'B', 100, 30

union all select 5, 'B', 200, 20
union all select 5, 'A', 150, 25

union all select 6, 'B', 200, 30


select ID, NUM = sum (case GX when 'A' then NUM else 0 end), NUM1 = sum (NUM1)
from A
where ID in
(
select ID from
(
select distinct ID from A where GX = 'A'
union all
select distinct ID from A where GX = 'B'
) A group by ID having count (*) = 2
)
group by ID
union all
select ID, NUM = sum (NUM), NUM1 = sum (NUM1)
from A
where ID not in
(
select ID from
(
select distinct ID from A where GX = 'A'
union all
select distinct ID from A where GX = 'B'
) A group by ID having count (*) = 2
)
group by ID

--result
ID NUM NUM1
----------- ----------- -----------
3 300 60
5 150 45
1 100 50
2 200 30
4 100 30
6 200 30

(6 row (s) affected)

7058707

OK