使用 SQLite 进行简单的数学计算 - Android

发布于 2024-12-11 09:47:54 字数 413 浏览 0 评论 0原文

我对 SQL 相当陌生，我正在尝试在 Android 应用程序中使用简单的数学进行查询。这就是我想要做的 -

我有一个名为 allfoods 的表，其中的列名为“卡路里”和“大小”。这是查询——

int tempCalories = 100;
int maxCalories = 300;
Cursor dataFoods = myDataBase.rawQuery("SELECT id FROM allfoods WHERE size <> 0 AND (" + tempCalories + " + (calories * (size / 100)) < " + maxCalories +")" , null);

这有效吗？我自己测试它时遇到一些麻烦，因为返回的值变化太大。

谢谢！

原文

I'm fairly new to SQL, and i'm trying to make a query with simple math in the Android app.
Here's what i'm trying to do -

I have a table named allfoods, with columns named 'calories' and 'size'. Here's the query-

int tempCalories = 100;
int maxCalories = 300;
Cursor dataFoods = myDataBase.rawQuery("SELECT id FROM allfoods WHERE size <> 0 AND (" + tempCalories + " + (calories * (size / 100)) < " + maxCalories +")" , null);

Will that work?
I have some trouble testing it myself, since the returned values are too varied.

Thanks!

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

捎一片雪花 2024-12-18 09:47:54

Hello tofira，

是的，如果您将 (size / 100.0)100.0，您的查询将有效>。

但是，您的查询可能会返回 0，因为您的方程式：

100 + (卡路里 * (大小 / 100.0)) 100 + (卡路里 * (大小 / 100.0)) 300

当尺寸和/或卡路里值超出范围时，

将返回 false。证明

为了测试您的查询，我创建了一个包含数据的表和一个测试视图。

数据

为了进行测试，我创建了一个表，其中尺寸值的范围为 1，从 1 到 1000，卡路里的范围为 5，从 5 到 5000，如下所示：

Id  |   size    |   calories
----------------------------
1   |   1       |   5
2   |   2       |   10
3   |   3       |   15
4   |   4       |   20
5   |   5       |   25

测试

为了测试您的方程，我创建了一个查看以验证方程的每一步。

SQL

CREATE VIEW "test" AS 
    SELECT id, size AS 'S', calories AS 'C',
    size/100.0 AS 'S/100',
    calories * (size/100.0) AS 'C*(S/100)', 
    100 + (calories * (size/100.0)) AS '100+(C*(S/100))',
    CASE WHEN (300 > (100 + (calories * (size/100.0)))) 
        THEN 'true' ELSE 'false' END AS 'Eq'
    FROM allfoods;

结果

Id  |  S   |  C    |  S/100  |  C*(S/100)  |  100+(C*(S/100))  |  Eq
---------------------------------------------------------------------------
62  |  62  |  310  |  0.62   |  192.2      |  292.2            |  true
63  |  63  |  315  |  0.63   |  198.45     |  298.45           |  true
64  |  64  |  320  |  0.64   |  204.8      |  304.8            |  false
65  |  65  |  325  |  0.65   |  211.25     |  311.25           |  false
66  |  66  |  330  |  0.66   |  217.8      |  317.8            |  false

结论

针对我的测试数据运行查询将返回第 1 行到第 63 行。

Hello tofira,

Yes, your query will work, if you change the denominator to 100.0 in (size / 100.0).

However, your query might return 0, as your equation:

100 + (calories * (size / 100.0)) < 300

will return false when the values for size and/or calories are out of range.

Proof

To test your query I created a table with data, and a tests view.

Data

To have something to test against, I created a table where the values for size ranged in steps of 1 from 1 to 1000 and calories ranged in steps of 5 from 5 to 5000, like this:

Id  |   size    |   calories
----------------------------
1   |   1       |   5
2   |   2       |   10
3   |   3       |   15
4   |   4       |   20
5   |   5       |   25

Test

To test your equation, I created a view to verify each step of your equation.

SQL

CREATE VIEW "test" AS 
    SELECT id, size AS 'S', calories AS 'C',
    size/100.0 AS 'S/100',
    calories * (size/100.0) AS 'C*(S/100)', 
    100 + (calories * (size/100.0)) AS '100+(C*(S/100))',
    CASE WHEN (300 > (100 + (calories * (size/100.0)))) 
        THEN 'true' ELSE 'false' END AS 'Eq'
    FROM allfoods;

Result

Id  |  S   |  C    |  S/100  |  C*(S/100)  |  100+(C*(S/100))  |  Eq
---------------------------------------------------------------------------
62  |  62  |  310  |  0.62   |  192.2      |  292.2            |  true
63  |  63  |  315  |  0.63   |  198.45     |  298.45           |  true
64  |  64  |  320  |  0.64   |  204.8      |  304.8            |  false
65  |  65  |  325  |  0.65   |  211.25     |  311.25           |  false
66  |  66  |  330  |  0.66   |  217.8      |  317.8            |  false