如何按一个表中的元素分组并按 2 列分组? [英] how can I group by elements from one table and group by it by 2 columns?

问题描述

我有一个结构如下的表:

I have a table with a structure like this:

table_id | user_id |   text   | number_id | start_time
1            1        gdsgds       8        2015-10-11 07:14:44
2            2        vcxvc        8        2015-10-11 07:20:44
3            4        ewgs         8        2015-10-12 09:19:22
4            7        vvvcc        8        2015-10-13 18:12:23
etc.

我需要编写一个查询，该查询将返回每个数字上每天显示的文本数量.到目前为止，我有以下查询:

I need to write a query that will return me the number of texts displayed each day on each number. So far I have the following query:

SELECT  * 
FROM 
(
    SELECT  DATEDIFF(now(), start_time) AS days_ago, 
            COUNT(table_id)             AS num_texts 
    FROM    TABLE 
    GROUP BY DATE(start_time)
) 
WHERE 
(
    days_ago <= 7 
AND days_ago > 0
)

这个查询返回一个表:

days_ago  |  num_texts
   0      |     2
   1      |     3
   2      |     4
   3      |     1

这几乎可以正常工作，但我也需要将其除以 number_id...我该怎么做?

and that works almost fine, but I need to divide it by number_id too... How can I do it?

推荐答案

SELECT  * 
FROM 
(
    SELECT  DATEDIFF(now(), start_time) AS days_ago, 
            number_id, 
            COUNT(table_id)             AS num_texts 
    FROM    TABLE 
    GROUP BY DATE(start_time), number_id 
) 
WHERE 
    days_ago <= 7 
AND days_ago > 0

这篇关于如何按一个表中的元素分组并按 2 列分组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！