表table
a b c
我的sql语句 select into tmptest from (select * from table)
结果mysql报语法错误,我在网上查似乎sqlserver和oracle都支持这种语法,是不是mysql不支持?
那我应该怎么写,求助...
a b c
我的sql语句 select into tmptest from (select * from table)
结果mysql报语法错误,我在网上查似乎sqlserver和oracle都支持这种语法,是不是mysql不支持?
那我应该怎么写,求助...
select * from temptest into
(select * from test2 as b left join kdpumc0614 as a on (b.cname = a.cname or a.`检验项目别名` like concat('%',b.cname,'%') or a.`正式名称` like concat('%',b.cname,'%') or a.`检验项目英文名称` = b.ename or a.`检验项目英文缩写` = b.testalias) and a.sampletype = b.sampletype
union
select * from test2 as b right join kdpumc0614 as a on (b.cname = a.cname or a.`检验项目别名` like concat('%',b.cname,'%') or a.`正式名称` like concat('%',b.cname,'%') or a.`检验项目英文名称` = b.ename or a.`检验项目英文缩写` = b.testalias) and a.sampletype = b.sampletype
)
select * from (
(select * from test2 as b left join kdpumc0614 as a on (b.cname = a.cname or a.`检验项目别名` like concat('%',b.cname,'%') or a.`正式名称` like concat('%',b.cname,'%') or a.`检验项目英文名称` = b.ename or a.`检验项目英文缩写` = b.testalias) and a.sampletype = b.sampletype
union
select * from test2 as b right join kdpumc0614 as a on (b.cname = a.cname or a.`检验项目别名` like concat('%',b.cname,'%') or a.`正式名称` like concat('%',b.cname,'%') or a.`检验项目英文名称` = b.ename or a.`检验项目英文缩写` = b.testalias) and a.sampletype = b.sampletype
)) b
---创建表
create table temptest
(id int not null auto_increment,
....
primary key(id))
---插入表
inert into tmptest select * from table;
简化a的结构:
a b c
1 2 3
4 null 6
简化的b的结构
d e f
1 2 null
4 5 6
除此之外有额外一张空白表c
我现在把a和b 以on a = b的条件join在一起,然后如果a.b <> null将a.b更新至新表c,否则将b.e更新至新表c
c:
a b c
1 2 3
4 5 6
select a.a,ifnull(a.b,b.e),ifnull(a.c,b.f)
from a inner join b on a.a=b.d
(select * from test2 as b left join kdpumc0614 as a on (b.cname = a.cname or a.`检验项目别名` like concat('%',b.cname,'%') or a.`正式名称` like concat('%',b.cname,'%') or a.`检验项目英文名称` = b.ename or a.`检验项目英文缩写` = b.testalias) and a.sampletype = b.sampletype
union
select * from test2 as b right join kdpumc0614 as a on (b.cname = a.cname or a.`检验项目别名` like concat('%',b.cname,'%') or a.`正式名称` like concat('%',b.cname,'%') or a.`检验项目英文名称` = b.ename or a.`检验项目英文缩写` = b.testalias) and a.sampletype = b.sampletype
) t