表格概述:
2列
i_trans_to和i_trans_amnt
i_trans_to由ID组成,i_trans_amnt由用户在账户中存入的金额组成
用户只能存入这些金额300175,75,40,20(因此i_trans_amnt列将只包含这些值)
IDS可能有多个条目,因为用户可以多次存款。
我想要一个将选择的查询
1.所有存款总额超过500的用户
2.在所有这些用户中,查询必须告知他进行了多少类型的条目(例如:5个300卢比的条目和2个175卢比的条目)
我成功地设计了查询,除了用户必须存款超过500卢比这一限制这是查询
SELECT DISTINCT i_trans_to,
SUM( i_trans_amnt ),
(SELECT COUNT(*)
FROM transac
WHERE i_trans_amnt=300 AND transac.i_trans_to=current.i_trans_to) AS level1,
(SELECT COUNT(*)
FROM transac
WHERE i_trans_amnt=175 AND transac.i_trans_to=current.i_trans_to) AS level2,
(SELECT COUNT(*)
FROM transac
WHERE i_trans_amnt=75 AND transac.i_trans_to=current.i_trans_to) AS level3,
(SELECT COUNT(*)
FROM transac
WHERE i_trans_amnt=40 AND transac.i_trans_to=current.i_trans_to) AS level4,
(SELECT COUNT(*)
FROM transac
WHERE i_trans_amnt=40 AND transac.i_trans_to=current.i_trans_to) AS level5,
(SELECT COUNT(*)
FROM transac
WHERE i_trans_amnt=20 AND transac.i_trans_to=current.i_trans_to) AS level6
FROM transac as current
WHERE SUM( current.i_trans_amnt )>500
GROUP BY i_trans_to
我试着在查询的末尾添加一个where子句,在该子句中我写了SUM(I_trans_amnt)>500,但这给了我一个错误。
有什么建议吗?
我已经更改了您的查询,您可以使用一组case语句获得level
总数,这将加快您的查询速度。
SUM( i_trans_amount)
可以在HAVING
子句中检查,有关此的详细信息,请参阅Sylvain Leroux的回答
SELECT
i_trans_to,
SUM( i_trans_amnt ),
count(CASE WHEN i_trans_amnt=300 THEN 1 END ) AS level1,
count(CASE WHEN i_trans_amnt=175 THEN 1 END ) AS level2,
count(CASE WHEN i_trans_amnt=75 THEN 1 END ) AS level3
FROM
transac as current
HAVING
SUM( i_trans_amnt ) > 500
GROUP BY
i_trans_to
我编写这个查询的方法是,首先创建一个查询,列出所有存放的总金额超过500 amnt的ID。然后,我在联接中使用该查询来只保留那些(所有其他ID都不会通过join条件)。然后,我只需在两列上进行分组,就可以显示每个ID、每个金额和存款数量。
SELECT
transac.i_trans_to,
transac.i_trans_amnt,
COUNT(0) AS number_of_times_amount_was_deposited
FROM
transac
JOIN (
SELECT
i_trans_to
FROM
transac
GROUP BY
i_trans_to
HAVING
SUM(i_trans_amnt) > 500
) AS large_depositors ON transac.i_trans_to = large_depositors.i_trans_to
GROUP BY
i_trans_to,
i_trans_amnt
查看WHERE vs HAVING(请将此答案归功于该帖子上的Quassnoi):
WHERE应用于GROUP BY之前,HAVING应用于之后(并且可以过滤聚合)。
聚合函数是SUM()
、COUNT()
以及所有在行组上工作的函数:http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html