遇到一个写sql的面试题

[船长ζ]

请问各位大大，这个sql怎么写，思路怎么样。新手没有思路。这个时间怎么判断。

[浩水浮生]

select user_id,price from t_reward where channel_type=1 and dateformat(create_time,‘%Y-%m’)=‘2016’ order by price limit 10;

[CS_草祭先生]

给个思路吧，那些一上来就给写法的人，你们是真的想帮他还是害他？授人以鱼不如授人以渔。 1.先根据时间查出2016年的打赏记录； 2.再根据用户分组查询取金额总数并having金额总数，并给予排序后的结果每行一个行号； 3.取前10得到最终结果。

[船长ζ]

引用 1 楼 wzs535131 的回复：

select user_id,price from t_reward where channel_type=1 and dateformat(create_time,‘%Y-%m’)=‘2016’ order by price limit 10;

大概意思我了解了，你这个没解决总用户打赏金额的计算，需要sum函数 和groupby groupby 和order by 能在一个函数中吗，top 10 能不能解决问题

[船长ζ]

select user_id,sumprice,create_time from(select a.user_id,sum(a.price) sumprice,create_time from mytest a where a.channel_type=1 group by a.user_id having date_format(a.create_time,'%Y-%m')=2016 ) stu order by sumprice desc limit 10;

[船长ζ]

引用 3 楼 船长ζ 的回复：

select user_id,sumprice,create_time from(select a.user_id,sum(a.price) sumprice,create_time from mytest a where a.channel_type=1 group by a.user_id having date_format(a.create_time,'%Y-%m')=2016 ) stu order by sumprice desc limit 10;

能否解决问题，还有没有更好的写法

[nayi_224]

注意细节 _status = 0 而且查询create_time会报错，结果也会错

select *
  from (select t1.*
          from (select t1.user_id, sum(t1.price) pc
                  from mytest t1
                 where t1.channel_type = 1
                   and t1._status = 0
                   and trunc(t1.create_time, 'y') = '2016' --视为date类型
                 group by t1.user_id) t1
         order by t1.pc desc) t1
 where rownum <= 10
--oracle

[船长ζ]

引用 5 楼 nayi_224 的回复：

注意细节 _status = 0 而且查询create_time会报错，结果也会错

select *
  from (select t1.*
          from (select t1.user_id, sum(t1.price) pc
                  from mytest t1
                 where t1.channel_type = 1
                   and t1._status = 0
                   and trunc(t1.create_time, 'y') = '2016' --视为date类型
                 group by t1.user_id) t1
         order by t1.pc desc) t1
 where rownum <= 10
--oracle

我又琢磨下，这样写。没有创建临时表 你看这种有问题吗 前提是千万级的数据 select user_id,sum(price) as sumprice,create_time from mytest where channel_type =1 and year(create_time) ='2016' group by user_id order by sumprice desc limit 10;

[船长ζ]

引用 6 楼 船长ζ 的回复：

[quote=引用 5 楼 nayi_224 的回复：]注意细节 _status = 0 而且查询create_time会报错，结果也会错

select *
  from (select t1.*
          from (select t1.user_id, sum(t1.price) pc
                  from mytest t1
                 where t1.channel_type = 1
                   and t1._status = 0
                   and trunc(t1.create_time, 'y') = '2016' --视为date类型
                 group by t1.user_id) t1
         order by t1.pc desc) t1
 where rownum <= 10
--oracle

我又琢磨下，这样写。没有创建临时表 你看这种有问题吗 前提是千万级的数据 select user_id,sum(price) as sumprice,create_time from mytest where channel_type =1 and year(create_time) ='2016' group by user_id order by sumprice desc limit 10;[/quote]对，加上and status =0