问题:现有如下数据表,
用户表: Members(qqnumber,userid);
充值表: Payment(payid,userid,paytime,amount);
查询2012年各个月充值最多的用户的qqnumber,要求根据qqnumber进行汇总(提示:一个qqnumber可能会有多个游戏角色数据,多条充值记录;paytime为充值时间,),请写出SQL语句。
创建的表如下。
Members表
Payment表
查询语句如下:
方法一:
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
)
=
'2016'
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
)
=
2016
GROUP
BY
m
.
qqnumber
,
MONTH
)
AS
t
GROUP
BY
t
.
month;