SQL Server iOS Version Target query

vkc1a9a2  于 12个月前  发布在  iOS
关注(0)|答案(2)|浏览(100)

I have two tables: 1) current iOS versions and 2) target iOS version and wanted to get a target iOS version for each version, how should I go about doing this?
| iOS Version |
| ------------ |
| 11.0.0 |
| 12.0.1 |
| 13.5.0 |
| 16.4.1 |
| 17.1.0 |

Target iOS Version
12.5.7
15.8.0
17.2.0

Goal: The goal was for each current version to have a target version to be matched with, i.e. if the current version is 12.0.1, the target version would be 12.5.7 but if the current version exceeds 12.5.7, for example 13.5.0 then the target version would be 15.8.0 but not the latest 17.2.0.
| iOS Version | Target iOS Version |
| ------------ | ------------ |
| 11.0.0 | 12.5.7 |
| 12.0.1 | 12.5.7 |
| 13.5.0 | 15.8.0 |
| 16.4.1 | 17.2.0 |
| 17.1.0 | 17.2.0 |

ikfrs5lh

ikfrs5lh1#

A correlated subquery in the select list is probably the most basic solution:

select c.[iOS Version],
       (select min([Target iOS Version]) from targetosversionstable
        where [Target iOS Version] > c.[iOS Version]) as [Target iOS Version]
from currentosversionstable c
7xzttuei

7xzttuei2#

One method is a cartesian join and getting the min(t.version) greater than each version.

select c.version, min(t.version) as target_ios_version
  from ios_version c, target_ios_version t
 where t.version > c.version
 group by c.version
--or using Cross Join
select c.version, min(t.version) as target_ios_version
  from ios_version c
 cross join target_ios_version t
 where t.version > c.version
 group by c.version
versiontarget_ios_version
11.0.012.5.7
12.0.112.5.7
13.5.015.8.0
16.4.117.2.0
17.1.017.2.0

相关问题