小弟急求一个问题!!
有A表里面有
ID         GX         NUM       NUM1
1           A           100         50
2           A           200         30
3           A           300         40
3           B           250         20
4           B           100         30
5           B           200         20
5           A           150         25
6           B           200         30
现在要按ID合计NUM   和NUM1的值,如果ID相同GX有A和B两条记录的
NUM的值取A的值,NUM1的值取A和B加起来的值   在按ID分组.

------解决方案--------------------
SELECT ID,ISNULL(NNUM,NUM),SUM(NUM1) FROM
(SELECT M.ID,M.GX,M.NUM,M.NUM1,N.GX AS NGX,N.NUM AS NNUM FROM A M LEFT JOIN A N ON M.ID=N.ID AND N.GX= 'A ') T
GROUP BY ID,ISNULL(NNUM,NUM)
ORDER BY ID

这个也可以
------解决方案--------------------
--不知對不對

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)