mySql:计算列中具有相同数据的行数

Question

我正在尝试选择表格中的所有内容，并计算表格中具有相同数据的行数。

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

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

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

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

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

等...

这有意义吗？

Answer 1

如果你有一个看起来像这样的表:

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,'< NULL >'),'|',
        COALESCE(col3,'< NULL >'),'|',
        COALESCE(col4,'< NULL >'),'|') 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),'rolando',4,3   ,'angel'),
    -> (DATE(NOW() - INTERVAL 1 DAY),'rolando',4,3   ,'angel'),
    -> (DATE(NOW() - INTERVAL 1 DAY),'rolando',4,NULL,'angel'),
    -> (DATE(NOW() - INTERVAL 1 DAY),'rolando',4,NULL,'angel'),
    -> (DATE(NOW() - INTERVAL 2 DAY),'rolando',4,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 2 DAY),'rolando',4,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 2 DAY),'rolando',4,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 2 DAY),'rolando',4,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 2 DAY),'rolando',4,NULL,'edwards'),
    -> (DATE(NOW() - INTERVAL 2 DAY),'rolando',4,NULL,'angel'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'rolando',5,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'rolando',5,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'rolando',4,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'pamela' ,4,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'pamela' ,4,NULL,'edwards'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'pamela' ,5,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'pamela' ,5,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'rolando',4,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'rolando',4,2   ,'angel'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'rolando',4,NULL,'edwards'),
    -> (DATE(NOW() - INTERVAL 3 DAY),'rolando',4,NULL,'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,10) datefield,
    ->     SUBSTR(all_fields,11) all_other_fields
    -> FROM
    -> (
    ->     SELECT
    ->         DATE(datefield) datefield,
    ->         CONCAT(DATE(datefield),'|',
    ->         COALESCE(col1,'< NULL >'),'|',
    ->         COALESCE(col2,'< NULL >'),'|',
    ->         COALESCE(col3,'< NULL >'),'|',
    ->         COALESCE(col4,'< NULL >'),'|') 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_count
• 打印 all_other_fields 'datefield_count' 次

试一试!!!