如何在查询中添加一些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

SQLFiddle演示

更新

要在总数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.

更新了SQLFiddle

您也可以在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屋！