如何根据房间数据库中另一个表的数据计算一个表的行

另外，是否可以在查询中进行这样的算术运算-

是的，但是 ADD 不是有效的 SQL，因此：-

也许可以考虑以下查询：-

WITH cte_combine AS (SELECT * FROM `pizza-sales` UNION ALL SELECT * FROM `burger-sales`)
INSERT OR REPLACE INTO `total-sales`
    SELECT date,sum(quantitysold),sum(price * quantitysold) FROM cte_combine GROUP BY date
;

• 注意！ GROUP BY 子句假定日期的格式是无需操作即可排序的（示例使用的日期格式为 YYYY-MM-DD）

为了演示，使用 SQLite 工具，考虑这个本机 SQLite（基于@Entity问题中的注释类）

• 请注意，列类型已假定

:-

DROP TABLE IF EXISTS `pizza-sales`;
DROP TABLE IF EXISTS `burger-sales`;
DROP TABLE IF EXISTS `total-sales`;
CREATE TABLE IF NOT EXISTS `pizza-sales` (id INTEGER PRIMARY KEY, name TEXT, price REAL, quantitySold INTEGER, date TEXT);
CREATE TABLE IF NOT EXISTS `burger-sales` (id INTEGER PRIMARY KEY, name TEXT, price REAL, quantitySold INTEGER, date TEXT);
CREATE TABLE IF NOT EXISTS `total-sales` (/*id INTEGER PRIMARY KEY !!!!!!!!!!probably not required!!!!!!!!!!,*/ date TEXT PRIMARY KEY, totalQuantity INTEGER, totalPrice REAL);

/* Add some pizza data */
INSERT INTO `pizza-sales` (name,price,quantitySold,date) VALUES
    ('Hawian',3.50,3,'2024-03-14')
    ,('Meat Lovers',3.50,2,'2024-03-14')
    ,('Plain',3.50,5,'2024-03-14')
    ,('Hawian',3.50,13,'2024-03-15')
    ,('Meat Lovers',3.50,12,'2024-03-15')
    ,('Plain',3.50,15,'2024-03-15') 
;
/* add some burger data */
INSERT INTO `burger-sales` (name,price,quantitySold,date) VALUES
    ('Big Whatever',3.50,3,'2024-03-14')
    ,('Huge Thinggy',3.50,2,'2024-03-14')
    ,('Cheese',3.50,5,'2024-03-14')
    , ('Big Whatever',3.50,10,'2024-03-15')
    ,('Huge Thinggy',3.50,11,'2024-03-15')
    ,('Cheese',3.50,12,'2024-03-15')
;

SELECT * FROM `pizza-sales`;
SELECT * FROM `burger-sales`;
/* An INSERT SELECT that utilised a CTE (Common table expression) which combines the data from the pizza and burger table
    the INSERT is then driven by the calculated values using the sum function, the summing being based upon the GROUP BY clause
   Duplicate data, according to date, would be replaced
*/
WITH cte_combine AS (SELECT * FROM `pizza-sales` UNION ALL SELECT * FROM `burger-sales`)
INSERT OR REPLACE INTO `total-sales`
    SELECT date,sum(quantitysold),sum(price * quantitysold) FROM cte_combine GROUP BY date
;
SELECT * FROM `total-sales`;

/* Cleanup Demo */

DROP TABLE IF EXISTS `pizza-sales`;
DROP TABLE IF EXISTS `burger-sales`;
DROP TABLE IF EXISTS `total-sales`;

3 的输出SELECT为：-

• 披萨销售

• 汉堡销售

最后是总销售额表：-

也许要注意 SQL 中包含的注释

房间

根据您的代码（已更正并假定类型）将上述内容转换为 Room，因此@Entity带注释的类是：-

@Entity(tableName = "pizza-sales")
data class Pizza(
    @PrimaryKey
    val id: Long?=null,
    val name: String,
    val price: Double,
    val quantitySold: Int,
    val date: String
) //Pizza sales of a day

@Entity(tableName = "burger-sales")
data class Burger(
    @PrimaryKey
    val id: Long?=null,
    val name: String,
    val price: Double,
    val quantitySold: Int,
    val date: String,
) //Burger sales of a day

