数据源

-(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