如何在sqlite中从多个表中计算行数

Question

我正在使用以下代码

  - (int)GetTextCount
  {

    NSMutableArray *audioArray=[[NSMutableArray alloc]init];



    int count = 0;
//This method is defined to retrieve data from Database


NSString *dbPath=filePath;

sqlite3 *database;

if(sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

    // Setup the SQL Statement and compile it for faster access
    /*
    SELECT  (
             SELECT COUNT(*)
             FROM   tab1
             ) AS count1,
    (
     SELECT COUNT(*)
     FROM   tab2
     ) AS count2
    FROM    dual
    */


    const char *sqlStatement = "select count(*) from photo where mid=? ";
    //const char *sqlStatement = "select * from textt where mid=?";
    sqlite3_stmt *compiledStatement;


    if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {

        sqlite3_bind_int(compiledStatement, 1, memoryData.memoryId);

        //(compiledStatement, 1, [header UTF8String], -1, SQLITE_TRANSIENT);

        while(sqlite3_step(compiledStatement) == SQLITE_ROW) {              
            AudioData *data=[[AudioData alloc]init];
            //create the MemoryData object to store the data of one record


            // Read the data from the result row

            int pId=sqlite3_column_int(compiledStatement, 0);
            NSLog(@"total audiosssss are %i",pId);


            //NSString *filePath=[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];


            //filePath=[self retrievePath:filePath];

            //[data setAudioId:pId];
            //[data setFilePath:filePath];
            //Store every object of MemoryData in t
            [audioArray addObject:data];


        } // end of the while


    }
    sqlite3_finalize(compiledStatement);
}
sqlite3_close(database);
return [audioArray count];
  }

我需要从四个表中计算行数，但是现在我正在运行单独的查询，这会降低性能，因此我想运行一个查询从四个表中选择，帮忙解决如何实现？

- Mario

Answer 1

我认为您需要这个查询。（如果我错了，对不起）。

SELECT (SELECT count(*) from table_1  where mid = ?) + 
       (SELECT count(*) from table_2  where mid = ?) +
       (SELECT count(*) from table_3  where mid = ?) +
       (SELECT count(*) from table_4  where mid = ?)

- EmptyStack

Answer 2

试试这个：

SELECT COUNT(*) AS MyCount, 'MyTable1Count' AS Description FROM Table1
UNION ALL 
SELECT COUNT(*) AS MyCount, 'MyTable2Count' AS Description FROM Table2
UNION ALL 
SELECT COUNT(*) AS MyCount, 'MyTable3Count' AS Description FROM Table3
UNION ALL 
SELECT COUNT(*) AS MyCount, 'MyTable4Count' AS Description FROM Table4

这将生成一个结果集，类似于：

MyCount   Description
----------------------
534      MyTable1Count
33       MyTable2Count
92843    MyTable3Count
931      MyTable4Count

- p.campbell

Answer 3

有几种方法可以完成这个任务，

SELECT
(SELECT COUNT(DISTINCT id) FROM member) AS members,
(SELECT COUNT(DISTINCT id) FROM thread) AS threads,
(SELECT COUNT(DISTINCT id) FROM post) AS posts

或者你可以使用，

SELECT COUNT(DISTINCT member.id), COUNT(DISTINCT thread.id), COUNT(DISTINCT post.id) FROM member, thread, post;

- Mobile Developer iOS Android