Android SQLite - 如何在 2 个连接的表上使用 strftime

Question

在我的数据库中，我有 2 个 SQLite 表，每个表都有一个名为 created_at 的列，其中包含从CURRENT_TIMESTAMP获取的 DATETIME 值。

表格如下：

db.execSQL("CREATE TABLE folders (_id INTEGER PRIMARY KEY, " +
        "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
        "parent INTEGER DEFAULT -1, " +
        "hidden INTEGER DEFAULT 0, " +
        "name TEXT);");
db.execSQL("CREATE TABLE folder_items (" +
        "_id INTEGER PRIMARY KEY, " +
        "created_at DATETIME DEFAULT CURRENT_TIMESTAMP, " +
        "parent_id INTEGER, " +
        "item_id INTEGER, " +
        "item_type INTEGER, " +
        "item_date TEXT" +
        ");");

我想使用内部连接和左连接从两个表中选择数据。

这是我到目前为止的代码：

public Cursor getFolderContents(long id, int item_type) {
    SQLiteDatabase db = databaseHelper.getReadableDatabase();
    String args[];
    StringBuilder query = new StringBuilder("SELECT ");
    query.append("pf._id AS pf_id, ");
    query.append("(strftime('%s', pf_created_at) * 1000) AS pf_created_at, ");
    query.append("pf.parent AS pf_parent, ");
    query.append("pf.name AS pf_name, ");
    query.append("f.name AS f_name, ");
    query.append("fi._id AS fi_id, ");
    query.append("(strftime('%s', fi_created_at) * 1000) AS fi_created_at, ");
    query.append("fi.parent_id AS fi_parent_id, ");
    query.append("fi.item_id AS fi_item_id, ");
    query.append("fi.item_type AS fi_item_type, ");
    query.append("fi.item_date AS fi_item_date ");
    query.append("FROM folders pf ");
    query.append("INNER JOIN folder_items fi ON (fi.parent_id = pf._id) ");
    query.append("LEFT JOIN folders f ON (f._id = fi.item_id) ");
    query.append("WHERE pf._id = ? ");
    if (item_type >= 0) {
        query.append("AND fi.item_type = ? ");
        args= new String[]{String.valueOf(id), String.valueOf(item_type)};
    } else {
        args= new String[]{String.valueOf(id)};
    }
    query.append("ORDER BY fi.item_type ASC, LOWER(f.name) ASC, fi.item_date ASC");
    Log.e("getfolder", query.toString());
    return db.rawQuery(query.toString(), args);
}

执行 SELECT 时，将发生以下错误：

no such column: pf_created_at (code 1): , while compiling: 
SELECT pf._id AS pf_id, (strftime('%s', pf_created_at) * 1000) AS pf_created_at,
pf.parent AS pf_parent, pf.name AS pf_name, f.name AS f_name, fi._id AS fi_id, 
(strftime('%s', fi_created_at) * 1000) AS fi_created_at, fi.parent_id AS 
fi_parent_id, fi.item_id AS fi_item_id, fi.item_type AS fi_item_type, 
fi.item_date AS fi_item_date FROM folders pf INNER JOIN folder_items fi ON 
(fi.parent_id = pf._id) LEFT JOIN folders f ON (f._id = fi.item_id) WHERE pf._id 
= ? ORDER BY fi.item_type ASC, LOWER(f.name) ASC, fi.item_date ASC

我假设这发生在：

query.append("(strftime('%s', pf_created_at) * 1000) AS pf_created_at, ");

如何编写 strftime 函数，以便它们从 2 个不同的表中选取列

Answer 1

发现问题：

query.append("(strftime('%s', pf_created_at) * 1000) AS pf_created_at, ");

应该是：

query.append("(strftime('%s', pf.created_at) * 1000) AS pf_created_at, ");

句号在pf.created_at处不下划线。