sum 求和问题,再次求解!
有如下视图： jhview (goodsID,goodsNAME,goodsSL,riqi)
记录有如下：
goodsID goodsNAME goodsSL goodsDJ goodsJE goodsGG riqi
1 钢笔 20 2 40 长城牌 2004-12-15
1 钢笔 20 2 40 长城牌 2005-12-05
2 笔记本 30 1 30 大号 2005-12-15
3 铅笔 20 1 20 长细 2005-12-20
4 钢笔 10 4 40 英雄牌 2005-12-25
现在想计算sum(goodsSL),且过滤goodsID 的重复项
即得出sum(goodsSL),sum(goodsJE),名字相同的物品它们的ID号(goodsID)和规格 (goodsGG)不相同.现在要显示如下视图:
goodsID goodsNAME goodsSL goodsJE goodsGG
1 钢笔 40 80 长城牌
2 笔记本 30 30 大号
3 铅笔 20 20 长细
4 钢笔 10 40 英雄牌
请帮忙写出语句 谢谢!
[解决办法]
select goodsID, goodsNAME, sum(goodSL) as goodSL, sum(goodJE) as goodsJE, goodsGG
group by goodsID, goodsNAME, goodsSL , goodsJE , goodsGG,
order by goodsID
[解决办法]
create table tb(goodsID int,goodsNAME varchar(50),goodsSL int, goodsJE int, goodsGG varchar(50))
insert into tb values(1, '钢笔 ', 20, 40, '长城牌 ' )
insert into tb values(1, '钢笔 ', 20, 40, '长城牌 ')
insert into tb values(2, '笔记本 ', 30, 30, '大号 ')
insert into tb values(3, '铅笔 ', 20, 20, '长细 ')
insert into tb values(4, '钢笔 ', 10, 40, '英雄牌 ')
SELECT goodsID, goodsNAME, SUM(goodsSL) AS goodsSL, SUM(goodsJE) AS goodsJE, goodsGG FROM tb GROUP BY goodsID, goodsNAME, goodsGG ORDER BY goodsID
drop table tb
