FMDB查询计数

Question

我正在创建一个应用程序，从我创建的数据库读取数据。数据库中有三个表:CLIFFS, SECTORS, REGIONS。我使用FMDB从REGIONS表中推断数据，但同时我必须在表FIELDS中对CITY进行计数。

有人知道我该怎么做吗?

这是我的代码:

(void)viewDidLoad {
    [super viewDidLoad];
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"CLIMB.sqlite"];
    BOOL success = [fileManager fileExistsAtPath:writableDBPath];
    if(!success){
        NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"CLIMB.sqlite"];
        success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    }
    if(!success){
        NSAssert1(0, @"failed to create writable db file with message '%@'.", [error localizedDescription]);
    }
    FMDatabase* db = [FMDatabase databaseWithPath:writableDBPath];
    if(![db open])
    {
        NSLog(@"Err %d: %@", [db lastErrorCode], [db lastErrorMessage]);
    }
    [db setShouldCacheStatements:YES];
    NSString *sql = @"SELECT * FROM REGIONI;";

    FMResultSet  *rs = [db executeQuery:sql];
    data = [[NSMutableArray alloc] init];
    while( [rs next] )
    {
        CDEvent * region = [[CDEvent alloc] init];
        region.ID_SETTORE = [rs stringForColumn:@"ID_SETTORE"];
        region.REGIONE = [rs stringForColumn:@"REGIONE"];
        region.N_CITTA = [rs stringForColumn:@"N_CITTA"];
        region.CODICE_ID = [rs stringForColumn:@"CODICE_ID"];
        region.CITTA = [rs stringForColumn:@"CITTA"];

        [data addObject:region];
    }
    [rs close];
    [db close];
}

Answer 1

试试这个:

queryString = [NSString stringWithFormat:@"SELECT COUNT(CITY) FROM FIELDS];
NSInteger count = [db intForQuery: queryString];

Answer 2

在回答之前，我在sql变量中看到一些奇怪的东西:

NSString *sql = @"SELECT * FROM REGIONI;";

首先，在SQLite中没有必要在其语句中使用";"。其次，当表(如您所说)是REGIONS时，您正在咨询REGIONI。

我的回答:

FMResultSet *field = [db executeQuery:@"select count(CITY) from FIELDS as cities_number"];
int count = [field intForColumn:@"cities_number"];
NSLog(@"Number of cities: %i" , count);