SQLITE查询-要检索两个版本之间的所有版本? [英] SQLITE Query- To retrieve all versions between two versions?

问题描述

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查询-要检索两个版本之间的所有版本?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！