表结构:
礼物记录表:GiftRecord
ID,GiftName,PriceTotal,UserID会员表:Users
ID,UserName,****问题:
1、现在根据GiftRecord的PriceTotal字段来显示前10位排行,并根据UserID联合查询Users表中会员的相关信息,如用户名、头像、最后登录时间等2、在问题1的基础上加个条件,如PriceTotal只能大于5000
礼物记录表:GiftRecord
ID,GiftName,PriceTotal,UserID会员表:Users
ID,UserName,****问题:
1、现在根据GiftRecord的PriceTotal字段来显示前10位排行,并根据UserID联合查询Users表中会员的相关信息,如用户名、头像、最后登录时间等2、在问题1的基础上加个条件,如PriceTotal只能大于5000
表结构:
礼物记录表:GiftRecord
ID,GiftName,PriceTotal,UserID会员表:Users
ID,UserName,****问题:
1、现在根据GiftRecord的PriceTotal字段来显示前10位排行,并根据UserID联合查询Users表中会员的相关信息,如用户名、头像、最后登录时间等select top 10 a.ID,a.GiftName,a.PriceTotal,a.UserID,b.UserName,b.头像,b.最后登录时间
from GiftRecord a,Users b
where a.UserID=b.ID
order by PriceTotal desc
2、在问题1的基础上加个条件,如PriceTotal只能大于5000
select top 10 a.ID,a.GiftName,a.PriceTotal,a.UserID,b.UserName,b.头像,b.最后登录时间
from GiftRecord a,Users b
where a.UserID=b.ID AND PriceTotal>5000
order by PriceTotal desc
select top 10 * from GiftRecord a,Users b
where a.UserID=b.id and a.PriceTotal>5000
order by a.PriceTotal desc
换个思路不知行不行的通?!
select a.ID,a.GiftName,a.PriceTotal,a.UserID,b.UserName,b.头像,b.最后登录时间
from Users b inner join (select top 10* from GiftRecord where PriceTotal>5000 order by PriceTotal desc ) a
on a.UserID=b.ID
order by PriceTotal desc
top 10 *
from
GiftRecord a join Users b
on
a.UserID=b.id
order by
a.PriceTotal desc
2.select
top 10 *
from
GiftRecord a join Users b
on
a.UserID=b.id and a.PriceTotal>5000
order by
a.PriceTotal desc
所以排行榜用到了Group by,而不是各位说的直接用order by进行排序GiftRecord表数据如:
userid pricetotal
1 10
1 20
2 30
3 10
4 20
2 5