create table JSJ.JSJ_ZSB_FYK
(
ID VARCHAR2(32),
TITLE VARCHAR2(1000),
CREATED VARCHAR2(20),
MODIFIED VARCHAR2(20),
CREATOR VARCHAR2(32),
MODIFICATOR VARCHAR2(32),
RECYCLE VARCHAR2(2),
XQMC VARCHAR2(1000),
ZHUANGHAO VARCHAR2(200),
FANGHAO VARCHAR2(200),
FHMJ VARCHAR2(200),
FHZH VARCHAR2(200),
QIKU VARCHAR2(200),
QKMJ VARCHAR2(200),
QKZH VARCHAR2(200),
ZIKU VARCHAR2(200),
ZKMJ VARCHAR2(1000),
ZKZH VARCHAR2(1000),
BIANHAO VARCHAR2(200),
LOUCENG VARCHAR2(1000),
LCMJ VARCHAR2(200),
BEIZHU VARCHAR2(1000),
PARENTID VARCHAR2(1000)
)
insert into JSJ_ZSB_FYK (ID, TITLE, CREATED, MODIFIED, CREATOR, MODIFICATOR, RECYCLE, XQMC, ZHUANGHAO, FANGHAO, FHMJ, FHZH, QIKU, QKMJ, QKZH, ZIKU, ZKMJ, ZKZH, BIANHAO, LOUCENG, LCMJ, BEIZHU, PARENTID)
values ('HZ28365c45cac06a0145caf0c8050137', null, '2014-05-05 13:52:28', '2014-05-05 13:55:03', 'admin', 'admin', null, null, '1', '1-1', '100', 'tom1', null, null, null, null, null, null, null, null, null, null, 'HZ28365c45cac06a0145caf0c7330136');
insert into JSJ_ZSB_FYK (ID, TITLE, CREATED, MODIFIED, CREATOR, MODIFICATOR, RECYCLE, XQMC, ZHUANGHAO, FANGHAO, FHMJ, FHZH, QIKU, QKMJ, QKZH, ZIKU, ZKMJ, ZKZH, BIANHAO, LOUCENG, LCMJ, BEIZHU, PARENTID)
values ('HZ28365c45cac06a0145caf0c8050138', null, '2014-05-05 13:52:28', '2014-05-05 13:55:03', 'admin', 'admin', null, null, '1', '1-2', '100', 'tom2', null, null, null, null, null, null, null, null, null, null, 'HZ28365c45cac06a0145caf0c7330136');
insert into JSJ_ZSB_FYK (ID, TITLE, CREATED, MODIFIED, CREATOR, MODIFICATOR, RECYCLE, XQMC, ZHUANGHAO, FANGHAO, FHMJ, FHZH, QIKU, QKMJ, QKZH, ZIKU, ZKMJ, ZKZH, BIANHAO, LOUCENG, LCMJ, BEIZHU, PARENTID)
values ('HZ28365c45cac06a0145caf0c8050139', null, '2014-05-05 13:52:28', '2014-05-05 13:55:03', 'admin', 'admin', null, null, '1', '1-3', '100', 'tom3', null, null, null, null, null, null, null, null, null, null, 'HZ28365c45cac06a0145caf0c7330136');
insert into JSJ_ZSB_FYK (ID, TITLE, CREATED, MODIFIED, CREATOR, MODIFICATOR, RECYCLE, XQMC, ZHUANGHAO, FANGHAO, FHMJ, FHZH, QIKU, QKMJ, QKZH, ZIKU, ZKMJ, ZKZH, BIANHAO, LOUCENG, LCMJ, BEIZHU, PARENTID)
values ('HZ28365c45cac06a0145caf0c805013a', null, '2014-05-05 13:52:28', '2014-05-05 13:55:03', 'admin', 'admin', null, null, '1', '1-4', '100', 'tom4', null, null, null, null, null, null, null, null, null, null, 'HZ28365c45cac06a0145caf0c7330136');
insert into JSJ_ZSB_FYK (ID, TITLE, CREATED, MODIFIED, CREATOR, MODIFICATOR, RECYCLE, XQMC, ZHUANGHAO, FANGHAO, FHMJ, FHZH, QIKU, QKMJ, QKZH, ZIKU, ZKMJ, ZKZH, BIANHAO, LOUCENG, LCMJ, BEIZHU, PARENTID)
values ('HZ28365c45cac06a0145caf0c805013b', null, '2014-05-05 13:52:28', '2014-05-05 13:55:03', 'admin', 'admin', null, null, '1', null, null, null, '1-5', '200', 'tom5', null, null, null, null, null, null, null, 'HZ28365c45cac06a0145caf0c7330136');
insert into JSJ_ZSB_FYK (ID, TITLE, CREATED, MODIFIED, CREATOR, MODIFICATOR, RECYCLE, XQMC, ZHUANGHAO, FANGHAO, FHMJ, FHZH, QIKU, QKMJ, QKZH, ZIKU, ZKMJ, ZKZH, BIANHAO, LOUCENG, LCMJ, BEIZHU, PARENTID)
values ('HZ28365c45cac06a0145caf0c805013c', null, '2014-05-05 13:52:28', '2014-05-05 13:55:03', 'admin', 'admin', null, null, '2', '2-1', null, null, null, null, null, null, null, null, null, null, null, null, 'HZ28365c45cac06a0145caf0c7330136');
insert into JSJ_ZSB_FYK (ID, TITLE, CREATED, MODIFIED, CREATOR, MODIFICATOR, RECYCLE, XQMC, ZHUANGHAO, FANGHAO, FHMJ, FHZH, QIKU, QKMJ, QKZH, ZIKU, ZKMJ, ZKZH, BIANHAO, LOUCENG, LCMJ, BEIZHU, PARENTID)
values ('HZ28365c45cac06a0145caf0c805013d', null, '2014-05-05 13:52:28', '2014-05-05 13:55:03', 'admin', 'admin', null, null, '2', null, null, null, '2-2', '200', 'tom6', null, null, null, null, null, null, null, 'HZ28365c45cac06a0145caf0c7330136');
insert into JSJ_ZSB_FYK (ID, TITLE, CREATED, MODIFIED, CREATOR, MODIFICATOR, RECYCLE, XQMC, ZHUANGHAO, FANGHAO, FHMJ, FHZH, QIKU, QKMJ, QKZH, ZIKU, ZKMJ, ZKZH, BIANHAO, LOUCENG, LCMJ, BEIZHU, PARENTID)
values ('HZ28365c45cac06a0145caf0c805013e', null, '2014-05-05 13:52:28', '2014-05-05 13:55:03', 'admin', 'admin', null, null, '2', null, null, null, '2-3', '100', 'tom7', null, null, null, null, null, null, null, 'HZ28365c45cac06a0145caf0c7330136');
能从这样的数据格式
变成这样吗？
6 个解决方案
#1
两张图贴反了吧，这种问题一般都是从界面显示着手啊
#2
select t1.zhuanghao, t1.fanghao, t1.fhmj, t1.fhzh
from jsj_zsb_fyk t1
where t1.fanghao is not null
union
select t1.zhuanghao, t1.qiku, t1.qkmj, t1.qkzh
from jsj_zsb_fyk t1
where t1.qiku is not null
union all
select t1.zhuanghao, t1.ziku, t1.zkmj, t1.zkzh
from jsj_zsb_fyk t1
where t1.ziku is not null
#3
SELECT COALESCE(FANGHAO, QIKU, ZIKU),
COALESCE(FHMJ, QKMJ, ZKMJ),
COALESCE(FHZH, QKZH, ZKZH)
FROM JSJ_ZSB_FYK T
COALESCE用这个函数很完美。取第一个不为空的值。。
你可以根据自己需要进行罗列。。
#4
2楼正解。。
#5
建议楼主看看case when 语句，适合于行列转换。
#6
3樓的方法正解，問題解決啦，萬分感謝。
#1
两张图贴反了吧，这种问题一般都是从界面显示着手啊
#2
select t1.zhuanghao, t1.fanghao, t1.fhmj, t1.fhzh
from jsj_zsb_fyk t1
where t1.fanghao is not null
union
select t1.zhuanghao, t1.qiku, t1.qkmj, t1.qkzh
from jsj_zsb_fyk t1
where t1.qiku is not null
union all
select t1.zhuanghao, t1.ziku, t1.zkmj, t1.zkzh
from jsj_zsb_fyk t1
where t1.ziku is not null
#3
SELECT COALESCE(FANGHAO, QIKU, ZIKU),
COALESCE(FHMJ, QKMJ, ZKMJ),
COALESCE(FHZH, QKZH, ZKZH)
FROM JSJ_ZSB_FYK T
COALESCE用这个函数很完美。取第一个不为空的值。。
你可以根据自己需要进行罗列。。
#4
2楼正解。。
#5
建议楼主看看case when 语句，适合于行列转换。
#6
3樓的方法正解，問題解決啦，萬分感謝。