数据源
-(void)dataConfig{
_dataArr = [[NSMutableArray alloc] init];
Worker *w1 = [[Worker alloc] init];
w1.workId = @"2";
w1.name = @"小明1";
w1.age = 12;
Worker *w2 = [[Worker alloc] init];
w2.workId = @"1";
w2.name = @"小明2";
w2.age = 13;
Worker *w3 = [[Worker alloc] init];
w3.workId = @"3";
w3.name = @"小李";
w3.age = 14;
Worker *w4 = [[Worker alloc] init];
w4.workId = @"4";
w4.name = @"小李";
w4.age = 15;
[_dataArr addObject:w1];
[_dataArr addObject:w2];
[_dataArr addObject:w3];
[_dataArr addObject:w4];
}
先封装一下数据的统计
-(NSMutableArray *)getReArrFromSql:(NSString *)sql{
FMResultSet *rs = [_database executeQuery:sql];
NSMutableArray *arr = [[NSMutableArray alloc] init];
while ([rs next]) {
Worker *model = [[Worker alloc] init];
model.workId = [rs stringForColumn:@"workId"];
model.name = [rs stringForColumn:@"name"];
model.age = [rs intForColumn:@"age"];
[arr addObject:model];
}
return arr;
}
数据的升序和降序
//order by
//1 排序
//asc 默认 升序
//desc 降序
-(NSMutableArray *)getAllDataOrderBy:(NSString *)type{
NSString *sql = [NSString stringWithFormat:@"select * from workTable order by workId %@",type];
return [self getReArrFromSql:sql];
}
去重
//distinct
//1 只能放在最前面
//2 只能作用于一列
-(NSMutableArray *)getAllDataDistinct{
NSString *sql = [NSString stringWithFormat:@"select distinct name from workTable"];
return [self getReArrFromSql:sql];
}
并
//and就是并的作用
-(NSMutableArray *)getAllDataAnd{
NSString *sql = [NSString stringWithFormat:@"select * from workTable where workId = 1 and name = '小张'"];
return [self getReArrFromSql:sql];
}
或
//or就是或的作用
-(NSMutableArray *)getAllDataOr{
NSString *sql = [NSString stringWithFormat:@"select * from workTable where workId = 1 or name = '小李'"];
return [self getReArrFromSql:sql];
}
like 模糊查询
//like
//结尾是李的所有数据
-(NSMutableArray *)getAllDataLike1{
NSString *sql = [NSString stringWithFormat:@"select * from workTable where name like '%%李'"];
return [self getReArrFromSql:sql];
}
//中间是明的所有数据
-(NSMutableArray *)getAllDataLike3{
NSString *sql = [NSString stringWithFormat:@"select * from workTable where name like '%%明%%'"];
return [self getReArrFromSql:sql];
}
//不包含明的所有数据
-(NSMutableArray *)getAllDataLike4{
NSString *sql = [NSString stringWithFormat:@"select * from workTable where name not like '%%明%%'"];
return [self getReArrFromSql:sql];
}
in 相当于集合
//in 相当于集合
-(NSMutableArray *)getAllDataIn{
NSString *sql = [NSString stringWithFormat:@"select * from workTable where name in ('小明1','小李')"];
return [self getReArrFromSql:sql];
}
between and 闭区间
//between sqlite里面的between都是闭区间
-(NSMutableArray *)getAllDataBetween{
NSString *sql = [NSString stringWithFormat:@"select * from workTable where workId between 1 and 3"];
return [self getReArrFromSql:sql];
}
第二大类是数据的统计，比如数据量，某个字段的平均值
分组并筛选
//group by 筛选结果的计算
//having 对group by 结果的进一步筛选
-(NSMutableArray *)getAllDataGroupBy{
NSString *sql = [NSString stringWithFormat:@"select name,sum(age) s from workTable group by name having s>15"];
NSMutableArray *arr = [[NSMutableArray alloc] init];
FMResultSet *rs = [_database executeQuery:sql];
while ([rs next]) {
Worker *model = [[Worker alloc] init];
model.name = [rs stringForColumn:@"name"];
model.age = [rs intForColumn:@"s"];
[arr addObject:model];
}
return arr;
}
最小值
//min 最小值
-(NSInteger)getAllDataMin{
NSString *sql = [NSString stringWithFormat:@"select min(age) minAge from workTable"];
NSMutableArray *arr = [[NSMutableArray alloc] init];
FMResultSet *rs = [_database executeQuery:sql];
while ([rs next]) {
Worker *model = [[Worker alloc] init];
model.statistic = [rs intForColumn:@"minAge"];
[arr addObject:model];
}
Worker *model = [arr firstObject];
return model.statistic;
}
最大值
//max 最大值
-(NSInteger)getAllDataMax{
NSString *sql = [NSString stringWithFormat:@"select max(age) maxAge from workTable"];
NSMutableArray *arr = [[NSMutableArray alloc] init];
FMResultSet *rs = [_database executeQuery:sql];
while ([rs next]) {
Worker *model = [[Worker alloc] init];
model.statistic = [rs intForColumn:@"maxAge"];
[arr addObject:model];
}
Worker *model = [arr firstObject];
return model.statistic;
}
数据之和
//sum 数据之和
-(NSInteger)getAllDataSum{
NSString *sql = [NSString stringWithFormat:@"select sum(age) sumAge from workTable"];
NSMutableArray *arr = [[NSMutableArray alloc] init];
FMResultSet *rs = [_database executeQuery:sql];
while ([rs next]) {
Worker *model = [[Worker alloc] init];
model.statistic = [rs intForColumn:@"sumAge"];
[arr addObject:model];
}
Worker *model = [arr firstObject];
return model.statistic;
}
数据平均值
//avg 数据的平均值
-(NSInteger)getAllDataAvg{
NSString *sql = [NSString stringWithFormat:@"select avg(age) avgAge from workTable"];
NSMutableArray *arr = [[NSMutableArray alloc] init];
FMResultSet *rs = [_database executeQuery:sql];
while ([rs next]) {
Worker *model = [[Worker alloc] init];
model.statistic = [rs intForColumn:@"avgAge"];
[arr addObject:model];
}
Worker *model = [arr firstObject];
return model.statistic;
}
数据量
//count 表中的数据量
-(NSInteger)getAllDataCount{
NSString *sql = [NSString stringWithFormat:@"select count(*) count from workTable"];
NSMutableArray *arr = [[NSMutableArray alloc] init];
FMResultSet *rs = [_database executeQuery:sql];
while ([rs next]) {
Worker *model = [[Worker alloc] init];
model.statistic = [rs intForColumn:@"count"];
[arr addObject:model];
}
Worker *model = [arr firstObject];
return model.statistic;
}
本文章仅自学借鉴，文章转载至：http://blog.csdn.net/rpf2014/article/details/53116906
网友评论