如何计算相对于两列条目的长度

Question

下面提到的代码可以做得更紧凑吗，我正在尝试计算同一机架到同一机架的电缆长度，其中长度固定为 6mts，然后从一个机架到另一个机架，每个机架增加 .5mtrs，其余是一些随机长度具有以下列的数据库 r

ID        RACK_A           RACK_B          LENGTH
1          RK 1             RK 1
2          RK 2             RK 1
3          RK 1             RK 2
4          MCR DESK         PCR
5          CAR              INGEST
6          PCR              CAR

依此类推，大约 2000 个条目

UPDATE MASTER_SCHEDULE
SET CABLE_LENTH = 
    switch(
          RACK_A='RK 1' AND RACK_B='RK 1', 6
        , RACK_A='RK 2' AND RACK_B='RK 2', 6
        , RACK_A='RK 3' AND RACK_B='RK 3', 6
        , RACK_A='RK 4' AND RACK_B='RK 4', 6
        , RACK_A='RK 5' AND RACK_B='RK 5', 6
        , RACK_A='RK 6' AND RACK_B='RK 6', 6
        , RACK_A='RK 7' AND RACK_B='RK 7', 6
and so on say more 20 statements all with LENGTH = 6
        , RACK_A='RK 1' AND RACK_B='RK 2' OR RACK_A = 'RK 2' AND RACK_B = 'RK 1',6.5
        , RACK_A='RK 1' AND RACK_B='RK 3' OR RACK_A = 'RK 3' AND RACK_B = 'RK 1',7
        , RACK_A='RK 1' AND RACK_B='RK 4' OR RACK_A = 'RK 4' AND RACK_B = 'RK 1',7.5
        , RACK_A='RK 1' AND RACK_B='RK 5' OR RACK_A = 'RK 5' AND RACK_B = 'RK 1',8
        , RACK_A='RK 1' AND RACK_B='RK 6' OR RACK_A = 'RK 6' AND RACK_B = 'RK 1',8.5
        , RACK_A='RK 1' AND RACK_B='RK 7' OR RACK_A = 'RK 7' AND RACK_B = 'RK 1',9
and so on each LENGTH incremented by +.5
      ,  RACK_A= 'MCR DESK' AND RACK_B='PCR=' OR RACK_A= 'PCR' AND RACK_B='MCR_DESK',54
      ,  RACK_A= 'CAR' AND RACK_B='INGEST' OR RACK_A= 'INGEST' AND RACK_B='CAR',47 
  and so on each length is random figure ,        
        , true, 0)
WHERE ID>=1 AND ID<= 2000

Answer 1

根据此处给出的示例数据，我假设第 RACK_A 列和RACK_B中的所有数据都具有"RK {房间号}"格式。然后你可以试试这个查询：

UPDATE MASTER_SCHEDULE
SET CABLE_LENTH = 
    ABS((RIGHT(RACK_A, LEN(RACK_A)-3) - RIGHT(RACK_B, LEN(RACK_B)-3)))
        *
    0.5
        +
    6
WHERE ID>=1 AND ID<= 2000

供参考 ： MS 访问： 函数

Answer 2

下面是SQL Server的代码，我将在几分钟内为您提供MS ACCESS的代码。但是，如果您知道我在查询中使用的MS-ACCESS的类似功能，那么您可以自己尝试转换

UPDATE MASTER_SCHEDULE
SET LENGTH = ABS( CAST(SUBSTRING(RACK_A, CHARINDEX(' ',RACK_A)+1, LEN(RACK_A) - CHARINDEX   (' ',RACK_A)) AS INT)
-  CAST(SUBSTRING(RACK_B, CHARINDEX(' ',RACK_B)+1, LEN(RACK_B) - CHARINDEX(' ',RACK_B)) AS INT))  * 0.5 + 6 

我在查询中所做的是提取列的整数部分 RACK_A 和 RACK_B ，减去值，取结果的绝对值（如果减法导致负值，则需要），乘以 0.5，最后加 6。

看到它在SQL Fiddle：http://sqlfiddle.com/#!3/0abad/1 工作

以下是 MS-ACCESS 的相应代码：

UPDATE MASTER_SCHEDULE SET MASTER_SCHEDULE.LENGTH = Abs(CInt(Mid(RACK_A,InStr(1,RACK_A,' ')+1,Len(RACK_A)-InStr(1,RACK_A,' ')))-CInt(Mid(RACK_B,InStr(1,RACK_B,' ')+1,Len(RACK_B)-InStr(1,RACK_B,' '))))*0.5+6;

让我知道它是否按预期工作。