sql - SQL CREATE VIEW 的 id 编号如何在 Android 中工作？

Question

带有 CREATE VIEW 的 id 编号如何在 Android 中工作？

主键(id)是否会自动生成，或者我会遇到错误吗？
(我将尝试将此 View 加载到游标加载器中。)

创建我的交易表的代码:

public static final String TABLE_TRANSACTIONS = "transactions";
public static final String COLUMN_ID = "_id";
public static final String COLUMN_PAYEE = "payee";
public static final String COLUMN_AMOUNT = "amount";
public static final String COLUMN_CATEGORY = "category";
public static final String COLUMN_MONTH = "month";

public void onCreate(SQLiteDatabase db) {
    String sqlQueryTransactions = "CREATE TABLE " + TABLE_TRANSACTIONS + "(" +
            COLUMN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
            COLUMN_PAYEE + " TEXT, " +
            COLUMN_AMOUNT + " TEXT, " +
            COLUMN_CATEGORY + " TEXT, " +
            COLUMN_MONTH + " TEXT " +
            ")";
    db.execSQL(sqlQueryTransactions);
}

表格示例如下:

id  payee       amount  month    
1   Tom         90      March
3   Tom         66      April
4   Tom         89      May
10  Jasmine     125     April
11  Nancy       151     March
12  Jasmine     175     April
13  Nancy       152     April

我要创建并用于在 ListView 中显示的 VIEW 是:

payee   March   April   May
Tom     90      66      89
Jasmine --      300     --
Nancy   151     152     --

该 VIEW 将使用以下 SQL 查询生成:

SELECT `payee`
    , SUM(CASE WHEN `month` = 'March' THEN `amount` END) AS `March`
    , SUM(CASE WHEN `month` = 'April' THEN `amount` END) AS `April`
    , SUM(CASE WHEN `month` = 'May' THEN `amount` END) AS `May` 
  FROM `transactions`
  GROUP BY `payee`;

Answer 1

如果您使用自己的布局和自定义 BaseAdapter或 CursorAdapter那么你就不会遇到错误，

但是如果你使用的是android提供的布局(android.R.layout.simple_list_item_1)和适配器(SimpleCursorAdapter)，那么你会遇到错误。

如果您不关心要显示的 _id 或任何基于它的运算符，那么只需在查询中添加列并使用 android 和适配器的默认布局。

像这样根据您的需要，您可以创建您的 View

create view payee_of_MAM as SELECT _id,payee,
       SUM(CASE WHEN month = 'March' THEN amount END) AS March,
       SUM(CASE WHEN month = 'April' THEN amount END) AS April,
       SUM(CASE WHEN month = 'May' THEN amount END) AS May
  FROM transactions
 GROUP BY payee;

或仅使用查询

SELECT _id,payee,
           SUM(CASE WHEN month = 'March' THEN amount END) AS March,
           SUM(CASE WHEN month = 'April' THEN amount END) AS April,
           SUM(CASE WHEN month = 'May' THEN amount END) AS May
      FROM transactions
     GROUP BY payee;

所以按照这个你会得到这样的主键

_id    payee    March    April    May  
12     Jasmine           300    
13     Nancy    151      152    
4      Tom      90       66       89

您可以通过简单地传递光标来使用android和适配器的默认布局。

但 _id 将根据您表中记录的最后一个条目