求一SQL查询语句
0
悬赏园豆：5
[已解决问题]
解决于 2010-04-03 10:27
有两张表,数据如上面的图
用SQL语句要查出结果,如下图
请各位大虾帮帮忙
最佳答案
0
select UserSerialNumber, FirstName, LastName, SUM(SalesAmount) from (
select a.UserSerialNumber, b.FirstName, b.LastName, a.SalesAmount
from Sales a
inner join BaseUser b
on a.UserSerialNumber = b.UserSerialNumber
)
group by UserSerialNumber, FirstName, LastName
收获园豆：5
强人!太对了.
其他回答(4)
0
select a.UserSerialNumber,b.FirstName,b.LastName,a.Amount1
from
(
select UserSerialNumber,SUM(SalesAmount) as Amount1 from sales
group by UserSerialNumber
) a
left join BaseUser b on a.UserSerialNumber=b.UserSerialNumber
select a.UserSerialNumber,b.FirstName,b.LastName,a.Amount1 from(select UserSerialNumber,SUM(SalesAmount) as Amount1 from sales group by UserSerialNumber) aleft join BaseUser b on a.UserSerialNumber=b.UserSerialNumber
0
declare @BaseUser table(UserSerialNumber nvarchar(20),FirstName nvarchar(50),LastName nvarchar(50),SaleAmount numeric(18,2))
insert into @BaseUser
select UserSerialNumber,FirstName, LastName,null
select BaseUser
update A
set SaleAmount = sum()
from @BaseUser A inner join Sales B on A.UserSerialNumber=B.UserSerialNumber
select * from @BaseUser
辛苦了
0
select t.no, t.fn, t.ln, sum(t1.account) as acount from baseuser t left join sales t1
on t.no = t1.no group by t.no,t.fn,t.ln
select t.userSerialNumber, t.fisrtName, t.lastName, sum(t1.account) as acount
from baseuser t left join sales on t.userSerialNumber = t1.userSerialNumber group by t.userSerialNumber,t. fisrtName,t.lastName
0
select UserSerialNumber,
FirstName,
LastName,
(select sum(s.SalesAmount) from sales s where s.UserSerialNumber=UserSerialNumber) as totalsales from BaseUser
应该可以，不可以问我
