表 A 表B
id name id name
1 write write 写
2 read read 读
2 write
3 read
如果我想要得到这样的效果
表C
id name1 name2
1 空 write
2 read write
3 read 空
请问该如何实现呢？
sql ?连接查询
------解决方案--------------------
行转列问题
With A As (
Select 1 As Id, 'write' As Name From Dual
Union All
Select 2 As Id, 'read' As Name From Dual
Union All
Select 2 As Id, 'write' As Name From Dual
Union All
Select 3 As Id, 'write' As Name From Dual
)
Select Id,
Max(Decode(Name,'read','read')) As Name1,
Max(Decode(Name,'write','write')) As Name2
From A
group by id;
------解决方案--------------------
CREATE TABLE Ta(ID VARCHAR2(10),NAME VARCHAR2(20));
INSERT INTO TA VALUES('1','write');
INSERT INTO TA VALUES('2','read');
INSERT INTO TA VALUES('2','write');
INSERT INTO TA VALUES('3','read');
SELECT ID,(SELECT NAME FROM TA WHERE ID=TMP.ID AND NAME='read')NAME1,(SELECT NAME FROM TA WHERE ID=TMP.ID AND NAME='write')NAME2 FROM TA TMP GROUP BY ID;
ID NAME1 NAME2
1 write
2 read write
3 read