一个SQL能不能统计出来这样的数据？

[Mapleleaf123]

表A

id name
1 a
2 b
3 c

表B
id score bid
1 1 1
2 5 1
3 2 3
4 2 1

结果,在表B中计算出相应分值，并按分值排序

id name score
1 a 8
3 c 2
2 b 0

[gogodiy]

CREATE TABLE t1
(
	id INT,
	NAME VARCHAR(2)
)
INSERT INTO t1
SELECT 1,'a' UNION ALL
SELECT 2,'b' UNION ALL
SELECT 3,'c'
CREATE TABLE t2
(
	id INT,
	score INT,
	aid INT
)
INSERT INTO t2
SELECT 1,1,1 UNION ALL
SELECT 2,5,1 UNION ALL
SELECT 3,2,3 UNION ALL
SELECT 4,2,1

;WITH aaa AS
(
	SELECT	aid,
			SUM(score) AS score
	FROM	t2 
	GROUP BY aid
)
SELECT	a.id,
		a.NAME,
		isnull(b.score,0) AS score
FROM	t1 AS a LEFT JOIN
		aaa AS b ON a.id=b.aid

id NAME score 1 a 8 2 b 0 3 c 2

[jack15850798154]

CREATE TABLE T_CSDN_ONE
(
	ID INT IDENTITY(1,1) PRIMARY KEY,
	[NAME] VARCHAR(20)
)

INSERT INTO T_CSDN_ONE
SELECT 'a'
UNION ALL
SELECT 'b'
UNION ALL
SELECT 'c'



CREATE TABLE T_CSDN_TWO
(
   ID INT IDENTITY(1,1) PRIMARY KEY,
   SCORE INT,
   BID INT
)


INSERT INTO T_CSDN_TWO
SELECT 1,1
UNION ALL
SELECT 5,1
UNION ALL
SELECT 2,3
UNION ALL
SELECT 2,1


SELECT * FROM (
SELECT a.ID,a.[NAME],SUM(ISNULL(B.SCORE,0)) AS SCORE FROM T_CSDN_ONE a 
LEFT JOIN T_CSDN_TWO b ON a.ID=b.BID
GROUP BY a.ID,a.[NAME]
) T ORDER BY T.SCORE DESC

[lcawen]

楼上已经给出回答了，在此，谢谢楼主和楼上各位的回复，本人也遇到了同样的问题

[唐诗三百首]

create table 表A(id int, name varchar(5))

insert into 表A
select 1, 'a' union all
select 2, 'b' union all
select 3, 'c'

create table 表B(id int, score int, bid int)

insert into 表B
select 1, 1, 1 union all
select 2, 5, 1 union all
select 3, 2, 3 union all
select 4, 2, 1


select a.id,
       a.name,
       isnull(b.score,0) 'score'
 from 表A a
 left join
 (select bid,
         sum(score) 'score'
  from 表B group by bid) b
 on a.id=b.bid
 order by isnull(b.score,0) desc

/*
id          name  score
----------- ----- -----------
1           a     8
3           c     2
2           b     0

(3 row(s) affected)
*/