小弟急求一个问题!!

7058707

有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分组.

116657396

select ID,sum(case GX when 'A' then NUM else 0 end) as NUM,sum(NUM1) as NUM1 from A group by ID

cv4bf5d65fd

select ID ,GX,sum(NUM) num, sum(NUM1) num1
from tablename
group by ID ,GX

116657396

select ID,sum(case GX when 'A' then NUM else 0 end) as NUM,sum(NUM1) as NUM1 from A where exists(select 1 from A t where t.ID=ID and t.GX='A') group by ID
union
select ID,sum(NUM) as NUM,sum(NUM1) as NUM1 from A where not exists(select 1 from A t where t.ID=ID and t.GX='A') group by ID

sinexe

select id ,sum(case when gx = 'A' then num else 0 end),sum(num + num1)
from(
    select id ,gx,sum(num),sum(num1)
    from t
    group by id,gx) _x
group by id

cv4bf5d65fd

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
union all
select 6   ,'B',     200,    30
union all
select 6   ,'B',     200,    30



select ID ,GX,sum(NUM) num, sum(NUM1) num1
from a
group by ID , GX

wgfxman

CREATE TABLE A
(ID INT,
GX  CHAR(1),
NUM  INT,
NUM1 INT
)
GO

INSERT INTO A(ID,GX,NUM,NUM1)
    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
GO

SELECT ID,MAX(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

7058707

好象还没有把只有A或只有B的记录算出来啊。

wgfxman

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

这个也可以

wgfxman

你试一下我的这两种看看