从sqlite中的文本字段中选择最大值 [英] Selecting max value from text field in sqlite
问题描述
我在Windows CE设备上有一个SQLite数据库。它有一个名为product_code的列。存储在此列中的值包含字母和字符。第一个字符取决于产品所在的部门，例如对于Spirits S而言，它将是D，一个示例product_code列条目将是S124或S09。
在设备上，我有一个表单可以将新产品添加到数据库中。我希望表单能够生成下一个可用的产品代码。为此，我需要为特定部门选择product_code的最大值（从组合框中选择）。
我试过使用这个：
I have a SQLite database on a Windows CE device. It has a column called product_code. The values stored in this column contain both letters and characters. The first character is dependent on the department in which the product is, for example for Draught it would be D, for Spirits S. A sample product_code column entry would be S124 or S09.
On the device, I have a form to add new products to the database. I want the form to be able to generate the next available product code. In order to do that, I need to select the max value of a product_code for a specific department (selected from a combo box).
I have tried using this:
stm = "SELECT MAX(CAST(product_code AS Int)) AS maxPCode FROM product WHERE department_description = '" + cmbDeps.Text.ToString() + "';";
我也试过：
I have also tried:
stm = "SELECT MAX(CAST(product_code AS Int)) AS maxPCode FROM product WHERE product_code NOT LIKE '%[a-z]%' AND ISNUMERIC(product_code) = 1 AND department_description = '" + cmbDeps.Text.ToString() + "';";
但似乎ISNUMERIC（）不是SQLite中的函数？有没有其他方法来解决这个问题？我在考虑可能使用replace（），但不确定是否有办法剥去任何信件，或者我是否必须为每个可能的部门设置条件？
我尝试过：
But seems like ISNUMERIC() is not a function in SQLite? Is there any other way to get around this problem? I was thinking about possibly using replace(), but not sure if there is a way to strip any letter or if I have to have a condition for each possible department?
What I have tried:
string maxPcode = "";
using (SQLiteConnection con = new SQLiteConnection(cs1))
{
string stm;
stm = "SELECT MAX(CAST(product_code AS Int)) AS maxPCode FROM product WHERE product_code NOT LIKE '%[a-z]%' AND ISNUMERIC(product_code) = 1 AND department_description = '" + cmbDeps.Text.ToString() + "';";
con.Open();
using (SQLiteCommand cmd = new SQLiteCommand(stm, con))
{
using (SQLiteDataReader rdr = cmd.ExecuteReader())
{
while (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("maxPCode")))
{
rdr.GetOrdinal("product_code");
maxPcode = rdr.GetString(0);
}
}
}
}
con.Close();
}
推荐答案
您好，
使用非数字值的数学运算的结果将变为null，因此您可以尝试使用以下内容替换isnumeric：
Hello,
The result of a mathematical operation with non numeric value will turn into null so you can give a try to replace isnumeric with the following:
select max(cast(your_column as integer)) from your_table where ifnull(your_column + 1, null) is not null
注1：为什么不使用ExecuteScalar（）？
注意2：尝试使用尽可能多的参数化查询而不是连接
编辑：< br $> b $ b
Note 1: Why do you not use ExecuteScalar()?
Note 2: Try to use as much as possible parameterized queries instead of concatenations
select 'A' || substr('00000000' || cast(max(cast(substr(product_code, 2) as integer)) + 1 as text), -4) from products where substr(product_code, 1, 1) = 'A'
substr（product_code，2）给你数字部分
cast（max（cast（substr（product_code，2）as integer））+ 1给你下一个id
最后一个substr给你一个格式化的数字id
最后一个连接给你你想要的东西
"substr(product_code, 2)" gives you numeric part
"cast(max(cast(substr(product_code, 2) as integer)) + 1" gives you next id
the last substr gives you a formatted numerical id
the last concatenate gives you what you want
这篇关于从sqlite中的文本字段中选择最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
