create table 表一(name varchar(10),data_ int)insert into 表一 select 'A',4 union all select 'b',6 union all select 'A',2 union all select 'B',3 union all select 'C',4 union all select 'D',5create table 表二(name varchar(10),data_ int)insert into 表二 select 'A',3 union all select 'b',5 union all select 'B',2 union all select 'D',1 select a.name,a.data_,b.data_ 'data_2' from 表一 a left join 表二 b on a.name=b.name collate Chinese_PRC_CS_AS/* name data_ data_2 ---------- ----------- ----------- A 4 3 b 6 5 A 2 3 B 3 2 C 4 NULL D 5 1(6 row(s) affected) */
if object_id ('T1') is not null drop table T1 create table T1(name varchar(20),data_ int) insert into T1 select 'A',4 union all select 'b',6 union all select 'A',2 union all select 'B',3 union all select 'C',4 union all select 'D',5 if object_id ('T2') is not null drop table T2 create table T2(name varchar(20),data_ int)
insert into T2 select 'A',3 union all select 'b',5 union all select 'B',2 union all select 'D',1
select a.name,a.data_,b.data_ 'data_2' from T1 a left join T2 b on a.name=b.name
select a.name,a.data_,b.data_ as data_2 from 表一 a left join 表二 b on a.name=b.name
我现在按照一般的写法还是不行 我发现我的表一 里面的数据,name这行里面所有值都有一个空格, name data_ A 4 b 6 A 2 B 3 C 4 D 5
改了上面的代码,去掉了空格,应该就可以了: create table 表一(name varchar(10),data_ int)insert into 表一 select ' A ',4 union all select ' b ',6 union all select ' A ',2 union all select ' B ',3 union all select ' C ',4 union all select ' D ',5create table 表二(name varchar(10),data_ int)insert into 表二 select 'A',3 union all select 'b',5 union all select 'B',2 union all select 'D',1 select a.name,a.data_,b.data_ 'data_2' from 表一 a left join 表二 b on rtrim(ltrim(a.name))=b.name collate Chinese_PRC_CS_AS/* name data_ data_2 A 4 3 b 6 5 A 2 3 B 3 2 C 4 NULL D 5 1 */
表一和表二 name 里面的值 有的是中文啊 就是名字 就不准了
select a.name,a.data_,b.data_ as data_2 from 表一 a left join 表二 b on ltrim(rtrim(a.name))=ltrim(rtrim(b.name))
大家好 我的表一里面的数据描述如下 name 这列都是中文名字,data_这列都是数值。 表二里面 name这列也是中文名字,data_这列都是数值。 我抽象成上面的小例子看来是个错误
噗~用trim去掉空格吧,你是怎么发现是空格而不是其它的特殊符号
SELECT a.name, a.data_, b.name,b.data_ AS data2_ FROM Sheet1_ a LEFT OUTER JOIN Sheet2_ b ON ltrim(rtrim(a.name))=b.name where a.name like '%段矿%'-- 段矿林 9 NULL NULL
select * from Sheet1_ where name like'%段矿%'--段矿林 9 select * from Sheet2_ where name like'段矿%'--段矿林 10 select count(*) from Sheet1_--1318 select count(*) from Sheet2_--1298单独查表 结果和 联合查 结果不一样啊
select A.name,A.data as data_1,B.data as _date_2 from A left join B on A.name=B.name COLLATE Chinese_PRC_CS_AS
select A.name,A.data as data_1,B.data as _date_2 from A left join B on ltrim(rtrim(A.name))=ltrim(rtrim(B.name)) COLLATE Chinese_PRC_CS_AS
create table 表一(name varchar(10),data_ int)insert into 表一
select 'A',4 union all
select 'b',6 union all
select 'A',2 union all
select 'B',3 union all
select 'C',4 union all
select 'D',5create table 表二(name varchar(10),data_ int)insert into 表二
select 'A',3 union all
select 'b',5 union all
select 'B',2 union all
select 'D',1
select a.name,a.data_,b.data_ 'data_2'
from 表一 a
left join 表二 b on a.name=b.name collate Chinese_PRC_CS_AS/*
name data_ data_2
---------- ----------- -----------
A 4 3
b 6 5
A 2 3
B 3 2
C 4 NULL
D 5 1(6 row(s) affected)
*/
if object_id ('T1') is not null
drop table T1
create table T1(name varchar(20),data_ int)
insert into T1
select 'A',4 union all
select 'b',6 union all
select 'A',2 union all
select 'B',3 union all
select 'C',4 union all
select 'D',5
if object_id ('T2') is not null
drop table T2
create table T2(name varchar(20),data_ int)
insert into T2
select 'A',3 union all
select 'b',5 union all
select 'B',2 union all
select 'D',1
select a.name,a.data_,b.data_ 'data_2'
from T1 a
left join T2 b on a.name=b.name
select a.name,a.data_,b.data_ as data_2
from 表一 a
left join 表二 b on a.name=b.name
我发现我的表一 里面的数据,name这行里面所有值都有一个空格,
name data_
A 4
b 6
A 2
B 3
C 4
D 5
改了上面的代码,去掉了空格,应该就可以了:
create table 表一(name varchar(10),data_ int)insert into 表一
select ' A ',4 union all
select ' b ',6 union all
select ' A ',2 union all
select ' B ',3 union all
select ' C ',4 union all
select ' D ',5create table 表二(name varchar(10),data_ int)insert into 表二
select 'A',3 union all
select 'b',5 union all
select 'B',2 union all
select 'D',1
select a.name,a.data_,b.data_ 'data_2'
from 表一 a
left join 表二 b on rtrim(ltrim(a.name))=b.name collate Chinese_PRC_CS_AS/*
name data_ data_2
A 4 3
b 6 5
A 2 3
B 3 2
C 4 NULL
D 5 1
*/
from 表一 a
left join 表二 b on ltrim(rtrim(a.name))=ltrim(rtrim(b.name))
name 这列都是中文名字,data_这列都是数值。
表二里面 name这列也是中文名字,data_这列都是数值。
我抽象成上面的小例子看来是个错误
FROM Sheet1_ a LEFT OUTER JOIN
Sheet2_ b ON ltrim(rtrim(a.name))=b.name where a.name like '%段矿%'-- 段矿林 9 NULL NULL
select * from Sheet1_ where name like'%段矿%'--段矿林 9
select * from Sheet2_ where name like'段矿%'--段矿林 10
select count(*) from Sheet1_--1318
select count(*) from Sheet2_--1298单独查表 结果和 联合查 结果不一样啊
select A.name,A.data as data_1,B.data as _date_2 from A left join B on A.name=B.name COLLATE Chinese_PRC_CS_AS
select A.name,A.data as data_1,B.data as _date_2 from A left join B
on ltrim(rtrim(A.name))=ltrim(rtrim(B.name)) COLLATE Chinese_PRC_CS_AS
只能 replace(A.name,' ','')-- 注意' '这个里面不是空格,是你从数据库里粘过来的特殊字符