问题描述
sqlite表由以下属性组成:
The sqlite table consists of attributes :
|Versions (TEXT)|
| "2.73.8" |
| "3.6.4 " |
| "3.9.11" |
以此类推.
我想从查询中给定的两个版本之间的表中检索所有版本.例如:在版本之间-2.9.10& 3.7.10.
I want to retrieve all the versions from the table between two versions given in the query. For instance: Between versions- 2.9.10 & 3.7.10 .
我找不到任何sqlite函数直接查询此函数.我使用Substring(SUBSTR)进行拆分以获取各个数字,然后可以将这些数字与表中存在的数字进行比较.我这样做很成功,但是我可以找到一种查询方法来检索两个版本集之间的所有版本.
I could not find any sqlite function to query this directly. I used Substring (SUBSTR) to split to get individual digits which could then be compared to the one present in the table. I was successful in doing that but I could find a way to query to retrieve all versions between two version set.
create table prod(version varchar);
insert into prod values('2.7.5');
insert into prod values('2.7.4');
insert into prod values('2.0.0');
insert into prod values('22.73.55');
insert into prod values('22.17.54');
insert into prod values('22.10.06');
insert into prod values('3.7.5');
insert into prod values('3.4.5');
insert into prod values('3.7.6');
查询以获取低于或等于"3.50.6"的所有版本(使用嵌套的"case when"):
Query to retrieve all versions below or equal to : "3.50.6" (using nested "case when" ):
SELECT * from prod
Where version IN ( SELECT
CASE WHEN (CAST(substr(version,0,instr(version,'.')) as integer)=3)
THEN
CASE WHEN (cast(SUBSTR(SUBSTR(version, INSTR(version, '.')),1,INSTR(SUBSTR(version, INSTR(version, '.') + 1), '.') - 1) as float)< 0.50 )
THEN
version
ELSE
CASE WHEN (cast(SUBSTR(SUBSTR(version, INSTR(version, '.')),1,INSTR(SUBSTR(version, INSTR(version, '.') + 1), '.') - 1) as float)=0.50)
THEN
CASE WHEN (CAST(replace(version, rtrim(version, replace(version, '.', '')), '')AS INTEGER)<=6)
THEN version
END END END END FROM prod);
请为我提供一种查询以检索表中两组版本之间的所有版本的方法.
Kindly provide me a way to query to retrieve all versions in the table between two sets of versions.
推荐答案
我假定版本值的3个部分中的每个部分最多3个数字.
将版本值转换为数字以使其具有可比性的最简单方法是:将第一部分乘以1000000,第二部分乘以1000,然后将其加3d部分.
在代码中:
I assume that each one of the 3 parts of the version value is max 3 digits.
The simplest way to convert a version value to a number so to make it comparable, is : multiply the 1st part by 1000000, the 2nd part by 1000 and then add them plus the 3d part.
In code:
1000000 * replace(version, '.', 'x') +
1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
replace(version, '.', '000') % 1000 number
如果执行:
select
version,
1000000 * replace(version, '.', 'x') +
1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
replace(version, '.', '000') % 1000 numericversion
from prod
您得到:
| version | numericversion |
| -------- | -------------- |
| 2.7.5 | 2007005 |
| 2.7.4 | 2007004 |
| 2.0.0 | 2000000 |
| 22.73.55 | 22073055 |
| 22.17.54 | 22017054 |
| 22.10.06 | 22010006 |
| 3.7.5 | 3007005 |
| 3.4.5 | 3004005 |
| 3.7.6 | 3007006 |
因此,要获取2.9.10及以上版本之间的所有版本, 3.7.10,请执行以下操作:
So to get all versions between versions- 2.9.10 & 3.7.10, do this:
with
cte1 as (
select
1000000 * replace('2.9.10', '.', 'x') +
1000 * replace(substr('2.9.10', instr('2.9.10', '.') + 1), '.', 'x') +
replace('2.9.10', '.', '000') % 1000 numericversion
),
cte2 as (
select
1000000 * replace('3.7.10', '.', 'x') +
1000 * replace(substr('3.7.10', instr('3.7.10', '.') + 1), '.', 'x') +
replace('3.7.10', '.', '000') % 1000 numericversion
),
versions as (
select
version,
1000000 * replace(version, '.', 'x') +
1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
replace(version, '.', '000') % 1000 numericversion
from prod
)
select version from versions
where numericversion between
(select numericversion from cte1) and (select numericversion from cte2)
第一个CTE返回数值2.9.10
,第二个CTE返回数值3.7.10
,而3d返回表中所有版本的数值.
最后,查询会比较数字版本.
请参见演示.
结果:
The 1st CTE returns the numeric value of 2.9.10
, the 2nd CTE returns the numeric value of 3.7.10
and the 3d the numeric values of all the versions in the table.
Finally the query compares the numeric versions.
See the demo.
Results:
| version |
| ------- |
| 3.7.5 |
| 3.4.5 |
| 3.7.6 |
或者通过将2个版本硬编码为数字来不带CTE:
Or without the CTEs by hardcoding the 2 versions as numbers:
select version from prod
where
1000000 * replace(version, '.', 'x') +
1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
replace(version, '.', '000') % 1000
between 2009010 and 3007010
请参见演示.
或者:
See the demo.
Or:
select version from prod
where
1000000 * replace(version, '.', 'x') +
1000 * replace(substr(version, instr(version, '.') + 1), '.', 'x') +
replace(version, '.', '000') % 1000
between
1000000 * replace('2.9.10', '.', 'x') +
1000 * replace(substr('2.9.10', instr('2.9.10', '.') + 1), '.', 'x') +
replace('2.9.10', '.', '000') % 1000
and
1000000 * replace('3.7.10', '.', 'x') +
1000 * replace(substr('3.7.10', instr('3.7.10', '.') + 1), '.', 'x') +
replace('3.7.10', '.', '000') % 1000
请参见演示.
这篇关于SQLITE查询-要检索两个版本之间的所有版本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!