mySql：count列中具有相同数据的行数

概述我试图选择表中的所有内容,并计算表中具有相同数据的行数.SELECT *, COUNT(thedate) daycount FROM `table` ORDER BY thedate DESC 我希望有一个查询输出与该日期相关的日期和行数,循环输出将是这样的： Jan 1, 2000 (2 rows) col1, col2, col3, col4

我试图选择表中的所有内容,并计算表中具有相同数据的行数.

SELECT *,COUNT(thedate) daycount FROM `table` ORDER BY thedate DESC

我希望有一个查询输出与该日期相关的日期和行数,循环输出将是这样的：

Jan 1,2000 (2 rows)
col1,col2,col3,col4
col1,col4

Jan 1,2000 (3 rows)
col1,2000 (6 rows)
col1,col4

等等…

这有意义吗？

最佳答案如果您有一个如下所示的表：

CREATE table yourtable(    datefIEld DATETIME,col1 VARCHAR(20),col2 INT NOT NulL,col3 tinyint NOT NulL,col4 CHAR(5));

并且您希望每个给定日期重复col1 .. col4的计数,您将运行此查询

SELECT    COUNT(datefIEld) datefIEld_count,left(all_fIElds,10) datefIEld,SUBSTR(all_fIElds,11) all_other_fIEldsFROM(    SELECT        DATE(datefIEld) datefIEld,CONCAT(DATE(datefIEld),'|',COALESCE(col1,'< NulL >'),COALESCE(col2,COALESCE(col3,COALESCE(col4,'|') all_fIElds    FROM         yourtable) AGROUP BY all_fIElds;

以下是一些示例数据和查询结果：

MysqL> DROP table IF EXISTS yourtable;query OK,0 rows affected (0.04 sec)MysqL> CREATE table yourtable    -> (    ->     datefIEld DATETIME,->     col1 VARCHAR(20),->     col2 INT,->     col3 tinyint,->     col4 CHAR(5)    -> );query OK,0 rows affected (0.11 sec)MysqL> INSERT INTO yourtable VALUES    -> (DATE(Now() - INTERVAL 1 DAY),'rolando',4,3,'angel'),-> (DATE(Now() - INTERVAL 1 DAY),NulL,-> (DATE(Now() - INTERVAL 2 DAY),2,'eDWards'),-> (DATE(Now() - INTERVAL 3 DAY),5,'pamela','angel')    -> ;query OK,22 rows affected,3 warnings (0.03 sec)Records: 22  Duplicates: 0  Warnings: 3MysqL> SELECT * FROM yourtable;+---------------------+---------+------+------+-------+| datefIEld           | col1    | col2 | col3 | col4  |+---------------------+---------+------+------+-------+| 2011-06-30 00:00:00 | rolando |    4 |    3 | angel || 2011-06-30 00:00:00 | rolando |    4 |    3 | angel || 2011-06-30 00:00:00 | rolando |    4 |    3 | angel || 2011-06-30 00:00:00 | rolando |    4 | NulL | angel || 2011-06-30 00:00:00 | rolando |    4 | NulL | angel || 2011-06-29 00:00:00 | rolando |    4 |    2 | angel || 2011-06-29 00:00:00 | rolando |    4 |    2 | angel || 2011-06-29 00:00:00 | rolando |    4 |    2 | angel || 2011-06-29 00:00:00 | rolando |    4 |    2 | angel || 2011-06-29 00:00:00 | rolando |    4 | NulL | eDWar || 2011-06-29 00:00:00 | rolando |    4 | NulL | angel || 2011-06-28 00:00:00 | rolando |    5 |    2 | angel || 2011-06-28 00:00:00 | rolando |    5 |    2 | angel || 2011-06-28 00:00:00 | rolando |    4 |    2 | angel || 2011-06-28 00:00:00 | pamela  |    4 |    2 | angel || 2011-06-28 00:00:00 | pamela  |    4 | NulL | eDWar || 2011-06-28 00:00:00 | pamela  |    5 |    2 | angel || 2011-06-28 00:00:00 | pamela  |    5 |    2 | angel || 2011-06-28 00:00:00 | rolando |    4 |    2 | angel || 2011-06-28 00:00:00 | rolando |    4 |    2 | angel || 2011-06-28 00:00:00 | rolando |    4 | NulL | eDWar || 2011-06-28 00:00:00 | rolando |    4 | NulL | angel |+---------------------+---------+------+------+-------+22 rows in set (0.00 sec)MysqL> SELECT    ->     COUNT(datefIEld) datefIEld_count,->     left(all_fIElds,->     SUBSTR(all_fIElds,11) all_other_fIElds    -> FROM    -> (    ->     SELECT    ->         DATE(datefIEld) datefIEld,->         CONCAT(DATE(datefIEld),->         COALESCE(col1,->         COALESCE(col2,->         COALESCE(col3,->         COALESCE(col4,'|') all_fIElds    ->     FROM    ->          yourtable    -> ) A    -> GROUP BY all_fIElds;+-----------------+------------+----------------------------+| datefIEld_count | datefIEld  | all_other_fIElds           |+-----------------+------------+----------------------------+|               1 | 2011-06-28 | |pamela|4|2|angel|         ||               1 | 2011-06-28 | |pamela|4|< NulL >|eDWar|  ||               2 | 2011-06-28 | |pamela|5|2|angel|         ||               3 | 2011-06-28 | |rolando|4|2|angel|        ||               1 | 2011-06-28 | |rolando|4|< NulL >|angel| ||               1 | 2011-06-28 | |rolando|4|< NulL >|eDWar| ||               2 | 2011-06-28 | |rolando|5|2|angel|        ||               4 | 2011-06-29 | |rolando|4|2|angel|        ||               1 | 2011-06-29 | |rolando|4|< NulL >|angel| ||               1 | 2011-06-29 | |rolando|4|< NulL >|eDWar| ||               3 | 2011-06-30 | |rolando|4|3|angel|        ||               2 | 2011-06-30 | |rolando|4|< NulL >|angel| |+-----------------+------------+----------------------------+12 rows in set (0.00 sec)MysqL>

我会留给你富有想象力的创造力来循环阅读和打印

> datefIEld
> datefIEld_count
>打印all_other_fIElds’datefIEld_count’次

试试看 ！！！ 总结

以上是内存溢出为你收集整理的mySql：count列中具有相同数据的行数全部内容，希望文章能够帮你解决mySql：count列中具有相同数据的行数所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。