前天笔试碰到的一个题，是列转行的，大家帮看看

前天笔试碰到的一个题，是列转行的，我把SQL贴出来，大家帮我看看，功能是实现了，不知道是否符合要求。

CREATE TABLE userinfo(
id NUMBER,
NAME VARCHAR2(20)
)

CREATE TABLE userdetail(
id NUMBER,
user_id NUMBER,
user_data VARCHAR2(20),
VALUE VARCHAR2(50)
)

INSERT INTO userinfo (id,NAME) VALUES(1,'张三');
INSERT INTO userinfo (id,NAME) VALUES(2,'李四');
INSERT INTO userinfo (id,NAME) VALUES(3,'王五');
COMMIT;


INSERT INTO userdetail (id,USER_ID,USER_DATA,VALUE) VALUES(1,1,'地址','北京市');
INSERT INTO userdetail (id,USER_ID,USER_DATA,VALUE) VALUES(2,1,'电话','123124324324');
INSERT INTO userdetail (id,USER_ID,USER_DATA,VALUE) VALUES(3,2,'证件号','23423423423423423');
INSERT INTO userdetail (id,USER_ID,USER_DATA,VALUE) VALUES(4,3,'邮编','133012');
INSERT INTO userdetail (id,USER_ID,USER_DATA,VALUE) VALUES(5,3,'QQ','567675767');
COMMIT;


SELECT a.NAME AS 姓名,
NVL((SELECT b.VALUE FROM userdetail b WHERE USER_DATA='地址' AND a.ID=b.USER_ID),'空') AS 地址,
NVL((SELECT c.VALUE FROM userdetail c WHERE USER_DATA='电话' AND a.ID=c.USER_ID),'空') AS 电话,
NVL((SELECT d.VALUE FROM userdetail d WHERE USER_DATA='证件号' AND a.ID=d.USER_ID),'空') AS 证件号,
NVL((SELECT e.VALUE FROM userdetail e WHERE USER_DATA='QQ' AND a.ID=e.USER_ID),'空') AS QQ,
NVL((SELECT f.VALUE FROM userdetail f WHERE USER_DATA='邮编' AND a.ID=f.USER_ID),'空') AS 邮编
FROM
(SELECT * FROM userinfo ) a

评论

1 楼 albb0608 2011-03-30  

userinfo 表数据



userdetail 表数据



我的SQL执行结果