题目如下,
有两个数据表分表是,
用户表: Members(qqnumber,userid);
充值表: Payment(payid,userid,paytime,amount);
查询2012年各个月充值最多的用户的qqnumber,要求根据qqnumber进行汇总(提示:一个qqnumber可能会有多条充值记录;paytime为充值时间,amount为充值金额),请写出SQL语句 。
首先创建两个表:
![](https://i-blog.csdnimg.cn/blog_migrate/5482924e578bf573ec0b23f0f6d7f0ce.png)
![](https://i-blog.csdnimg.cn/blog_migrate/66b33f25daed732a0e69693c1ddb09f4.png)
然后创建存储过程,往数据表里面插入测试数据:
![](https://i-blog.csdnimg.cn/blog_migrate/0eb96ddfbd98ef905d47116ac9773088.png)
![](https://i-blog.csdnimg.cn/blog_migrate/28d6089d2da6cef64045ed63ae38f0a7.png)
方法一:
SELECT
MAX( amount ) , t.qqnumber, t.MONTH
FROM (
SELECT
MONTH( a.paytime )
MONTH , b.qqnumber,
SUM( a.amount ) amount
FROM payment a, members b
WHERE a.userid = b.userid AND YEAR( a.paytime ) = '2012' GROUP BY b.qqnumber, MONTH
WHERE a.userid = b.userid AND YEAR( a.paytime ) = '2012' GROUP BY b.qqnumber, MONTH
) AS t GROUP BY t.MONTH;
方法二:
SELECT
MAX( amount ) , t.qqnumber, t.month
FROM (
SELECT
MONTH( p.paytime ) AS
MONTH ,
SUM( p.amount ) AS amount, m.qqnumber AS qqnumber
FROM Members m JOIN Payment p ON m.userid = p.userid
WHERE YEAR( p.paytime ) =2012 GROUP BY m.qqnumber, MONTH
FROM Members m JOIN Payment p ON m.userid = p.userid
WHERE YEAR( p.paytime ) =2012 GROUP BY m.qqnumber, MONTH
) AS t
GROUP BY t.month;
最后查询到的结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/1e541a50304d7de6293761bbfe16820b.png)
本文为作者原创,转载请注明出处,不对之处欢迎指正,请留言!