1、group 不要 组内,组外排序;
组内排序(若order by it.addtime ASC 写入groupby 后,不创建临时表,那么是分组之后的排序):
组内排序(http://blog.csdn.net/shellching/article/details/8292338):
SELECT
*
FROM
(
SELECT
it.money,
it.user_id
FROM
table1 it
LEFT JOIN table2 p ON p.iuser_uid = it.user_id
LEFT JOIN table3 act ON act.activities_code = p.plat
WHERE
(act.pid = 14)
AND (
(it.addtime >= 1467734400)
AND (it.addtime < 1467993600)
)
AND (
(it.money >= 0)
AND (it.money <= 10000000000)
)
ORDER BY
it.addtime ASC
) AS i
GROUP BY
i.user_id;
组外排序:
SELECT
it.money,
it.user_id
FROM
table1 it
LEFT JOIN table2 p ON p.iuser_uid = it.user_id
LEFT JOIN table3 act ON act.activities_code = p.plat
WHERE
(act.pid = 14)
AND (
(it.addtime >= 1467734400)
AND (it.addtime < 1467993600)
)
AND (
(it.money >= 0)
AND (it.money <= 10000000000)
)
GROUP BY
it.user_id
ORDER BY
it.addtime ASC
2、having分组
错误的写法举例(mysql error: Invalid use of group function):
SELECT
sum(it.money) as sum_money,
it.user_id
FROM
table1 it
LEFT JOIN table2 p ON p.iuser_uid = it.user_id
LEFT JOIN table3 act ON act.activities_code = p.plat
WHERE
(act.pid = 14)
AND (
(it.addtime >= 1467734400)
AND (it.addtime < 1467993600)
)
AND (
(sum(it.money) >= 0)
AND (sum(it.money) <= 10000000000)
)
GROUP BY
it.user_id
;
对于上语句,应采用having来进行记录刷选(参考: http://www.jb51.net/article/32562.htm):
SELECT
sum(it.money) as sum_money,
it.user_id
FROM
table1 it
LEFT JOIN table2 p ON p.iuser_uid = it.user_id
LEFT JOIN table3 act ON act.activities_code = p.plat
WHERE
(act.pid = 14)
AND (
(it.addtime >= 1467734400)
AND (it.addtime < 1467993600)
)
GROUP BY
it.user_id
HAVING
(sum(it.money) >= 0)
AND (sum(it.money) <= 10000000000)
;