我有2万条数据,有四个字段,第一是日期,第二是夜间雨量,第三是白天雨量,第四是总雨量
CREATE TABLE `rain_beibei` (
`date` date NOT NULL ,
`Nrain` int NOT NULL ,
`Train` int NOT NULL ,
`Arain` int NOT NULL ,
PRIMARY KEY (`date`)
)
;
我想得到的结果是统计出每个月内Nrain,Train,Arain大于0的天数;还有每个月Nrain,Train,Arain的总数
时间是1951年到2008年,表结构如下:
date Nrain Train Arain
1951-1-1 0 0 0
1951-1-2 0 0 0
1951-1-3 0 0 0
1951-1-4 0 0 0
1951-1-5 0 0 0
1951-1-6 0 0 0
1951-1-7 0 0 0
1951-1-8 0 0 0
1951-1-9 4 29 33
1951-1-10 21 3 24
1951-1-11 2 0 2
1951-1-12 5 6 11
1951-1-13 0 0 0
1951-1-14 0 0 0
1951-1-15 0 0 0
1951-1-16 0 0 0
1951-1-17 0 0 0
......
2008-12-18 0 0 0
2008-12-19 0 0 0
2008-12-20 0 0 0
2008-12-21 46 41 87
2008-12-22 71 61 132
2008-12-23 6 7 13
2008-12-24 1 0 1
2008-12-25 0 0 0
2008-12-26 0 0 0
2008-12-27 0 0 0
2008-12-28 0 0 0
2008-12-29 0 0 0
2008-12-30 11 17 28
2008-12-31 0 0 0
CREATE TABLE `rain_beibei` (
`date` date NOT NULL ,
`Nrain` int NOT NULL ,
`Train` int NOT NULL ,
`Arain` int NOT NULL ,
PRIMARY KEY (`date`)
)
;
我想得到的结果是统计出每个月内Nrain,Train,Arain大于0的天数;还有每个月Nrain,Train,Arain的总数
时间是1951年到2008年,表结构如下:
date Nrain Train Arain
1951-1-1 0 0 0
1951-1-2 0 0 0
1951-1-3 0 0 0
1951-1-4 0 0 0
1951-1-5 0 0 0
1951-1-6 0 0 0
1951-1-7 0 0 0
1951-1-8 0 0 0
1951-1-9 4 29 33
1951-1-10 21 3 24
1951-1-11 2 0 2
1951-1-12 5 6 11
1951-1-13 0 0 0
1951-1-14 0 0 0
1951-1-15 0 0 0
1951-1-16 0 0 0
1951-1-17 0 0 0
......
2008-12-18 0 0 0
2008-12-19 0 0 0
2008-12-20 0 0 0
2008-12-21 46 41 87
2008-12-22 71 61 132
2008-12-23 6 7 13
2008-12-24 1 0 1
2008-12-25 0 0 0
2008-12-26 0 0 0
2008-12-27 0 0 0
2008-12-28 0 0 0
2008-12-29 0 0 0
2008-12-30 11 17 28
2008-12-31 0 0 0
Nrain,Train,Arain这三个都大于0 ?还是任一大于0 ? (不要高估你的汉语表达能力或者我的汉语理解能力)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-2', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-3', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-4', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-5', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-6', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-7', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-8', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-9', 4, 29, 33);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-10', 21, 3, 24);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-11', 2, 0, 2);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-12', 5, 6, 11);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-13', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-14', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-15', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-16', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-17', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-18', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-19', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-20', 0, 4, 4);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-21', 10, 3, 13);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-22', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-23', 0, 1, 1);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-24', 0, 1, 1);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-25', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-26', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-27', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-28', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-29', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-30', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-1-31', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-1', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-2', 0, 4, 4);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-3', 1, 0, 1);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-4', 16, 3, 19);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-5', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-6', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-7', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-8', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-9', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-10', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-11', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-12', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-13', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-14', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-15', 31, 21, 52);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-16', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-17', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-18', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-19', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-20', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-21', 23, 0, 23);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-22', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-23', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-24', 7, 6, 13);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-25', 2, 0, 2);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-26', 1, 1, 2);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-27', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-2-28', 3, 0, 3);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-1', 26, 19, 45);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-2', 25, 0, 25);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-3', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-4', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-5', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-6', 9, 0, 9);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-7', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-8', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-9', 1, 0, 1);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-10', 0, 0, 0);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-11', 15, 12, 27);
INSERT INTO `rain_beibei` (`date`, `Nrain`, `Train`, `Arain`) VALUES ('1951-3-12', 117, 0, 117);
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)楼主何必一次挤牙膏一样只挤一点儿???
针对上面的测试数据,你期望的正确结果是什么?
Nrain总数 是不是 100 ? 如果不是应该是几?
1951-1 42 47 89
1951-2 238 35 119
1951-3 .. .. ..
+------------+-------+-------+-------+
| date | Nrain | Train | Arain |
+------------+-------+-------+-------+
| 1951-01-01 | 0 | 0 | 0 |
| 1951-01-02 | 0 | 0 | 0 |
| 1951-01-03 | 0 | 0 | 0 |
| 1951-01-04 | 0 | 0 | 0 |
| 1951-01-05 | 0 | 0 | 0 |
| 1951-01-06 | 0 | 0 | 0 |
| 1951-01-07 | 0 | 0 | 0 |
| 1951-01-08 | 0 | 0 | 0 |
| 1951-01-09 | 4 | 29 | 33 |
| 1951-01-10 | 21 | 3 | 24 |
| 1951-01-11 | 2 | 0 | 2 |
| 1951-01-12 | 5 | 6 | 11 |
| 1951-01-13 | 0 | 0 | 0 |
| 1951-01-14 | 0 | 0 | 0 |
| 1951-01-15 | 0 | 0 | 0 |
| 1951-01-16 | 0 | 0 | 0 |
| 1951-01-17 | 0 | 0 | 0 |
| 1951-01-18 | 0 | 0 | 0 |
| 1951-01-19 | 0 | 0 | 0 |
| 1951-01-20 | 0 | 4 | 4 |
| 1951-01-21 | 10 | 3 | 13 |
| 1951-01-22 | 0 | 0 | 0 |
| 1951-01-23 | 0 | 1 | 1 |
| 1951-01-24 | 0 | 1 | 1 |
| 1951-01-25 | 0 | 0 | 0 |
| 1951-01-26 | 0 | 0 | 0 |
| 1951-01-27 | 0 | 0 | 0 |
| 1951-01-28 | 0 | 0 | 0 |
| 1951-01-29 | 0 | 0 | 0 |
| 1951-01-30 | 0 | 0 | 0 |
| 1951-01-31 | 0 | 0 | 0 |
| 1951-02-01 | 0 | 0 | 0 |
| 1951-02-02 | 0 | 4 | 4 |
| 1951-02-03 | 1 | 0 | 1 |
| 1951-02-04 | 16 | 3 | 19 |
| 1951-02-05 | 0 | 0 | 0 |
| 1951-02-06 | 0 | 0 | 0 |
| 1951-02-07 | 0 | 0 | 0 |
| 1951-02-08 | 0 | 0 | 0 |
| 1951-02-09 | 0 | 0 | 0 |
| 1951-02-10 | 0 | 0 | 0 |
| 1951-02-11 | 0 | 0 | 0 |
| 1951-02-12 | 0 | 0 | 0 |
| 1951-02-13 | 0 | 0 | 0 |
| 1951-02-14 | 0 | 0 | 0 |
| 1951-02-15 | 31 | 21 | 52 |
| 1951-02-16 | 0 | 0 | 0 |
| 1951-02-17 | 0 | 0 | 0 |
| 1951-02-18 | 0 | 0 | 0 |
| 1951-02-19 | 0 | 0 | 0 |
| 1951-02-20 | 0 | 0 | 0 |
| 1951-02-21 | 23 | 0 | 23 |
| 1951-02-22 | 0 | 0 | 0 |
| 1951-02-23 | 0 | 0 | 0 |
| 1951-02-24 | 7 | 6 | 13 |
| 1951-02-25 | 2 | 0 | 2 |
| 1951-02-26 | 1 | 1 | 2 |
| 1951-02-27 | 0 | 0 | 0 |
| 1951-02-28 | 3 | 0 | 3 |
| 1951-03-01 | 26 | 19 | 45 |
| 1951-03-02 | 25 | 0 | 25 |
| 1951-03-03 | 0 | 0 | 0 |
| 1951-03-04 | 0 | 0 | 0 |
| 1951-03-05 | 0 | 0 | 0 |
| 1951-03-06 | 9 | 0 | 9 |
| 1951-03-07 | 0 | 0 | 0 |
| 1951-03-08 | 0 | 0 | 0 |
| 1951-03-09 | 1 | 0 | 1 |
| 1951-03-10 | 0 | 0 | 0 |
| 1951-03-11 | 15 | 12 | 27 |
| 1951-03-12 | 117 | 0 | 117 |
+------------+-------+-------+-------+
71 rows in set (0.00 sec)mysql> select date_format(`date`,'%Y-%m'),
-> sum(Nrain),sum(Train),sum(Arain)
-> from rain_beibei
-> group by date_format(`date`,'%Y-%m');
+-----------------------------+------------+------------+------------+
| date_format(`date`,'%Y-%m') | sum(Nrain) | sum(Train) | sum(Arain) |
+-----------------------------+------------+------------+------------+
| 1951-01 | 42 | 47 | 89 |
| 1951-02 | 84 | 35 | 119 |
| 1951-03 | 193 | 31 | 224 |
+-----------------------------+------------+------------+------------+
3 rows in set (0.00 sec)mysql>
1951-1 42 47 89
1951-2 238 35 119
1951-3 .. .. .
说明一下你的 238 怎么来的??
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
1951-2 8 5 9
1951-3 .. .. .相当于把
select date_format('date','%Y-%m'),count(*)
from rain_beibei where Nrain>0 group by date_format(`date`,'%Y-%m')select date_format('date','%Y-%m'),count(*)
from rain_beibei where Train>0 group by date_format(`date`,'%Y-%m')select date_format('date','%Y-%m'),count(*)
from rain_beibei where Arain>0 group by date_format(`date`,'%Y-%m')
三条语句组合起来
-> sum(Nrain>0),sum(Train>0),sum(Arain>0)
-> from rain_beibei
-> group by date_format(`date`,'%Y-%m');
+-----------------------------+--------------+--------------+--------------+
| date_format(`date`,'%Y-%m') | sum(Nrain>0) | sum(Train>0) | sum(Arain>0) |
+-----------------------------+--------------+--------------+--------------+
| 1951-01 | 5 | 7 | 8 |
| 1951-02 | 8 | 5 | 9 |
| 1951-03 | 6 | 2 | 6 |
+-----------------------------+--------------+--------------+--------------+
3 rows in set (0.00 sec)mysql>
CString strSql;
strSql.Format("select sum(Nrain),sum(Train),sum(Arain) from rain_beibei where year(date)=%d and month(date)=%d",iYear,iMonth);
其中iYear和iMonth为你想要查询的那个月的年月,可以用个循环取出全部每个月的Nrain,Train,Arain总数。