sum 求和问题,再次求解!
有如下视图： jhview (goodsID,goodsNAME,goodsSL,goodsDJ,goodsJE,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),sum(goodsJE)且过滤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(goodsSL) , sum(goodsJE ) , goodsGG
from jhview group by goodsID
[解决办法]
select
goodsID,
goodsNAME,
sum(goodsSL),
sum(goodsJE ),
goodsGG
from jhview
group by goodsID
[解决办法]
--改下
select
goodsID,
goodsNAME,
sum(goodsSL),
sum(goodsJE ),
goodsGG
from jhview
group by goodsID,goodsNAME,goodsGG
[解决办法]
create table #jhview (goodsID int,goodsNAME varchar(100),goodsSL int,goodsDJ int,goodsJE int,goodsGG varchar(100),riqi datetime)
insert into #jhview select 1, '钢笔 ',20,2,40, '长城牌 ', '2004-12-15 '
insert into #jhview select 1, '钢笔 ',20,2,40, '长城牌 ', '2005-12-05 '
insert into #jhview select 2, '笔记本 ',30,1,30, '大号 ', '2005-12-15 '
insert into #jhview select 3, '铅笔 ',20,1,20, '长细 ', '2005-12-20 '
insert into #jhview select 4, '钢笔 ',10,4,40, '英雄牌 ', '2005-12-25 '
select
goodsID,
goodsNAME,
sum(goodsSL) as goodsSL,
sum(goodsJE ) as goodsJE,
goodsGG
from #jhview
group by goodsID,goodsNAME,goodsGG
drop table #jhview
[解决办法]
对了又写错了
select goodsID , goodsNAME , sum(goodsSL) , sum(goodsJE ) , goodsGG
from jhview group by goodsID,goodsNAME,goodsGG 应该这样 ,不好意思啊
[解决办法]
--这种写法有新意哟，既然楼主开了贴，多写个方案讨论一下。
select goodsID , min(goodsNAME),sum(goodsSL), sum(goodsJE ) ,min(goodsGG)
from jhview group by goodsID
[解决办法]
select goodsID,goodsName,goodsSL=sum(goodsSL),goodsJE=sum(goodsJE),goodsGG from jhview group by goodsID,goodsName,goodsGG
[解决办法]
select goodsID , goodsNAME , sum(goodsSL) , sum(goodsJE ) , goodsGG
from jhview group by goodsID
[解决办法]
select a.goodsID,b.goodsName,a.sum(goodsSl),a.sum(goodsJE),b.goodsGG from (select goodsID,sum(goodsSl),sum(goodsJE) from jhview ) a left join jhview b on a.goodsID=b.goodsID
[解决办法]
select goodsID , goodsNAME , sum(goodsSL) , sum(goodsJE ) , goodsGG
from jhview group by goodsID
[解决办法]
select
goodsID,
goodsNAME,
sum(goodsSL),
sum(goodsJE ),
goodsGG
from jhview
group by goodsID,goodsNAME,goodsGG
