有一个表,500个列,列名从ITEM_1,一直到ITEM_500,这个字段存放的字段值为"S",但是会有不少列没有值的,在查询的时候怎么把这些列给排除掉?
建表如下:
create table T_DM_IMP_S_MARK
(
GUID VARCHAR2(36) default SYS_GUID() not null,
MODEL_CN VARCHAR2(30) not null,
BODY_TYPE_CN VARCHAR2(30) not null,
ENGINE VARCHAR2(10) not null,
TRAN_TYPE_CN VARCHAR2(10) not null,
WC_CN VARCHAR2(10) not null,
GRADE VARCHAR2(15) not null,
VERSION VARCHAR2(4) not null,
ITEMS_CN VARCHAR2(30) not null,
IS_ENABLE VARCHAR2(2) default '1' not null,
CREATOR VARCHAR2(50),
CREATED_DATE TIMESTAMP default SYSTIMESTAMP,
MODIFIER VARCHAR2(50),
LAST_UPDATED_DATE TIMESTAMP default SYSTIMESTAMP,
BUTYPE VARCHAR2(10),
constraint PK_T_DM_IMP_S_MARK primary key (GUID)
);
--表名称:S表标记信息导入,表编码:T_DM_IMP_S_MARK,修改类型:修改表
alter table T_DM_IMP_S_MARK add SUB_SEGMENT VARCHAR2(15);
comment on column T_DM_IMP_S_MARK.SUB_SEGMENT is '小区隔';alter table T_DM_IMP_S_MARK add OID VARCHAR2(450);
comment on column T_DM_IMP_S_MARK.OID is '车型唯一键';alter table T_DM_IMP_S_MARK add ITEM_1 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_1 is '项目1';alter table T_DM_IMP_S_MARK add ITEM_2 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_2 is '项目2';alter table T_DM_IMP_S_MARK add ITEM_3 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_3 is '项目3';alter table T_DM_IMP_S_MARK add ITEM_4 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_4 is '项目4';alter table T_DM_IMP_S_MARK add ITEM_5 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_5 is '项目5';alter table T_DM_IMP_S_MARK add ITEM_6 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_6 is '项目6';alter table T_DM_IMP_S_MARK add ITEM_7 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_7 is '项目7';alter table T_DM_IMP_S_MARK add ITEM_8 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_8 is '项目8';alter table T_DM_IMP_S_MARK add ITEM_9 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_9 is '项目9';alter table T_DM_IMP_S_MARK add ITEM_10 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_10 is '项目10';alter table T_DM_IMP_S_MARK add ITEM_11 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_11 is '项目11';alter table T_DM_IMP_S_MARK add ITEM_12 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_12 is '项目12';alter table T_DM_IMP_S_MARK add ITEM_13 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_13 is '项目13';alter table T_DM_IMP_S_MARK add ITEM_14 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_14 is '项目14';alter table T_DM_IMP_S_MARK add ITEM_15 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_15 is '项目15';alter table T_DM_IMP_S_MARK add ITEM_16 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_16 is '项目16';alter table T_DM_IMP_S_MARK add ITEM_17 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_17 is '项目17';alter table T_DM_IMP_S_MARK add ITEM_18 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_18 is '项目18';alter table T_DM_IMP_S_MARK add ITEM_19 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_19 is '项目19';alter table T_DM_IMP_S_MARK add ITEM_20 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_20 is '项目20';alter table T_DM_IMP_S_MARK add ITEM_21 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_21 is '项目21';alter table T_DM_IMP_S_MARK add ITEM_22 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_22 is '项目22';alter table T_DM_IMP_S_MARK add ITEM_23 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_23 is '项目23';alter table T_DM_IMP_S_MARK add ITEM_24 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_24 is '项目24';alter table T_DM_IMP_S_MARK add ITEM_25 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_25 is '项目25';alter table T_DM_IMP_S_MARK add ITEM_26 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_26 is '项目26';alter table T_DM_IMP_S_MARK add ITEM_27 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_27 is '项目27';alter table T_DM_IMP_S_MARK add ITEM_28 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_28 is '项目28';alter table T_DM_IMP_S_MARK add ITEM_29 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_29 is '项目29';alter table T_DM_IMP_S_MARK add ITEM_30 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_30 is '项目30';alter table T_DM_IMP_S_MARK add ITEM_31 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_31 is '项目31';alter table T_DM_IMP_S_MARK add ITEM_32 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_32 is '项目32';alter table T_DM_IMP_S_MARK add ITEM_33 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_33 is '项目33';alter table T_DM_IMP_S_MARK add ITEM_34 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_34 is '项目34';alter table T_DM_IMP_S_MARK add ITEM_35 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_35 is '项目35';alter table T_DM_IMP_S_MARK add ITEM_36 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_36 is '项目36';alter table T_DM_IMP_S_MARK add ITEM_37 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_37 is '项目37';alter table T_DM_IMP_S_MARK add ITEM_38 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_38 is '项目38';alter table T_DM_IMP_S_MARK add ITEM_39 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_39 is '项目39';alter table T_DM_IMP_S_MARK add ITEM_40 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_40 is '项目40';alter table T_DM_IMP_S_MARK add ITEM_41 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_41 is '项目41';alter table T_DM_IMP_S_MARK add ITEM_42 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_42 is '项目42';alter table T_DM_IMP_S_MARK add ITEM_43 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_43 is '项目43';alter table T_DM_IMP_S_MARK add ITEM_44 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_44 is '项目44';alter table T_DM_IMP_S_MARK add ITEM_45 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_45 is '项目45';alter table T_DM_IMP_S_MARK add ITEM_46 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_46 is '项目46';alter table T_DM_IMP_S_MARK add ITEM_47 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_47 is '项目47';alter table T_DM_IMP_S_MARK add ITEM_48 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_48 is '项目48';alter table T_DM_IMP_S_MARK add ITEM_49 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_49 is '项目49';alter table T_DM_IMP_S_MARK add ITEM_50 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_50 is '项目50';alter table T_DM_IMP_S_MARK add ITEM_51 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_51 is '项目51';alter table T_DM_IMP_S_MARK add ITEM_52 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_52 is '项目52';alter table T_DM_IMP_S_MARK add ITEM_53 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_53 is '项目53';alter table T_DM_IMP_S_MARK add ITEM_54 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_54 is '项目54';alter table T_DM_IMP_S_MARK add ITEM_55 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_55 is '项目55';alter table T_DM_IMP_S_MARK add ITEM_56 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_56 is '项目56';alter table T_DM_IMP_S_MARK add ITEM_57 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_57 is '项目57';alter table T_DM_IMP_S_MARK add ITEM_58 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_58 is '项目58';alter table T_DM_IMP_S_MARK add ITEM_59 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_59 is '项目59';alter table T_DM_IMP_S_MARK add ITEM_60 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_60 is '项目60';alter table T_DM_IMP_S_MARK add ITEM_61 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_61 is '项目61';alter table T_DM_IMP_S_MARK add ITEM_62 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_62 is '项目62';alter table T_DM_IMP_S_MARK add ITEM_63 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_63 is '项目63';
建表如下:
create table T_DM_IMP_S_MARK
(
GUID VARCHAR2(36) default SYS_GUID() not null,
MODEL_CN VARCHAR2(30) not null,
BODY_TYPE_CN VARCHAR2(30) not null,
ENGINE VARCHAR2(10) not null,
TRAN_TYPE_CN VARCHAR2(10) not null,
WC_CN VARCHAR2(10) not null,
GRADE VARCHAR2(15) not null,
VERSION VARCHAR2(4) not null,
ITEMS_CN VARCHAR2(30) not null,
IS_ENABLE VARCHAR2(2) default '1' not null,
CREATOR VARCHAR2(50),
CREATED_DATE TIMESTAMP default SYSTIMESTAMP,
MODIFIER VARCHAR2(50),
LAST_UPDATED_DATE TIMESTAMP default SYSTIMESTAMP,
BUTYPE VARCHAR2(10),
constraint PK_T_DM_IMP_S_MARK primary key (GUID)
);
--表名称:S表标记信息导入,表编码:T_DM_IMP_S_MARK,修改类型:修改表
alter table T_DM_IMP_S_MARK add SUB_SEGMENT VARCHAR2(15);
comment on column T_DM_IMP_S_MARK.SUB_SEGMENT is '小区隔';alter table T_DM_IMP_S_MARK add OID VARCHAR2(450);
comment on column T_DM_IMP_S_MARK.OID is '车型唯一键';alter table T_DM_IMP_S_MARK add ITEM_1 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_1 is '项目1';alter table T_DM_IMP_S_MARK add ITEM_2 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_2 is '项目2';alter table T_DM_IMP_S_MARK add ITEM_3 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_3 is '项目3';alter table T_DM_IMP_S_MARK add ITEM_4 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_4 is '项目4';alter table T_DM_IMP_S_MARK add ITEM_5 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_5 is '项目5';alter table T_DM_IMP_S_MARK add ITEM_6 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_6 is '项目6';alter table T_DM_IMP_S_MARK add ITEM_7 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_7 is '项目7';alter table T_DM_IMP_S_MARK add ITEM_8 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_8 is '项目8';alter table T_DM_IMP_S_MARK add ITEM_9 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_9 is '项目9';alter table T_DM_IMP_S_MARK add ITEM_10 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_10 is '项目10';alter table T_DM_IMP_S_MARK add ITEM_11 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_11 is '项目11';alter table T_DM_IMP_S_MARK add ITEM_12 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_12 is '项目12';alter table T_DM_IMP_S_MARK add ITEM_13 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_13 is '项目13';alter table T_DM_IMP_S_MARK add ITEM_14 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_14 is '项目14';alter table T_DM_IMP_S_MARK add ITEM_15 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_15 is '项目15';alter table T_DM_IMP_S_MARK add ITEM_16 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_16 is '项目16';alter table T_DM_IMP_S_MARK add ITEM_17 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_17 is '项目17';alter table T_DM_IMP_S_MARK add ITEM_18 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_18 is '项目18';alter table T_DM_IMP_S_MARK add ITEM_19 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_19 is '项目19';alter table T_DM_IMP_S_MARK add ITEM_20 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_20 is '项目20';alter table T_DM_IMP_S_MARK add ITEM_21 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_21 is '项目21';alter table T_DM_IMP_S_MARK add ITEM_22 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_22 is '项目22';alter table T_DM_IMP_S_MARK add ITEM_23 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_23 is '项目23';alter table T_DM_IMP_S_MARK add ITEM_24 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_24 is '项目24';alter table T_DM_IMP_S_MARK add ITEM_25 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_25 is '项目25';alter table T_DM_IMP_S_MARK add ITEM_26 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_26 is '项目26';alter table T_DM_IMP_S_MARK add ITEM_27 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_27 is '项目27';alter table T_DM_IMP_S_MARK add ITEM_28 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_28 is '项目28';alter table T_DM_IMP_S_MARK add ITEM_29 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_29 is '项目29';alter table T_DM_IMP_S_MARK add ITEM_30 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_30 is '项目30';alter table T_DM_IMP_S_MARK add ITEM_31 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_31 is '项目31';alter table T_DM_IMP_S_MARK add ITEM_32 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_32 is '项目32';alter table T_DM_IMP_S_MARK add ITEM_33 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_33 is '项目33';alter table T_DM_IMP_S_MARK add ITEM_34 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_34 is '项目34';alter table T_DM_IMP_S_MARK add ITEM_35 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_35 is '项目35';alter table T_DM_IMP_S_MARK add ITEM_36 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_36 is '项目36';alter table T_DM_IMP_S_MARK add ITEM_37 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_37 is '项目37';alter table T_DM_IMP_S_MARK add ITEM_38 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_38 is '项目38';alter table T_DM_IMP_S_MARK add ITEM_39 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_39 is '项目39';alter table T_DM_IMP_S_MARK add ITEM_40 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_40 is '项目40';alter table T_DM_IMP_S_MARK add ITEM_41 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_41 is '项目41';alter table T_DM_IMP_S_MARK add ITEM_42 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_42 is '项目42';alter table T_DM_IMP_S_MARK add ITEM_43 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_43 is '项目43';alter table T_DM_IMP_S_MARK add ITEM_44 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_44 is '项目44';alter table T_DM_IMP_S_MARK add ITEM_45 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_45 is '项目45';alter table T_DM_IMP_S_MARK add ITEM_46 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_46 is '项目46';alter table T_DM_IMP_S_MARK add ITEM_47 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_47 is '项目47';alter table T_DM_IMP_S_MARK add ITEM_48 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_48 is '项目48';alter table T_DM_IMP_S_MARK add ITEM_49 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_49 is '项目49';alter table T_DM_IMP_S_MARK add ITEM_50 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_50 is '项目50';alter table T_DM_IMP_S_MARK add ITEM_51 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_51 is '项目51';alter table T_DM_IMP_S_MARK add ITEM_52 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_52 is '项目52';alter table T_DM_IMP_S_MARK add ITEM_53 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_53 is '项目53';alter table T_DM_IMP_S_MARK add ITEM_54 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_54 is '项目54';alter table T_DM_IMP_S_MARK add ITEM_55 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_55 is '项目55';alter table T_DM_IMP_S_MARK add ITEM_56 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_56 is '项目56';alter table T_DM_IMP_S_MARK add ITEM_57 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_57 is '项目57';alter table T_DM_IMP_S_MARK add ITEM_58 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_58 is '项目58';alter table T_DM_IMP_S_MARK add ITEM_59 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_59 is '项目59';alter table T_DM_IMP_S_MARK add ITEM_60 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_60 is '项目60';alter table T_DM_IMP_S_MARK add ITEM_61 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_61 is '项目61';alter table T_DM_IMP_S_MARK add ITEM_62 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_62 is '项目62';alter table T_DM_IMP_S_MARK add ITEM_63 VARCHAR2(20);
comment on column T_DM_IMP_S_MARK.ITEM_63 is '项目63';
如果这些列只存一个'S'或空,这个表的设计可以改成把这些列合并成一列.
from T_DM_IMP_S_MARK t
where instr(nvl(ITEM_1, '~') || nvl(ITEM_2, '~') || nvl(ITEM_3, '~') ||
nvl(ITEM_500, '~'),
'~') = 0
where coalesce(item_1,item_2,item_3.....) != 'S'
-- 改成 = 'S' 吧。
select * from t
where coalesce(item_1,item_2,item_3.....) = 'S'