需求,有一个数据表,专门用来记录用户金币花费记录的:
ID UserID Date Count Desp ---- (记录流水号,用户ID整型,日期-2009-11-05-字符串,金币数量,描述)
1 101 2009-11-04 3 ''
2 101 2009-11-05 3 ''
3 101 2009-11-05 3 ''
4 102 2009-11-05 3 ''
5 101 2009-11-05 3 ''
6 103 2009-11-05 5 ''
一个用户一天可能有多条消费记录,即Date对于一个用户来说,可能有多条相同的。要求:用一个SQL,求出给定某天消费金币和的最大的用户的ID;
比如:给定2009-11-05,则结果是101?请求各位,应该如何写?
ID UserID Date Count Desp ---- (记录流水号,用户ID整型,日期-2009-11-05-字符串,金币数量,描述)
1 101 2009-11-04 3 ''
2 101 2009-11-05 3 ''
3 101 2009-11-05 3 ''
4 102 2009-11-05 3 ''
5 101 2009-11-05 3 ''
6 103 2009-11-05 5 ''
一个用户一天可能有多条消费记录,即Date对于一个用户来说,可能有多条相同的。要求:用一个SQL,求出给定某天消费金币和的最大的用户的ID;
比如:给定2009-11-05,则结果是101?请求各位,应该如何写?
select userid,sum(count) total from table
group by userid
where date=to_date(&date,'yyyy-mm-dd')
) group by userid
(select userid,sum(count) c from tt
where date='2009-11-05'
group by userid
order by c desc)
where rownum=1
select userid,dense_rank()over(order by c desc)dk
from(select userid,sum(count)c
group by userid)
)where dk=1
select userid from(
select userid,dense_rank()over(order by c desc)dk
from(select userid,sum(count)c
where date='2009-11-05'
group by userid)
)where dk=1
select a.*,dense_rank()over(order by b.c desc)dk from userinfo a,
(select userid,sum("count")c from table1 where "date"='2009-11-05' group by userid)b
where a.id=b.userid)
where dk=1;select * from userinfo a where exists(
select 1 from(
select userid,dense_rank()over(order by c desc)dk
from(select userid,sum(count)c
from table1
where date='2009-11-05'
group by userid)
)where dk=1 and userid=a.id);