insert into table1 select left(col2,2) from table2 b where not exists(select 1 from table1 a where a.col1=left(b,2))
insert into table1 select left(co2,2) from table2 b where not exists(select 1 from table1 a where a.co1=left(b.co2,2))
或者if not exists(select 1 from table1 a where co1 in ('hi','mn')) begin insert into table1 select 'hi' union select 'mn' end
if exists(select 1 from table2 where left(co2,2) not in(select co1 from table1)) begin insert table1(co1) select distinct left(col2,2) where left(co2,2) not in(select co1 from table1)) end
gahade(与君共勉)正确但楼主意思不甚明确,补充一句
b 是不是代表select left(co2,2) from table2 a 是不是代表select co1 from table1?
谢谢给位高手,解决问题起来 游刃有余! 我还想在刚才的问题上加case h开头的取1位,m开头的区3位结果(其他还是取两位和table1 比较) 结果 table1:co1 ab df h mn0请帮忙!
insert into table1 select left(co2,2) from (select case when left(co2,1)='h' then 'h' when left(co2,1)='m' then left(co2,3) else co2 end from table2)b where not exists(select 1 from table1 a where a.co1=left(b.co2,2))
上面的用下来不行。 select left(co2,2) from 只能取2位,不管后面是什么。 from后面用的结果集b报错了。
CREATE TABLE table1(co1 varchar(100)) go CREATE TABLE table2(co2 varchar(100)) go INSERT INTO table1(co1) VALUES('ab') INSERT INTO table1(co1) VALUES('df') INSERT INTO table1(co1) VALUES('h') go INSERT INTO table2(co2) VALUES('abc') INSERT INTO table2(co2) VALUES('efg') INSERT INTO table2(co2) VALUES('hij') INSERT INTO table2(co2) VALUES('abc') INSERT INTO table2(co2) VALUES('mno') go h开头的取1位,m开头的区3位结果(其他还是取两位和table1 比较) 结果 table1:co1 ab df h mn0
insert into table1 select left(co2,2) from table2 b where not exists(select 1 from table1 a where a.co1=left(b.co2,2))
insert into table1 select case left(co2,1) when 'h' then left(co2,1) when 'm' then left(co2,3) else left(co2,2) end from table2 b where not exists(select 1 from table1 a where a.co1=case left(co2,1) when 'h' then left(co2,1) when 'm' then left(co2,3) else left(co2,2) end)
if exists(select 1 from table2 a where not exists(select 1 from table1 where co1=left(co2,2))) begin insert into table1(co1) select left(co2,2) from table2 a where not exists(select 1 from table1 where co1=left(co2,2)) end
select left(col2,2) from table2 b
where not exists(select 1 from table1 a where a.col1=left(b,2))
select left(co2,2) from table2 b
where not exists(select 1 from table1 a where a.co1=left(b.co2,2))
begin
insert into table1
select 'hi' union select 'mn'
end
begin
insert table1(co1) select distinct left(col2,2)
where left(co2,2) not in(select co1 from table1))
end
a 是不是代表select co1 from table1?
我还想在刚才的问题上加case
h开头的取1位,m开头的区3位结果(其他还是取两位和table1 比较)
结果
table1:co1
ab
df
h
mn0请帮忙!
select left(co2,2) from (select case when left(co2,1)='h' then 'h' when left(co2,1)='m' then left(co2,3) else co2 end from table2)b
where not exists(select 1 from table1 a where a.co1=left(b.co2,2))
select left(co2,2) from 只能取2位,不管后面是什么。
from后面用的结果集b报错了。
CREATE TABLE table1(co1 varchar(100))
go
CREATE TABLE table2(co2 varchar(100))
go
INSERT INTO table1(co1) VALUES('ab')
INSERT INTO table1(co1) VALUES('df')
INSERT INTO table1(co1) VALUES('h')
go
INSERT INTO table2(co2) VALUES('abc')
INSERT INTO table2(co2) VALUES('efg')
INSERT INTO table2(co2) VALUES('hij')
INSERT INTO table2(co2) VALUES('abc')
INSERT INTO table2(co2) VALUES('mno')
go
h开头的取1位,m开头的区3位结果(其他还是取两位和table1 比较)
结果
table1:co1
ab
df
h
mn0
select left(co2,2) from table2 b
where not exists(select 1 from table1 a where a.co1=left(b.co2,2))
select case left(co2,1) when 'h' then left(co2,1) when 'm' then left(co2,3) else left(co2,2) end
from table2 b
where not exists(select 1 from table1 a where a.co1=case left(co2,1) when 'h' then left(co2,1) when 'm' then left(co2,3) else left(co2,2) end)
if exists(select 1 from table2 a
where not exists(select 1 from table1 where co1=left(co2,2)))
begin
insert into table1(co1) select left(co2,2) from table2 a
where not exists(select 1 from table1 where co1=left(co2,2))
end