如何在查询中添加一些restrictios? [英] How to add a few restrictios to a query?
问题描述
我在语法上有困难...
I have difficulty with syntax...
这是我的查询:
SELECT t.diapason,
Count(*) AS 'number_of_users'
FROM (SELECT CASE
WHEN amount < 200 THEN '0-200'
WHEN amount >= 200 THEN '200 +'
end AS diapason
FROM (SELECT Sum(amount) AS amount
FROM payments) p) t
GROUP BY t.diapason
ORDER BY number_of_users DESC;
但是现在我只需要选择在'2018-01-01'和'2018-01-12'之间具有activity.login_time的用户.
But now I need to select only users which had activity.login_time between '2018-01-01' and'2018-01-12'.
所以,我想我应该使用INNER JOIN并设置一段时间.怎么办?
So, I think I should use INNER JOIN and set period of time. Bu how?
我的桌子:
activity
user_id login_time
1 01.01.2018
2 01.01.2018
3 03.01.2018
4 30.02.2018
payments
user_id amount payment_time
1 50 10.12.2017
1 200 09.12.2017
2 40 08.08.2017
我应该在查询中进行哪些更改以添加activity.login_time?
what should I change in my query to add activity.login_time?
期间01.01.2018-12.01.2018的输出
Output for period 01.01.2018-12.01.2018
diapason number_of_users
0-200 2
200+ 1
推荐答案
我理解您的问题.在01.01.2018-12.01.2018期间,您有3个用户(user_id = 1,2,3)登录.在这些用户中,user_id 1进行了2次付款,总计250次,user_id 2进行了1次付款,共40次付款,user_id 3进行了0次付款,因此其总值为0.因此,在0-200
范围内有2个值,在200 +
.如果是正确的理解,此查询将为您提供所需的结果:
I understand your question as this. You had 3 users (user_id=1,2,3) login in the period 01.01.2018-12.01.2018. Of those users, user_id 1 made 2 payments totalling 250, user_id 2 made 1 payment of 40, and user_id 3 made 0 payments so their total is 0. Hence there are 2 values in the range 0-200
, and 1 in the range 200 +
. If that is the correct understanding, this query will give you the desired results:
SELECT CASE
WHEN amount < 200 THEN '0-200'
WHEN amount >= 200 THEN '200 +'
END AS diapason,
COUNT(*) AS number_of_users
FROM (SELECT a.user_id, COALESCE(SUM(p.amount), 0) AS amount
FROM activity a
LEFT JOIN payments p ON p.user_id = a.user_id
WHERE a.login_time BETWEEN '01.01.2018' AND '12.01.2018'
GROUP BY a.user_id) p
GROUP BY diapason;
输出:
diapason number_of_users
0-200 2
200 + 1
更新
要在总数number_of_users
中添加另一行,只需在GROUP BY
子句中添加WITH ROLLUP
:
To add another row with the total number_of_users
, just add WITH ROLLUP
to the GROUP BY
clause:
SELECT CASE
WHEN amount < 200 THEN '0-200'
WHEN amount >= 200 THEN '200 +'
END AS diapason,
COUNT(*) AS number_of_users
FROM (SELECT a.user_id, COALESCE(SUM(p.amount), 0) AS amount
FROM activity a
LEFT JOIN payments p ON p.user_id = a.user_id
WHERE a.login_time BETWEEN '01.01.2018' AND '12.01.2018'
GROUP BY a.user_id) p
GROUP BY diapason WITH ROLLUP
输出:
diapason number_of_users
0-200 2
200 + 1
(null) 3
在您的应用程序框架中,您可以使用diapason
值为NULL
的事实来代替输出Total
之类的东西.
In your application framework you can use the fact that the diapason
value is NULL
to output something like Total
instead.
您也可以在MySQL中执行相同的操作(请参见 SQLFiddle )通过将此查询包装为子查询并在diapason
列上使用COALESCE
.在这种情况下,输出将是:
You can also do the same in MySQL (see this SQLFiddle) by wrapping this query up as a subquery and using a COALESCE
on the diapason
column. In that case the output would be:
diapason number_of_users
0-200 2
200 + 1
Total 3
这篇关于如何在查询中添加一些restrictios?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!