@Entity(tableName= "total-sales")
data class TotalSales(
    /*val id, probably not required */
    @PrimaryKey
    val date: String,
    val totalQuantity: Int /*= Sum(Pizza[quantitySold]) + Sum(Burger[quantitySold])*/,
    val totalPrice: Double  /* = Sum(Pizza[quantitySold * price]) + Sum(Burger[quantitySold * price])*/
) //Total sales of a day

@Dao带注释的接口（名为 TheDAOs）具有以下功能：-

@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(pizza: Pizza): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(burger: Burger): Long

@Query("WITH cte_combine AS " +
        "    (SELECT * FROM `pizza-sales` UNION ALL SELECT * FROM `burger-sales`) " +
        "INSERT OR REPLACE INTO `total-sales` SELECT date,sum(quantitysold),sum(price * quantitysold) FROM cte_combine GROUP BY date" +
        ";")
fun generateTotals();

@Query("SELECT * FROM `total-sales`")
fun getAllTotals(): List<TotalSales>

并具有以下活动代码（请注意，为了演示的简洁，已经使用了主线程）：-

    db = TheDatabase.getInstance(this)
    dao = db.getTheDAOs()

    dao.insert(Pizza(name = "Hawian", price = 3.50, quantitySold = 3, date = "2024-03-14"))
    dao.insert(Pizza(name = "Meat Lovers", price = 3.50, quantitySold = 2, date = "2024-03-14"))
    dao.insert(Pizza(name = "Plain", price = 3.50, quantitySold = 5, date = "2024-03-14"))
    dao.insert(Pizza(name = "Hawian", price = 3.50, quantitySold = 13, date = "2024-03-15"))
    dao.insert(Pizza(name = "Meat Lovers", price = 3.50, quantitySold = 12, date = "2024-03-15"))
    dao.insert(Pizza(name = "Plain", price = 3.50, quantitySold = 15, date = "2024-03-15"))

    dao.insert(Burger(name = "Big Whatever", price = 3.50, quantitySold =  3, date ="2024-03-14"))
    dao.insert(Burger(name = "Huge Thinggy", price = 3.50, quantitySold =  2, date ="2024-03-14"))
    dao.insert(Burger(name = "Cheese", price = 3.50, quantitySold =  5, date ="2024-03-14"))
    dao.insert(Burger(name = "Big Whatever", price = 3.50, quantitySold =  10, date ="2024-03-15"))
    dao.insert(Burger(name = "Huge Thinggy", price = 3.50, quantitySold =  11, date ="2024-03-15"))
    dao.insert(Burger(name = "Cheese", price = 3.50, quantitySold =  12, date ="2024-03-15"))

    dao.generateTotals()
    for (ts in dao.getAllTotals()) {
        Log.d("DBINFO_1","TS Date=${ts.date} TOTQTY=${ts.totalQuantity} TOTPRICE=${ts.totalPrice}")
    }
    /* do again to show that REPLACE does not affect the end result (but would if prices or quintities changed) */
    dao.generateTotals()
    for (ts in dao.getAllTotals()) {
        Log.d("DBINFO_1","TS Date=${ts.date} TOTQTY=${ts.totalQuantity} TOTPRICE=${ts.totalPrice}")
    }

日志的输出为：-

2024-10-14 19:11:34.902 D/DBINFO_1: TS Date=2024-03-14 TOTQTY=20 TOTPRICE=70.0
2024-10-14 19:11:34.902 D/DBINFO_1: TS Date=2024-03-15 TOTQTY=73 TOTPRICE=255.5
2024-10-14 19:11:34.904 D/DBINFO_1: TS Date=2024-03-14 TOTQTY=20 TOTPRICE=70.0
2024-10-14 19:11:34.904 D/DBINFO_1: TS Date=2024-03-15 TOTQTY=73 TOTPRICE=255.5

使用 App Inspection，总销售额表如下：-

注意！设计很可能会有改进，但这超出了问题/答案的范围

• 例如，为所有销售类型设置一个表格，并用一列来表示类型（可能与类型的表格相关）。