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

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

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_fields
FROM
(
    SELECT
        DATE(datefield) datefield,CONCAT(DATE(datefield),'|',COALESCE(col1,'< NULL >'),COALESCE(col2,COALESCE(col3,COALESCE(col4,'|') all_fields
    FROM
         yourtable
) A
GROUP 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: 3
MysqL> 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相关文章