Question

oracle sql跨表查询问题

有2个表：
-- Create table
create table T_SALARY
(
zgh VARCHAR2(40),
fgznf VARCHAR2(4),
fgzyf VARCHAR2(2),
A1 VARCHAR2(20) default 0.00,
A2 VARCHAR2(20) default 0.00,
A3 VARCHAR2(20) default 0.00,
A4 VARCHAR2(20) default 0.00,
A5 VARCHAR2(20) default 0.00

)
INSERT INTO T_SALARY VALUES('95001','2014','1','11.00','12.00','13.00','14.00','15.00');
INSERT INTO T_SALARY VALUES('95001','2014','2','21.00','22.00','23.00','24.00','25.00');
INSERT INTO T_SALARY VALUES('95001','2014','3','31.00','32.00','33.00','34.00','35.00');

-- Create table
create table T_TABLE_ITEMS
(
wid VARCHAR2(40) not null,
col_name VARCHAR2(40) not null,
col_chname VARCHAR2(100),
is_use VARCHAR2(1)
)

INSERT INTO T_TABLE_ITEMS VALUES('1','A1','内退工资','1');
INSERT INTO T_TABLE_ITEMS VALUES('2','A2','岗位工资','1');
INSERT INTO T_TABLE_ITEMS VALUES('3','A3','薪级工资','1');
INSERT INTO T_TABLE_ITEMS VALUES('4','A4','院长基金10%','1');
INSERT INTO T_TABLE_ITEMS VALUES('5','A5','房贴','0');
自己写了一个SQL：
select t.col_chname,(select t.col_name from T_SALARY a where a.zgh='95001' and a.fgznf='2014' and a.fgzyf='2') as value
from T_TABLE_ITEMS t where t.is_use='1' and t.col_type='1'
得到结果是：

VALUE那一列不是我想要的结果，我想要的是95001在2014年2月的数据，正确结果应该是：
COL_CHNAME VALUE内退工资 21.00岗位工资 22.00薪级工资 23.00院长基金10% 24.00房贴 25.00

这个SQL要怎么写呢？求解，谢谢

Answer 1

select t.col_chname,(select t.col_name from T_SALARY a where a.zgh='95001' and a.fgznf='2014' and a.fgzyf='2') as value
from T_TABLE_ITEMS t where t.is_use='1' and t.col_type='1'

--- t.col_type='1' ，根本没有这个col_type字段嘛，是不是误写了

追问

是的，多写了where条件，实际不需要的。
select t.col_chname,(select t.col_name from T_SALARY a where a.zgh='95001' and a.fgznf='2014' and a.fgzyf='2') as value
from T_TABLE_ITEMS t
但是还是不对的，请帮忙看看，能不能改一下了，谢谢

追答

SELECT T.col_chname,S.COL_VAL
FROM T_TABLE_ITEMS T,
(
select ZGH,FGZNF,FGZYF,'A1' COL,A1 AS COL_VAL from T_SALARY
UNION ALL
select ZGH,FGZNF,FGZYF,'A2' COL,A2 AS COL_VAL from T_SALARY
UNION ALL
select ZGH,FGZNF,FGZYF,'A3' COL,A3 AS COL_VAL from T_SALARY
UNION ALL
select ZGH,FGZNF,FGZYF,'A4' COL,A4 AS COL_VAL from T_SALARY
UNION ALL
select ZGH,FGZNF,FGZYF,'A5' COL,A5 AS COL_VAL from T_SALARY
) S
WHERE T.col_name=S.COL
AND S.zgh = '95001'
and S.fgznf = '2014'
and S.fgzyf = '2'