不明白你最后一行是怎么来的. CREATE TABLE t1(id int,名字 nvarchar(10),vn nvarchar(10),vr nvarchar(10),vp nvarchar(10)) insert into t1 select 20,'说话','k',null,null insert into t1 select 12,'没有',null,'h',null insert into t1 select 32,'开心',null,null,'1' insert into t1 select 89,'好吧','和',null,'你' create table t2(表2id int,表2名字 nvarchar(10),意义 nvarchar(10)) insert into t2 select 2,'说','讲' insert into t2 select 3,'说','说话' insert into t2 select 33,'话','话语' insert into t2 select 14,'没','无' insert into t2 select 16,'有',' 没' insert into t2 select 19,'开','开心' insert into t2 select 78,'好',' 12' insert into t2 select 79,'把',' 34' go select a.id,a.名字,a.vn,a.vr,a.vp,b.表2id,b.表2名字,b.意义 from t1 a left join t2 b on (case when a.vn is not null and a.vr is null then left(a.名字,1) when a.vn is null and a.vr is not null then substring(a.名字,2,1) when a.vp is not null then null end) =b.表2名字 /* id 名字 vn vr vp 表2id 表2名字 意义 ----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- 20 说话 k NULL NULL 2 说 讲 20 说话 k NULL NULL 3 说 说话 12 没有 NULL h NULL 16 有 没 32 开心 NULL NULL 1 NULL NULL NULL 89 好吧 和 NULL 你 78 好 12(5 行受影响)*/ go drop table t1,t2
我想表1的数据为CREATE TABLE t1(id int,名字 nvarchar(10),vn nvarchar(10),vr nvarchar(10),vp nvarchar(10)) insert into t1 select 20,'说话','k','','' insert into t1 select 12,'没有','','h','' insert into t1 select 32,'开心','','','1' insert into t1 select 89,'好吧','和','','你'如果vn vp vr 都是nill时应原样输出吧
create table t1(id int, 名字 varchar(10), vn varchar(10), vr varchar(10), vp varchar(10)) insert into t1 values(20 , '说话', 'k' , '' ,'') insert into t1 values(12 , '没有', '' , 'h','') insert into t1 values(32 , '开心', '' , '' ,'1') insert into t1 values(89 , '好吧', '和', '' ,'你') go create table t2(id int, 名字 varchar(10), 意义 varchar(10)) insert into t2 values(2 , '说', '讲') insert into t2 values(3 , '说', '说话') insert into t2 values(33, '话', '话语') insert into t2 values(14, '没', '无') insert into t2 values(16, '有', '没') insert into t2 values(19, '开', '开心') insert into t2 values(78, '好', '12') insert into t2 values(79, '把', '34') GO select t1.* ,t2.id t2_id , t2.名字 t2_名字 , t2.意义 t2_意义 from t1 , t2 where (t1.vn <> '' and t1.vn is not null and left(t1.名字,1) = t2.名字) or (t1.vr <> '' and t1.vr is not null and left(t1.名字,1) = t2.名字) union select t1.* ,null t2_id , null t2_名字 , null t2_意义 from t1 where vp = '' or vp is null union select t1.* ,t2.id t2_id , t2.名字 t2_名字 , t2.意义 t2_意义 from t1 , t2 where t1.vn <> '' and t1.vn is not null and t1.vr <> '' and t1.vr is not null and t1.vp <> '' and t1.vp is not null and (t1.vn = t2.名字 or t1.vr = t2.名字 or t1.vp = t2.名字) order by t1.iddrop table t1 , t2/* id 名字 vn vr vp t2_id t2_名字 t2_意义 ----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- 12 没有 h 14 没 无 12 没有 h NULL NULL NULL 20 说话 k NULL NULL NULL 20 说话 k 2 说 讲 20 说话 k 3 说 说话 89 好吧 和 你 78 好 12(所影响的行数为 6 行) */ 貌似和你的结果有点差距,自己看看吧.
修正: CREATE TABLE t1(id int,名字 nvarchar(10),vn nvarchar(10),vr nvarchar(10),vp nvarchar(10)) insert into t1 select 20,'说话','k',null,null insert into t1 select 12,'没有',null,'h',null insert into t1 select 32,'开心',null,null,'1' insert into t1 select 89,'好吧','和',null,'你' create table t2(表2id int,表2名字 nvarchar(10),意义 nvarchar(10)) insert into t2 select 2,'说','讲' insert into t2 select 3,'说','说话' insert into t2 select 33,'话','话语' insert into t2 select 14,'没','无' insert into t2 select 16,'有',' 没' insert into t2 select 19,'开','开心' insert into t2 select 78,'好',' 12' insert into t2 select 79,'把',' 34' go select a.id,a.名字,a.vn,a.vr,a.vp,b.表2id,b.表2名字,b.意义 from t1 a inner join t2 b on (case when a.vn is not null and a.vr is null then left(a.名字,1) when a.vn is null and a.vr is not null then substring(a.名字,2,1) end) =b.表2名字 union all select id,名字,vn,vr,vp,null,null,null from t1 where vp is not null --order by id /* id 名字 vn vr vp 表2id 表2名字 意义 ----------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- 12 没有 NULL h NULL 16 有 没 20 说话 k NULL NULL 2 说 讲 20 说话 k NULL NULL 3 说 说话 32 开心 NULL NULL 1 NULL NULL NULL 89 好吧 和 NULL 你 NULL NULL NULL 89 好吧 和 NULL 你 78 好 12(6 行受影响)*/ go drop table t1,t2
CREATE TABLE t1(id int,名字 nvarchar(10),vn nvarchar(10),vr nvarchar(10),vp nvarchar(10))
insert into t1 select 20,'说话','k',null,null
insert into t1 select 12,'没有',null,'h',null
insert into t1 select 32,'开心',null,null,'1'
insert into t1 select 89,'好吧','和',null,'你'
create table t2(表2id int,表2名字 nvarchar(10),意义 nvarchar(10))
insert into t2 select 2,'说','讲'
insert into t2 select 3,'说','说话'
insert into t2 select 33,'话','话语'
insert into t2 select 14,'没','无'
insert into t2 select 16,'有',' 没'
insert into t2 select 19,'开','开心'
insert into t2 select 78,'好',' 12'
insert into t2 select 79,'把',' 34'
go
select a.id,a.名字,a.vn,a.vr,a.vp,b.表2id,b.表2名字,b.意义
from t1 a left join t2 b on
(case when a.vn is not null and a.vr is null then left(a.名字,1)
when a.vn is null and a.vr is not null then substring(a.名字,2,1)
when a.vp is not null then null end)
=b.表2名字
/*
id 名字 vn vr vp 表2id 表2名字 意义
----------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
20 说话 k NULL NULL 2 说 讲
20 说话 k NULL NULL 3 说 说话
12 没有 NULL h NULL 16 有 没
32 开心 NULL NULL 1 NULL NULL NULL
89 好吧 和 NULL 你 78 好 12(5 行受影响)*/
go
drop table t1,t2
我想表1的数据为CREATE TABLE t1(id int,名字 nvarchar(10),vn nvarchar(10),vr nvarchar(10),vp nvarchar(10))
insert into t1 select 20,'说话','k','',''
insert into t1 select 12,'没有','','h',''
insert into t1 select 32,'开心','','','1'
insert into t1 select 89,'好吧','和','','你'如果vn vp vr 都是nill时应原样输出吧
insert into t1 values(20 , '说话', 'k' , '' ,'')
insert into t1 values(12 , '没有', '' , 'h','')
insert into t1 values(32 , '开心', '' , '' ,'1')
insert into t1 values(89 , '好吧', '和', '' ,'你')
go
create table t2(id int, 名字 varchar(10), 意义 varchar(10))
insert into t2 values(2 , '说', '讲')
insert into t2 values(3 , '说', '说话')
insert into t2 values(33, '话', '话语')
insert into t2 values(14, '没', '无')
insert into t2 values(16, '有', '没')
insert into t2 values(19, '开', '开心')
insert into t2 values(78, '好', '12')
insert into t2 values(79, '把', '34')
GO
select t1.* ,t2.id t2_id , t2.名字 t2_名字 , t2.意义 t2_意义 from t1 , t2 where
(t1.vn <> '' and t1.vn is not null and left(t1.名字,1) = t2.名字) or (t1.vr <> '' and t1.vr is not null and left(t1.名字,1) = t2.名字)
union
select t1.* ,null t2_id , null t2_名字 , null t2_意义 from t1 where vp = '' or vp is null
union
select t1.* ,t2.id t2_id , t2.名字 t2_名字 , t2.意义 t2_意义 from t1 , t2 where
t1.vn <> '' and t1.vn is not null and t1.vr <> '' and t1.vr is not null and t1.vp <> '' and t1.vp is not null and
(t1.vn = t2.名字 or t1.vr = t2.名字 or t1.vp = t2.名字)
order by t1.iddrop table t1 , t2/*
id 名字 vn vr vp t2_id t2_名字 t2_意义
----------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
12 没有 h 14 没 无
12 没有 h NULL NULL NULL
20 说话 k NULL NULL NULL
20 说话 k 2 说 讲
20 说话 k 3 说 说话
89 好吧 和 你 78 好 12(所影响的行数为 6 行)
*/
貌似和你的结果有点差距,自己看看吧.
CREATE TABLE t1(id int,名字 nvarchar(10),vn nvarchar(10),vr nvarchar(10),vp nvarchar(10))
insert into t1 select 20,'说话','k',null,null
insert into t1 select 12,'没有',null,'h',null
insert into t1 select 32,'开心',null,null,'1'
insert into t1 select 89,'好吧','和',null,'你'
create table t2(表2id int,表2名字 nvarchar(10),意义 nvarchar(10))
insert into t2 select 2,'说','讲'
insert into t2 select 3,'说','说话'
insert into t2 select 33,'话','话语'
insert into t2 select 14,'没','无'
insert into t2 select 16,'有',' 没'
insert into t2 select 19,'开','开心'
insert into t2 select 78,'好',' 12'
insert into t2 select 79,'把',' 34'
go
select a.id,a.名字,a.vn,a.vr,a.vp,b.表2id,b.表2名字,b.意义
from t1 a inner join t2 b on
(case when a.vn is not null and a.vr is null then left(a.名字,1)
when a.vn is null and a.vr is not null then substring(a.名字,2,1)
end)
=b.表2名字
union all
select id,名字,vn,vr,vp,null,null,null
from t1 where vp is not null
--order by id
/*
id 名字 vn vr vp 表2id 表2名字 意义
----------- ---------- ---------- ---------- ---------- ----------- ---------- ----------
12 没有 NULL h NULL 16 有 没
20 说话 k NULL NULL 2 说 讲
20 说话 k NULL NULL 3 说 说话
32 开心 NULL NULL 1 NULL NULL NULL
89 好吧 和 NULL 你 NULL NULL NULL
89 好吧 和 NULL 你 78 好 12(6 行受影响)*/
go
drop table t1,t2