Sql 小间题~~计算结果不对

[allen_feng]

表A

id code name
1 11 111
2 22 222
3 33 333

表B

id ACode number status
1 11 100 1
2 11 150 1
3 22 80 1
4 22 90 1

表C

id ACode number status
1 11 50 2
2 22 60 2
3 11 110 2

我想要的结果为
id ACode name Stautsnumber1 statusnumber2
1 11 111 250 160
2 22 222 170 60

我写的SQL:
select a.code,a.name,case when b.status=1 then sum(b.number) end as statusnumber1 ,
case when c.status=2 then sum(c.number) end as statusnumber2
from a inner join b on a.code = b.Acode inner join c on a.code=c.Acode group by a.code,a.naem,b.status,c.status

语法没问题，但计算出来的结果却是末名其妙，完全不对，请各位大侠帮我看一下问题出在那里

[AcHerat]

select a.code,a.name,sum(case when b.status=1 then b.number end) as statusnumber1 ,
sum(case when c.status=2 then c.number end) as statusnumber2
from a inner join b on a.code = b.Acode inner join c on a.code=c.Acode group by a.code,a.naem,b.status,c.status

[飘零一叶]

SELECT A.code,A.name,B.number,C.number 
FROM 表A A JOIN
(SELECT ACode,status,SUM(number) AS number FROM 表B GROUP BY ACode,status) B ON A.code=B.ACode
JOIN
(SELECT ACode,status,SUM(number) AS number FROM 表C GROUP BY ACode,status) C ON A.code=C.ACode

[allen_feng]

计算出来的结果还是不对呀

[AcHerat]

[Quote=引用 3 楼 allen_feng 的回复:]
计算出来的结果还是不对呀
[/Quote]

select a.code,a.name,sum(case when b.status=1 then b.number else 0 end) as statusnumber1 ,sum(case when c.status=2 then c.number else 0 end) as statusnumber2
from a inner join b on a.code = b.Acode inner join c on a.code=c.Acode group by a.code,a.naem,b.status,c.status

[allen_feng]

[Quote=引用 1 楼 acherat 的回复:]
SQL code

select a.code,a.name,sum(case when b.status=1 then b.number end) as statusnumber1 ,
sum(case when c.status=2 then c.number end) as statusnumber2
from a inner join b on a.code = b.Acode i……
[/Quote]

CREATE TABLE [dbo].[表A](
[ID] [bigint] IDENTITY(1,1) ,
[code] [nvarchar](255) ,
[Name] [nvarchar](500))


CREATE TABLE [dbo].[表B](
[ID] [bigint] IDENTITY(1,1) ,
[Acode] [nvarchar](255) ,
[number] [int] ,
[status] [int])

CREATE TABLE [dbo].[表C](
[ID] [bigint] IDENTITY(1,1) ,
[Acode] [nvarchar](255) ,
[number] [int] ,
[status] [int])
INSERT 表A VALUES ('1','11')
INSERT 表A VALUES ('2','22')
INSERT 表A VALUES ('3','33')
INSERT 表A VALUES ('4','44')

INSERT 表B VALUES ('11',100,1)
INSERT 表B VALUES ('11',200,1)
INSERT 表B VALUES ('22',150,1)
INSERT 表B VALUES ('22',40,1)

INSERT 表C VALUES ('11',70,2)
INSERT 表C VALUES ('11',120,2)
INSERT 表C VALUES ('22',20,2)
INSERT 表C VALUES ('22',210,2)

select * from 表A
select * from 表B
select * from 表C

SELECT A.code,A.name,B.number,C.number
FROM 表A A JOIN
(SELECT ACode,status,SUM(number) AS number FROM 表B GROUP BY ACode,status) B ON A.code=B.ACode
JOIN
(SELECT ACode,status,SUM(number) AS number FROM 表C GROUP BY ACode,status) C ON A.code=C.ACode

计算的没有结果

[allen_feng]

引用错了，是二楼的计算没有结果

[allen_feng]

[Quote=引用 4 楼 acherat 的回复:]
引用 3 楼 allen_feng 的回复:
计算出来的结果还是不对呀



SQL code

select a.code,a.name,sum(case when b.status=1 then b.number else 0 end) as statusnumber1 ,sum(case when c.status=2 then c.number else 0 end) as ……
[/Quote]

你没试过吧，你看看结果

[飘零一叶]

[Quote=引用 5 楼 allen_feng 的回复:]
引用 1 楼 acherat 的回复:
SQL code

select a.code,a.name,sum(case when b.status=1 then b.number end) as statusnumber1 ,
sum(case when c.status=2 then c.number end) as statusnumber2
from a inner join b ……
[/Quote]

SELECT A.code,A.name,B.number,C.number  
FROM 表A A JOIN
(SELECT ACode,status,SUM(number) AS number FROM 表B GROUP BY ACode,status) B ON A.name=B.ACode
JOIN
(SELECT ACode,status,SUM(number) AS number FROM 表C GROUP BY ACode,status) C ON A.name=C.ACode
/*
code	name	number	number
1	11	300	190
2	22	190	230
*/

你的A表数据插入的有问题

[allen_feng]

[Quote=引用 2 楼 dlut_liuq 的回复:]
SQL code
SELECT A.code,A.name,B.number,C.number
FROM 表A A JOIN
(SELECT ACode,status,SUM(number) AS number FROM 表B GROUP BY ACode,status) B ON A.code=B.ACode
JOIN
(SELECT ACode,status,SUM(number)……
[/Quote]

这个是对的，谢谢哈