如果你有一个如下所示的表:
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
datefield_count
打印all_other_fields 'datefield_count'次
试试看 !!!