高手帮忙写一条sql语句！
表AA
id shop_id user_id shu
1 11 111 10
2 22 333 20
3 33 222 30
4 33 111 50
5 33 333 50
6 33 222 50
7 33 444 50
8 33 111 50
表BB
id aa bb cc bb ee
111 ………………………………
222 ………………………………
333 ………………………………
结果:
查询出AA表中user_id 次数最多的前3条记录
如 user_Id
111
222
333
数据
并关联到BB表中查出他的所有信息！！再统计出 每个user_Id 的总(shu)
如 user_Id shu
111 110
222 80
[解决办法]
select 表BB.*,t.[user_id],t.shu
from (select top 3 [user_id],sum(shu) as shu
from 表AA
group by [user_id]
order by count(*) desc)t,表BB
where t.[user_id]=表BB.id
[解决办法]
Select
A.*,
B.aa
From
(
Select
TOP 3
[user_Id],
SUM(shu) As shu
From
AA
Group By [user_Id]
Order By Count(*) Desc
) A
Inner Join
BB B
On A.[user_Id] = B.[id]
[解决办法]
:)
差不多的
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(id int,shop_id int,[user_id] int,shu int)
insert into tb(id,shop_id,[user_id],shu) values(1, 11, 111, 10)
insert into tb(id,shop_id,[user_id],shu) values(2, 22, 333, 20)
insert into tb(id,shop_id,[user_id],shu) values(3, 33, 222, 30 )
insert into tb(id,shop_id,[user_id],shu) values(4, 33, 111, 50)
insert into tb(id,shop_id,[user_id],shu) values(5, 33, 333, 50)
insert into tb(id,shop_id,[user_id],shu) values(6, 33, 222, 50)
insert into tb(id,shop_id,[user_id],shu) values(7, 33, 444, 50)
insert into tb(id,shop_id,[user_id],shu) values(8, 33, 111, 50)
go
select m.[user_id] , m.shu from
(
select [user_id] , sum(shu) as shu from tb group by [user_id]
) m,
(
select top 3 * from
(
select [user_id] , count(*) as cnt from tb group by [user_id]
) t
order by cnt desc
) n
where m.[user_id] = n.[user_id]
drop table tb
/*
user_id shu
----------- -----------
111 110
222 80
333 70
（所影响的行数为 3 行）
*/
[解决办法]
select 表BB.* , m.[user_id] , m.shu from 表BB,
(
select [user_id] , sum(shu) as shu from tb group by [user_id]
) m,
(
select top 3 * from
(
select [user_id] , count(*) as cnt from tb group by [user_id]
) t
order by cnt desc
) n
where 表BB.id = m.[user_id] and 表BB.id = n.[user_id]
[解决办法]
select 表BB.*,t.count_shu
from
(select top 3 user_id,sum(shu) as count_shu
from 表AA
group by user_id
order by count(count_shu) desc)as t,
表BB
where t.user_id=表BB.id
[解决办法]
那么快啊,我看见才写,等写出来你们都写好过了!
太牛了啊!
_______________________________
user_id 也是关键字?
