如何比较单列中的行

Question

>我在 Oracle 数据库中有如下数据

Name   Session     CATEGORY   HT     WT     BMI
John   SESSION 1   OW         1.5    64.6   28.71
John   SESSION 3   OW         1.5    64.6   28.71
Eric   SESSION 1   OW         1.48   55.7   25.43
Eric   SESSION 3   OW         1.52   55.4   23.98

我已经使用枢轴转换了上述数据

Name  session_1_Category  Session_1_height  session_1_weight  session_1_BMI  session_3_Category  Session_3_height  session_3_weight  session_3_BMI
John  OW                  1.5               64.6              28.71          OW                  1.5               64.6              28.71
Eric  OW                  1.48              55.7              25.43          OW                  1.52              55.4              23.98

我想将会话 1 的高度与会话 3 的高度进行比较，将会话

1 的权重与会话 3 的权重进行比较。

例如

if SESSION_1_HEIGHT =SESSION_3_HEIGHT then height_status=true else height_status=false 
if SESSION_1_WEIGHT =SESSION_3_WEIGHT then weight_status=true else weight_status=false
if SESSION_1_BMI =SESSION_3_BMI then bmi_status=true else bmi_status=false

请看这里 SQLFiddle

Answer 1

我对Oracle和PIVOT不是很熟悉，但您可以使用CASE进行比较。

我不得不删除SELECT *并将其替换为字段列表，我不确定为什么有必要抱歉。

SELECT name, category, 
session_1_height, session_1_weight, session_1_bmi,
session_3_height, session_3_weight, session_3_bmi,
CASE WHEN session_1_height = session_3_height THEN 'true' ELSE 'false' END AS height_status,
CASE WHEN session_1_weight = session_3_weight THEN 'true' ELSE 'false' END AS weight_status,
CASE WHEN session_1_bmi = session_3_bmi THEN 'true' ELSE 'false' END AS bmi_status
FROM   table_name
PIVOT(
    MAX( HT ) AS height,
    MAX( WT ) AS weight,
    MAX( BMI ) AS BMI
    FOR "Session" IN (
        'SESSION 1' AS Session_1,
        'SESSION 3' AS Session_3
    )
);

Answer 2

对于您在 SqlFiddle 采样的表：

CREATE TABLE TABLE_NAME ( Name, "Session", CATEGORY, HT, WT, BMI ) AS
SELECT 'John', 'SESSION 1', 'OW', 1.5,  64.6, 28.71 FROM DUAL UNION ALL
SELECT 'John', 'SESSION 3', 'OW', 1.52, 63.2, 27.35 FROM DUAL UNION ALL
SELECT 'Eric', 'SESSION 1', 'OW', 1.48, 55.7, 25.43 FROM DUAL UNION ALL
SELECT 'Eric', 'SESSION 3', 'OW', 1.52, 55.4, 23.98 FROM DUAL;

这应该给出您想要的结果：

SELECT T.*, CASE WHEN T.SESSION_1_HEIGHT = T.SESSION_3_HEIGHT THEN 1 ELSE 0 END AS HeightEquals, CASE WHEN T.SESSION_1_WEIGHT = T.SESSION_3_WEIGHT THEN 1 ELSE 0 END AS WeightEquals, CASE WHEN T.SESSION_1_BMI = T.SESSION_3_BMI THEN 1 ELSE 0 END AS BmiEquals FROM (
  SELECT *
  FROM table_name
  PIVOT(
    MAX( HT ) AS height,
    MAX( WT ) AS weight,
    MAX( BMI ) AS BMI
    FOR "Session" IN (
      'SESSION 1' AS Session_1,
      'SESSION 3' AS Session_3
    )
  )
) T;

希望对您有所帮助。