--建表 CREATE TABLE test(a VARCHAR2(10),b VARCHAR2(10),c VARCHAR2(10));INSERT INTO test VALUES('1','3','222'); INSERT INTO test VALUES('1','1','333'); INSERT INTO test VALUES('3','22','444'); INSERT INTO test VALUES('3','2',''); INSERT INTO test VALUES('5','5',''); INSERT INTO test VALUES('6','6','');--將a,b相等的記錄拿出來和原Table做外連接再用decode轉一下 WITH t1 AS (SELECT DISTINCT a FROM test),t2 AS (SELECT DISTINCT a AS B FROM test WHERE a=b)SELECT T3.A,Decode(T3.A,T3.B,'同意','不同意') comt FROM (SELECT * FROM T1,T2 WHERE T1.A=T2.B(+) ORDER BY T1.A)T3;--执行结果 A COMT 1 同意 3 不同意 5 同意 6 同意
select a,max(d) from (select a,b,c,decode(a-b,0,'同意','不同意') d from test ) group by a
CREATE TABLE test(a VARCHAR2(10),b VARCHAR2(10),c VARCHAR2(10)); ------------------------------------------------------------------------ INSERT INTO test VALUES('1','3','222'); INSERT INTO test VALUES('1','1','333'); INSERT INTO test VALUES('3','22','444'); INSERT INTO test VALUES('3','2',''); INSERT INTO test VALUES('5','5',''); INSERT INTO test VALUES('6','6',''); ----------------------------------------------------------------------------- select a.A,a.b, (case when a.A=a.B then '同意' else '不同意' end) as 比较结果 from TEST a ---------------------------------------------------------------------------------- A B 比较结果 1 3 不同意 1 1 同意 3 22 不同意 3 2 不同意 5 5 同意 6 6 同意
--建表
CREATE TABLE test(a VARCHAR2(10),b VARCHAR2(10),c VARCHAR2(10));INSERT INTO test VALUES('1','3','222');
INSERT INTO test VALUES('1','1','333');
INSERT INTO test VALUES('3','22','444');
INSERT INTO test VALUES('3','2','');
INSERT INTO test VALUES('5','5','');
INSERT INTO test VALUES('6','6','');--將a,b相等的記錄拿出來和原Table做外連接再用decode轉一下
WITH t1 AS (SELECT DISTINCT a FROM test),t2 AS (SELECT DISTINCT a AS B FROM test WHERE a=b)SELECT T3.A,Decode(T3.A,T3.B,'同意','不同意') comt FROM (SELECT * FROM T1,T2 WHERE T1.A=T2.B(+) ORDER BY T1.A)T3;--执行结果
A COMT
1 同意
3 不同意
5 同意
6 同意
(select a,b,c,decode(a-b,0,'同意','不同意') d from test
)
group by a
------------------------------------------------------------------------
INSERT INTO test VALUES('1','3','222');
INSERT INTO test VALUES('1','1','333');
INSERT INTO test VALUES('3','22','444');
INSERT INTO test VALUES('3','2','');
INSERT INTO test VALUES('5','5','');
INSERT INTO test VALUES('6','6','');
-----------------------------------------------------------------------------
select a.A,a.b,
(case when a.A=a.B then '同意' else '不同意' end) as 比较结果
from TEST a
----------------------------------------------------------------------------------
A B 比较结果
1 3 不同意
1 1 同意
3 22 不同意
3 2 不同意
5 5 同意
6 6 同意