时间戳

首页 > 解决方案 > SQL_Data_Manipulation

sql - SQL_Data_Manipulation

问题描述

     Indicator   Letter   Number  Value
1    Control       A       119    1494
2    Test          A       119    1083
3    Control       A       121    834
4    Test          A       121    638
5    Control       B       121    319
6    Test          B       121    378

我想创建一个新列，在测试和控制之间运行计算，其中字母和数字在测试和控制之间相等。因此，例如前两行将是 (1083-1494)/1083

标签： sql

解决方案

应该这样工作：

SELECT letter, number, (test.value - control.value) / test.value as result
FROM t as control
JOIN t as test 
ON
  control.letter = test.letter AND
  control.number = test.number AND 
  control.indicator = 'Control' AND 
  test.indicator = 'Test'

编辑（除以零）：根据您要如何处理 0 值，最通用的解决方案是CASE表达式

SELECT 
  letter, 
  number, 
  CASE WHEN test.value IS NULL OR test.value = 0 
    THEN 'Undefined'
    ELSE (test.value - control.value) / test.value 
  END AS result
FROM t AS control
JOIN t AS test 
ON
  control.letter = test.letter AND
  control.number = test.number AND 
  control.indicator = 'Control' AND 
  test.indicator = 'Test'

推荐阅读