50分求一SQL语句
A 表:是一张定单表
username acount
a 20
b 30
c 10
a 20
b 15
B是一张用户表:
username company
a baidu
b sohu
c nike
要求将得出一个销售排行,按acount的总和进行排序,要从B表中取出一些资料,如公司信息等，并且能得出行号
[解决办法]
select top 100--前一100名
b.username,b.姓名,b.生名,
销量=sum(acount)
from a inner join b on a.username=b.username
group by b.username,b.姓名,b.生名--定义以B表列名为组就行了
order by sum(acount) desc
[解决办法]
if object_id( 'A ') is not null drop table A
go
create table A (username varchar(10),acount int)
insert A
select 'a ', 20 union all
select 'b ', 30 union all
select 'c ', 10 union all
select 'a ', 20 union all
select 'b ', 15
go
if object_id( 'B ') is not null drop table B
go
create table B (username varchar(10),company varchar(10))
insert B
select 'a ', 'baidu ' union all
select 'b ', 'sohu ' union all
select 'c ', 'nike '
go
select OrderID=identity(int,1,1),a.username,company=min(b.company),total=sum(a.acount)
into #
from A,B
where A.username=B.username
group by a.username
order by total desc
select * from #
order by OrderID
drop table #
--结果
/*
orderid username company total
1abaidu40
2bsohu45
3cnike10
*/
