好久没写sql语句了,求一语句,高手帮个忙

wzzaiwml 2011-01-27 10:33:18
表A
ID Name Quantity
1 A 10
1 B 15
2 A 20
2 B 18
2 C 12

表B
ID Type Info
1 AAA XXX
2 BBB YYY


求一查询语句,要求有 ID,Type, Info 以及每个ID Quantity的总数。结果样式如下
ID Type Info Sum(Quantity)
1 AAA XXX 25
2 BBB YYY 50

我用这样的写法 Select b.ID,b.Type,b.Info,Sum(a.Quantity)
From A a,B b
Where a.ID=b.ID

执行时报错。是不是Sum的用法有问题。很久没写过sql语句了,不记得用法了。请大家帮忙给个正确的。能实现上面的结果。谢谢了。
...全文
59 点赞 收藏 13
写回复
13 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
a402626926 2011-01-27
[Quote=引用 3 楼 fengyun142415 的回复:]

SQL code

Select b.ID,b.Type,b.Info,Sum(a.Quantity)
from A a,B b
where a.ID=b.ID
group by b.id
[/Quote]
正解
回复
javatemptation 2011-01-27
select B.id,B.type,B.info,SUM(A.quantity) as [SUM(Quantity)]
from A join B
on A.id = B.id
group by B.id,B.type,B.info;
回复
xiaoguanzhao 2011-01-27
/*建A表*/
CREATE TABLE A
(ID INT ,
NAME VARCHAR(20),
Quantity INTEGER
)
INSERT INTO A VALUES(1,'A',10);
INSERT INTO A VALUES(1,'B',15);
INSERT INTO A VALUES(2,'A',20);
INSERT INTO A VALUES(2,'B',18);
INSERT INTO A VALUES(2,'C',12);
/*建B表*/
CREATE TABLE B
(
ID INT ,
Type1 VARCHAR(20),
Info VARCHAR(20)
)
INSERT INTO B VALUES(1,'AAA','XXX');
INSERT INTO B VALUES(2,'BBB','YYY');
/*查询语句*/
SELECT t1.ID,T1.Type,T1.Info,SUM(Quantity)
FROM B T1 LEFT JOIN A T ON T.ID=T1.ID
GROUP BY t1.ID,T1.Type,T1.Info
ORDER BY ID;
回复
Dic4000 2011-01-27

create table A
(
ID int,
Name varchar(2),
Quantity int
)
go
create table B
(
ID int,
Type varchar(10),
Info varchar(10)
)
go
insert into a select 1,'A',10 union all
select 1,'b',15 union all
select 2,'a',20 union all
select 2,'b',18 union all
select 2,'c',12
go
insert into B select 1,'AAA','XXX' union all
select 2,'BBB','YYY'

go
select b.ID,Type,Info,t.Sum 'Sum(a.Quantity)' from B join
(select id,sum(quantity) 'Sum' from A group by id) t on t.id=b.id

回复
wzzaiwml 2011-01-27
哇,好心人真多啊。问题解决了。谢谢大家
Select b.ID,b.Type,b.Info,Sum(a.Quantity)
From A a,B b
Where a.ID=b.ID group by b.ID,b.Type,b.Info
回复
GoAwayZ 2011-01-27
Select b.ID,b.Type,b.Info,Sum(a.Quantity)
From A a,B b
Where a.ID=b.ID group by b.ID,b.Type,b.Info
回复
AcHerat 元老 2011-01-27
2# 的漏了两个字段,4# 补上了。
回复
王向飞 2011-01-27
Select b.ID,b.Type,b.Info, Quantity =(SELECT SUM(a.Quantity) FROM 表A a WHERE a.ID=b.ID group by a.ID)
From 表B b
回复
Oraclers 2011-01-27
select a.ID,a.Type,a.Info,b.Quantity
from TableB a join (select ID,sum(Quantity) as Quantity from TableA group by ID) b on a.ID=b.ID
order by a.ID
回复
AcHerat 元老 2011-01-27

Select b.ID,b.Type,b.Info,Sum(a.Quantity)
From A a,B b
Where a.ID=b.ID
group by b.ID,b.Type,b.Info
回复
fengyun142415 2011-01-27

Select b.ID,b.Type,b.Info,Sum(a.Quantity)
from A a,B b
where a.ID=b.ID
group by b.id
回复
AcHerat 元老 2011-01-27

Select b.ID,b.Type,b.Info,Sum(a.Quantity)
From A a,B b
Where a.ID=b.ID
group by b.ID
回复
fengyun142415 2011-01-27
group by a.id
回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2011-01-27 10:33
社区公告
暂无公告