select BllDetail.GoodsID, GoodsInfo.GoodsName,
BllDetail.Amount,
BllDetail.Quote, BllDetail.Money
from BllDetail
left join GoodsInfo on GoodsInfo.GoodsID=BllDetail.GoodsID
where BllDetail.BillID='243'上面的BllDetail表是一个明细表, BllDetail.GoodsID存着GoodsInfo的东西
查出来的东西是:
(重量)(单价)(金额)
GOODSID, GOODSNAME, Amount, Quote, Money
1001 aaaa 4 5 20
1001 aaaa 3 5 15
1002 bbbb 6 3 34我想让数据变成:
(重量)(单价)(金额) (件数)
GOODSID, GOODSNAME, Amount, Quote, Money Number
1001 aaaa 7 5 35 2
1002 bbbb 6 3 34 1要怎么写? 急等(注:GoodsName存的是汉字,怕一起group by 会有问题)
BllDetail.Amount,
BllDetail.Quote, BllDetail.Money
from BllDetail
left join GoodsInfo on GoodsInfo.GoodsID=BllDetail.GoodsID
where BllDetail.BillID='243'上面的BllDetail表是一个明细表, BllDetail.GoodsID存着GoodsInfo的东西
查出来的东西是:
(重量)(单价)(金额)
GOODSID, GOODSNAME, Amount, Quote, Money
1001 aaaa 4 5 20
1001 aaaa 3 5 15
1002 bbbb 6 3 34我想让数据变成:
(重量)(单价)(金额) (件数)
GOODSID, GOODSNAME, Amount, Quote, Money Number
1001 aaaa 7 5 35 2
1002 bbbb 6 3 34 1要怎么写? 急等(注:GoodsName存的是汉字,怕一起group by 会有问题)
select BllDetail.GoodsID,GoodsName=max( GoodsInfo.GoodsName),
Amount=sum(isnull(BllDetail.Amount,0)),
Quote=max(BllDetail.Quote), Money=sum(isnull(BllDetail.Money,0))
from BllDetail
left join GoodsInfo on GoodsInfo.GoodsID=BllDetail.GoodsID
where BllDetail.BillID='243'
group by BllDetail.GoodsID
把第二部分的数据插入新表
然后再Group by 新表内的数据!
sum(BllDetail.Amount),BllDetail.Quote, sum(BllDetail.Money),count(BllDetail.GoodsID)
from BllDetail
left join GoodsInfo on GoodsInfo.GoodsID=BllDetail.GoodsID
where BllDetail.BillID='243'
group by BllDetail.GoodsID,BllDetail.GoodsName,BllDetail.Quote
userTest
(GOODSID int , GOODSNAME nvarchar(50), Amount int , Quote int , [Money] int)
insert userTest
select
1001 , 'aaaa ' , 4 , 5 , 20
union all
select
1001 ,'aaaa ', 3 , 5 , 15
union all
select
1002 , 'bbbb ' , 6 , 3 , 34
insert userTest
select
10011 , '中国人 ' , 4 , 5 , 20
union all
select
10011 , '中国人 ' , 4 , 5 , 20
union all
select
10011 , '中国人 ' , 4 , 5 , 20
select * from userTest
insert userTest
select
100111 , '日本人 ' , 4 , 5 , 20
union all
select
100111 , '日本人 ' , 4 , 5 , 20
union all
select
100111 , '日本人 ' , 4 , 5 , 20
select * from userTestselect GOODSID ,GOODSNAME,sum(Amount) Amount, Quote,sum([money]) money,count(GOODSNAME) Number
from userTest group by GOODSID ,GOODSNAME,Quote
select GOODSID,count(*)as number,sum(Amount)as Amount ,sum(money)as money into #aa from T group by (GOODSID)select a.GoodSID,b.GOODSNAME,a.Amount,b.Quote,a.money,a.number from #aa a left join
T b on a.GOODSID=b.GOODSID
sum(BllDetail.Amount),
BllDetail.Quote, sum(BllDetail.Money),count(BllDetail.GoodsID) Number
from BllDetail
left join GoodsInfo on GoodsInfo.GoodsID=BllDetail.GoodsID
where BllDetail.BillID='243'
group by BllDetail.GoodsID,GoodsInfo.GoodsName,BllDetail.Quote
#userTest
(GOODSID int , GOODSNAME nvarchar(50), Amount int , Quote int , [Money] int)
insert #userTest
select
1001 , 'aaaa ' , 4 , 5 , 20
union all
select
1001 ,'aaaa ', 3 , 5 , 15
union all
select
1002 , 'bbbb ' , 6 , 3 , 34
insert #userTest
select
10011 , '中国人 ' , 4 , 5 , 20
union all
select
10011 , '中国人 ' , 4 , 5 , 20
union all
select
10011 , '中国人 ' , 4 , 5 , 20
select * from #userTest
insert #userTest
select
100111 , '日本人 ' , 4 , 5 , 20
union all
select
100111 , '日本人 ' , 4 , 5 , 20
union all
select
100111 , '日本人 ' , 4 , 5 , 20
select * from #userTestselect GOODSID as 编号 ,max(GOODSNAME) as 名称,sum(Amount) as 数量, max(Quote) as 单价,sum([money]) as 金额,count(GOODSid) as 记录数
from #userTest group by GOODSID --,GOODSNAME,Quote--删除临时表
drop table #userTest
========================
结果:(所影响的行数为 3 行)
(所影响的行数为 3 行)GOODSID,GOODSNAME,Amount,Quote,Money
1001,aaaa ,4,5,20
1001,aaaa ,3,5,15
1002,bbbb ,6,3,34
10011,中国人 ,4,5,20
10011,中国人 ,4,5,20
10011,中国人 ,4,5,20(所影响的行数为 6 行)
(所影响的行数为 3 行)GOODSID,GOODSNAME,Amount,Quote,Money
1001,aaaa ,4,5,20
1001,aaaa ,3,5,15
1002,bbbb ,6,3,34
10011,中国人 ,4,5,20
10011,中国人 ,4,5,20
10011,中国人 ,4,5,20
100111,日本人 ,4,5,20
100111,日本人 ,4,5,20
100111,日本人 ,4,5,20(所影响的行数为 9 行)编号,名称,数量,单价,金额,记录数
1001,aaaa ,7,5,35,2
1002,bbbb ,6,3,34,1
10011,中国人 ,12,5,60,3
100111,日本人 ,12,5,60,3(所影响的行数为 4 行)