首页新闻找找看学习计划

求一SQL查询语句

0
悬赏园豆:5 [已解决问题] 解决于 2010-04-03 10:27

有两张表,数据如上面的图

用SQL语句要查出结果,如下图

请各位大虾帮帮忙

bicabo的主页 bicabo | 初学一级 | 园豆:160
提问于:2010-03-27 23:42
< >
分享
最佳答案
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
Gray Zhang | 专家六级 |园豆:17610 | 2010-03-28 00:00
强人!太对了.
bicabo | 园豆:160 (初学一级) | 2010-04-03 10:27
其他回答(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

深水的猫 | 园豆:110 (初学一级) | 2010-03-28 18:01
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

lily.zheng | 园豆:151 (初学一级) | 2010-03-29 13:45
辛苦了
支持(0) 反对(0) bicabo | 园豆:160 (初学一级) | 2010-04-03 10:28
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

zZ__Zz | 园豆:210 (菜鸟二级) | 2010-03-29 18:06
0

select UserSerialNumber,

    FirstName,

    LastName,

    (select sum(s.SalesAmount) from sales s where s.UserSerialNumber=UserSerialNumber) as totalsales from BaseUser  

应该可以,不可以问我

loogn | 园豆:329 (菜鸟二级) | 2010-04-01 16:19
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册