如何使用Oracle SQL比较同一列中多个列的数值 [英] How to compare numerical values in the same columns for multiple columns using Oracle SQL

问题描述

我有一个视图，该视图合并了具有这种结构的3个表中的数据.

I have a view that combines data from 3 tables that has this structure.

TABLE    PERIOD  COUNTRY  CODE  POSITION  OCT_14  NOV_14  DEC_14
TABLE_1  Q1      UK       123   EMPL      .2      .2      .2
TABLE_2  Q1      Uk       123   EMPL      .2      .2      .2
TABLE_3  Q1      UK       123   EMPL      .3      .4      .4

我想做的是将OCT_14，NOV_14和DEC_14列的TABLE_3行与TABLE_1和TABLE_2进行比较.如果TABLE_3的数值大于TABLE_1或2的数值，我希望它返回某种类型的标志，例如"Attention".

What I am trying to do is compare the TABLE_3 rows to TABLE_1 and TABLE_2 for the OCT_14, NOV_14 AND DEC_14 columns. If the numerical value for TABLE_3 is > than that of TABLE_1 or 2 I would like it to return some type of flag like 'Attention'.

就期望的输出而言，我可以使用具有唯一名称的新行或诸如OCT_14_R之类的新列.实际上，最好有一个新行.

In terms of desired output, I am ok with either have a new row with a unique name or a new column such as OCT_14_R. Having a new row is actually preferred.

TABLE    PERIOD  COUNTRY  CODE  POSITION  OCT_14  OCT_14_R     NOV_14  DEC_14
TABLE_1  Q1      UK       123   EMPL      .2                    .2      .2
TABLE_2  Q1      Uk       123   EMPL      .2                    .2      .2
TABLE_3  Q1      UK       123   EMPL      .3       Attn         .4      .4

OR

TABLE    PERIOD  COUNTRY  CODE  POSITION  OCT_14  NOV_14  DEC_14
TABLE_1  Q1      UK       123   EMPL      .2      .2      .2
TABLE_2  Q1      Uk       123   EMPL      .2      .2      .2
TABLE_3  Q1      UK       123   EMPL      .3      .4      .4
Comparison                                 attn    attn    attn

推荐答案

Create Table V(TName VARCHAR(5), Oct_14 NUMBER(5,2), Nov_14 NUMBER(5,2), Dec_14 NUMBER(5,2));

Insert into V values('T1', 1.2, 1.2, 1.2);
Insert into V values('T2', 1.4, 1.5, 1.6);
Insert into V values('T3', 1.5, 1.5, 1.7);

SELECT TName, To_Char(Oct_14) Oct_14, To_Char(Nov_14) Nov_14, To_Char(Dec_14) Dec_14 from V
UNION
SELECT 'Compare', 
(select 'ATTN' from dual where EXISTS (select 1 from V where TName = 'T3' and OCT_14 > ALL(SELECT OCT_14 FROM V WHERE TName IN ('T1','T2')))) Oct_14_Res,
(select 'ATTN' from dual where EXISTS (select 1 from V where TName = 'T3' and Nov_14 > ALL(SELECT Nov_14 FROM V WHERE TName IN ('T1','T2')))) Nov_14_Res,
(select 'ATTN' from dual where EXISTS (select 1 from V where TName = 'T3' and Dec_14 > ALL(SELECT Dec_14 FROM V WHERE TName IN ('T1','T2')))) Dec_14_Res
FROM dual;

还有其他更好的方法.

这篇关于如何使用Oracle SQL比较同一列中多个列的数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！