又写了句sql，比较变态。
时间：2006-07-13 来源：jingzhi
表
id datetime check
1 2006-7-12 12:00:00 1
2 2006-7-12 12:23:00 1
3 2006-7-12 12:56:00 0
4 2006-7-13 12:59:00 0
5 2006-7-13 12:00:00 0
6 2006-7-14 12:00:00 0
要求得到结果：
time count(id) count(check=1) count(check=0)
2006-7-12 3 2 1
2006-7-13 2 0 2
2006-7-14 1 0 1
sum(case `check` when 0 then 1 else 0 end) as 'count(check=0)'
from info GROUP by substr(info_date,1,10);
+------------------------+-----------+----------------+----------------+
| substr(info_date,1,10) | count(id) | count(check=1) | count(check=0) |
+------------------------+-----------+----------------+----------------+
| 2006-07-12 | 3 | 2 | 1 |
| 2006-07-13 | 2 | 0 | 2 |
| 2006-07-14 | 1 | 0 | 1 |
+------------------------+-----------+----------------+----------------+
3 rows in set (0.00 sec)
select substr(info_date,1,10) ,count(id),count(case `check` when 1 then 1 else null end) as 'count(check=1)',
count(case `check` when 0 then 1 else null end) as 'count(check=0)'
from info GROUP by substr(info_date,1,10);
+------------------------+-----------+----------------+----------------+
| substr(info_date,1,10) | count(id) | count(check=1) | count(check=0) |
+------------------------+-----------+----------------+----------------+
| 2006-07-12 | 3 | 2 | 1 |
| 2006-07-13 | 2 | 0 | 2 |
| 2006-07-14 | 1 | 0 | 1 |
+------------------------+-----------+----------------+----------------+
3 rows in set (0.00 sec)
id datetime check
1 2006-7-12 12:00:00 1
2 2006-7-12 12:23:00 1
3 2006-7-12 12:56:00 0
4 2006-7-13 12:59:00 0
5 2006-7-13 12:00:00 0
6 2006-7-14 12:00:00 0
要求得到结果：
time count(id) count(check=1) count(check=0)
2006-7-12 3 2 1
2006-7-13 2 0 2
2006-7-14 1 0 1
mysql> create table info(
-> `id` int(6) not null auto_increment,
-> `info_date` DATETIME not null default '0000-00-00 00:00:00',
-> `check` int(10) not null default '0',
-> PRIMARY KEY (`id`)
-> )TYPE=MyISAM;
Query OK, 0 rows affected, 1 warning (0.06 sec)
mysql> insert into info values('','2006-7-12 12:00:00',1);
Query OK, 1 row affected, 1 warning (0.01 sec)
mysql> insert into info values('','2006-7-12 12:23:00',1);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into info values('','2006-7-12 12:56:00',0);
Query OK, 1 row affected, 1 warning (0.02 sec)
mysql> insert into info values('','2006-7-13 12:59:00',0);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into info values('','2006-7-13 12:12:00',0);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> insert into info values('','2006-7-14 12:12:00',0);
Query OK, 1 row affected, 1 warning (0.00 sec)
mysql> select * from info;
+----+---------------------+-------+
| id | info_date | check |
+----+---------------------+-------+
| 1 | 2006-07-12 12:00:00 | 1 |
| 2 | 2006-07-12 12:23:00 | 1 |
| 3 | 2006-07-12 12:56:00 | 0 |
| 4 | 2006-07-13 12:59:00 | 0 |
| 5 | 2006-07-13 12:12:00 | 0 |
| 6 | 2006-07-14 12:12:00 | 0 |
+----+---------------------+-------+
6 rows in set (0.00 sec)
mysql> select substr(a.info_date,1,10) as info_time,
-> (select count(b.check) from info as b where b.check=1
-> and substr(b.info_date,1,10)=info_time ) as check_1 ,
-> (select count(c.check) from info as c where c.check=0
-> and substr(c.info_date,1,10)=info_time ) as check_0
-> from info as a
-> group by info_time;
+------------+---------+---------+
| info_time | check_1 | check_0 |
+------------+---------+---------+
| 2006-07-12 | 2 | 1 |
| 2006-07-13 | 0 | 2 |
| 2006-07-14 | 0 | 1 |
+------------+---------+---------+
3 rows in set (0.01 sec)
改进的写法（只针对本特例）：
mysql> select substr(info_date,1,10) ,count(id),sum(`check`),sum(1-`check`) from info GROUP by substr(info_date,1,10);
+------------------------+-----------+--------------+----------------+
| substr(info_date,1,10) | count(id) | sum(`check`) | sum(1-`check`) |
+------------------------+-----------+--------------+----------------+
| 2006-07-12 | 3 | 2 | 1 |
| 2006-07-13 | 2 | 0 | 2 |
| 2006-07-14 | 1 | 0 | 1 |
+------------------------+-----------+--------------+----------------+
3 rows in set (0.02 sec)
sum(case `check` when 0 then 1 else 0 end) as 'count(check=0)'
from info GROUP by substr(info_date,1,10);
+------------------------+-----------+----------------+----------------+
| substr(info_date,1,10) | count(id) | count(check=1) | count(check=0) |
+------------------------+-----------+----------------+----------------+
| 2006-07-12 | 3 | 2 | 1 |
| 2006-07-13 | 2 | 0 | 2 |
| 2006-07-14 | 1 | 0 | 1 |
+------------------------+-----------+----------------+----------------+
3 rows in set (0.00 sec)
select substr(info_date,1,10) ,count(id),count(case `check` when 1 then 1 else null end) as 'count(check=1)',
count(case `check` when 0 then 1 else null end) as 'count(check=0)'
from info GROUP by substr(info_date,1,10);
+------------------------+-----------+----------------+----------------+
| substr(info_date,1,10) | count(id) | count(check=1) | count(check=0) |
+------------------------+-----------+----------------+----------------+
| 2006-07-12 | 3 | 2 | 1 |
| 2006-07-13 | 2 | 0 | 2 |
| 2006-07-14 | 1 | 0 | 1 |
+------------------------+-----------+----------------+----------------+
3 rows in set (0.00 sec)
mysql> select substr(info_date,1,10),count(id),
sum(if(`check`,1,0)) as 'count(check=1)',
sum(if(`check`,0,1)) as 'count(check=0)'
from info
group by substr(info_date,1,10);
+------------------------+-----------+----------------+----------------+
| substr(info_date,1,10) | count(id) | count(check=1) | count(check=0) |
+------------------------+-----------+----------------+----------------+
| 2006-07-12 | 3 | 2 | 1 |
| 2006-07-13 | 2 | 0 | 2 |
| 2006-07-14 | 1 | 0 | 1 |
+------------------------+-----------+----------------+----------------+
3 rows in set (0.00 sec)
相关阅读 更多 +
