SQLite3中使用除法的计算字段 [英] Calculation field using division in SQLite3

问题描述

我有一个包含 2 个 INT 字段的 sqlite3 表.(通过和失败)

I have a sqlite3 table with 2 fields of INT. (Passed and Failed)

我想计算 % Passed 所以我使用以下 SQL

I want to calculate % Passed so am using the following SQL

SELECT id,
       passed,
       failed,
       passed / (passed + failed )* 100 AS 'passedPC'
  FROM myData

如果 Passed 是 23 而 Failed 是 3 我得到 0 返回这是意外的.

if Passed is 23 and Failed is 3 I am getting 0 returned which is unexpected.

这是为什么?

推荐答案

这是因为你在做整数除法和 23/26=0.您需要强制它进行浮点除法.最简单的方法是将一个值与 1.0 相乘，如下所示:

It's because you are doing integer division and 23/26=0. You need to force it to do floating point division instead. The easiest way is to multiply one value with 1.0 like this:

SELECT id, passed, failed, ((passed * 1.0) / (passed + failed )) * 100 AS 'passedPC' 
FROM myData

或者，您可以使用 cast 运算符 强制 aa 列到 实数数据类型:

Alternatively you could use the cast operator to force a a column to the real data type:

SELECT id, passed, failed, cast(passed as real) / (passed + failed ) * 100 AS 'passedPC' 
FROM myData;

示例 SQL Fiddle 显示差异.

这篇关于SQLite3中使用除法的计算字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！