关于C语言编程实现数据的查询计算及插入的问题

和过去说再见 2019-04-04 09:58:18
实习的时候有这样一个任务,就是有一个数据库存放的是列车的路程、速度、时间、经纬度等数据,这些数据理论上是需要每5秒以内传送一次的,但是有时可能会超过5秒,那么问题来了,如果两条数据超过5s,就根据已经有的数据(如相邻的两个数据)进行计算,再插入到数据库中,这样两个数据之间就小于5s了,不过有个问题就是,两个数据之间相差20的话,比如一个是10 然后是30,中间插15, 15到30之间就不插了,循环我也没循环明白,递归也不咋熟,纯小白一个,恳请各位大神帮帮忙。

这是我自己建的一个测试用数据库:
mysql> select * from test4;
+----+---------------------+-------+----------+
| id | time | speed | distance |
+----+---------------------+-------+----------+
| 1 | 2017-05-03 17:12:20 | 22 | 3847 |
| 2 | 2017-05-03 17:12:26 | 22 | 3847 |
| 3 | 2017-05-03 17:12:40 | 22 | 3847 |
+----+---------------------+-------+----------+


这是我的代码:

#include <mysql/mysql.h>
#include <stdio.h>
#include <string.h>
#include <time.h>
#include <stdlib.h>
long GetTick(char *str_time)
{
struct tm stm;
int iY, iM, iD, iH, iMin, iS;

memset(&stm,0,sizeof(stm));

iY = atoi(str_time);
iM = atoi(str_time+5);
iD = atoi(str_time+8);
iH = atoi(str_time+11);
iMin = atoi(str_time+14);
iS = atoi(str_time+17);

stm.tm_year=iY-1900;
stm.tm_mon=iM-1;
stm.tm_mday=iD;
stm.tm_hour=iH;
stm.tm_min=iMin;
stm.tm_sec=iS;

/*printf("%d-%0d-%0d %0d:%0d:%0d\n", iY, iM, iD, iH, iMin, iS);*/

return mktime(&stm);
}
char* itstr(time_t value,char*a)
{


struct tm t;
time_t result;
t.tm_year = 70;
t.tm_mon = 1;
t.tm_mday = 0;
t.tm_hour = -8;
t.tm_min = 0;
t.tm_sec = 0;
result = mktime(&t);
result += value;
memcpy(&t, localtime(&result), sizeof(struct tm));
sprintf(a,"%d-%02d-%02d %02d:%02d:%02d", t.tm_year + 1900, t.tm_mon, t.tm_mday, t.tm_hour, t.tm_min, t.tm_sec);
return a;
}
int main()
{
MYSQL mysql;
MYSQL_RES *res;
MYSQL_ROW row;
char query[512] = {0};
int flag, t;
char sql[512] = {0};
char *sql1;
mysql_init(&mysql);
if(!mysql_real_connect(&mysql, "localhost", "root", "yang5716", "test", 3306, NULL, 0)) {
printf("Failed to connect to Mysql!\n");fprintf(stderr,"Failedtoconnecttodatabase:Error:%s\n",mysql_error(&mysql)); return 0;
}else {
printf("Connected to Mysql successfully!\n");
}

sprintf(query,"SELECT time,speed,distance from test4;");


/*查询,成功则返回0*/
flag = mysql_real_query(&mysql, query, (unsigned int)strlen(query));
if(flag) {
printf("Query failed!\n");
return 0;
}else {
printf("[%s] made...\n", query);
}

long savetime,savespeed,savedistance=0;
/*mysql_store_result讲全部的查询结果读取到客户端*/
res = mysql_store_result(&mysql);
/*mysql_fetch_row检索结果集的下一行*/
while(row = mysql_fetch_row(res)) {


if(savetime&&savespeed&&savedistance)
{


while(GetTick(row[0])-savetime>5)
{
char a[256]={0};
time_t time1=savetime;
time_t time2=GetTick(row[0]);
int speed1=savespeed,speed2=atoi(row[1]);
int distance=savedistance;
int fspeed,fdistance=0;
time_t ftime;
ftime = time1 + 5;
fspeed = (speed1+speed2)/2;
fdistance =5 * fspeed+distance;
printf("time1 %d time2 %d speed1 %d\n",time1,GetTick(row[0]),speed1);
//printf("%d %d %d\n",ftime,fspeed,fdistance);
printf("%s\n",itstr(ftime,a));
sprintf(sql,"insert into test4(time,speed,distance) values('%s',%d,%d);",itstr(ftime,a),fspeed,fdistance);
printf("%s\n",sql);

flag = mysql_real_query(&mysql, sql, (unsigned int)strlen(sql));
savetime=GetTick(row[0]);savespeed=atoi(row[1]);savedistance=atoi(row[2]);



}
}
else
savetime=GetTick(row[0]);savespeed=atoi(row[1]);savedistance=atoi(row[2]);





}
mysql_close(&mysql);
return 0;
}




执行之后这样:
+----+---------------------+-------+----------+
| id | time | speed | distance |
+----+---------------------+-------+----------+
| 1 | 2017-05-03 17:12:20 | 22 | 3847 |
| 2 | 2017-05-03 17:12:26 | 22 | 3847 |
| 3 | 2017-05-03 17:12:40 | 22 | 3847 |
| 14 | 2017-05-02 17:12:25 | 22 | 3957 |
| 15 | 2017-05-02 17:12:31 | 22 | 3957 |
+----+---------------------+-------+----------+
5 rows in set (0.00 sec)
...全文
77 点赞 收藏 回复
写回复
回复
切换为时间正序
请发表友善的回复…
发表回复

还没有回复,快来抢沙发~

相关推荐
发帖
其它技术问题
创建于2007-09-28

3845

社区成员

C/C++ 其它技术问题
申请成为版主
帖子事件
创建了帖子
2019-04-04 09:58
社区公告
暂无公告