/* 應該是sql語句中比較運算符兩邊的列名它們所對應的字符集不一樣引起的。 示例如下: */if object_id('tempdb..#a') is not null drop table #a if object_id('tempdb..#b') is not null drop table #b gocreate table #a(id varchar(10) collate Chinese_Taiwan_Stroke_CI_AS not null) create table #b(id varchar(10) collate Chinese_Taiwan_Stroke_CS_AS not null) goinsert #a select '1' union all select '2' union all select '5' insert #b select '1' union all select '3' union all select '5' go--會出錯,提示:"Cannot resolve collation conflict for equal to operation." select * from #a a join #b b on a.id=b.id go/* 有兩種方法解決: 方法一:查詢的時候指定列名的字符集(兩者一定要指定為相同的字符集) */ --1.都指定為Chinese_Taiwan_Stroke_CI_AS字符 select * from #a a join #b b on a.id collate Chinese_Taiwan_Stroke_CI_AS =b.id collate Chinese_Taiwan_Stroke_CI_AS /* id id 1 1 5 5 */--2.都指定為Chinese_Taiwan_Stroke_CI_AS字符 select * from #a a join #b b on a.id collate Chinese_Taiwan_Stroke_CS_AS =b.id collate Chinese_Taiwan_Stroke_CS_AS /* id id 1 1 5 5 */--3.都指定為Chinese_Taiwan_Stroke_CI_AS字符 select * from #a a join #b b on a.id collate Chinese_Taiwan_Stroke_CI_AS_KS =b.id collate Chinese_Taiwan_Stroke_CI_AS_KS /* id id 1 1 5 5 */ /* 方法二:把兩者的列名的字符集指定為相同.可以在企業管理器中去設置,也可用語句設置。 下列通過語句來改: */ alter table #b alter column id varchar(10) collate Chinese_Taiwan_Stroke_CI_AS not null go--現在不會出錯了. select * from #a a join #b b on a.id=b.id /* id id 1 1 5 5 */
應該是sql語句中比較運算符兩邊的列名它們所對應的字符集不一樣引起的。
示例如下:
*/if object_id('tempdb..#a') is not null
drop table #a
if object_id('tempdb..#b') is not null
drop table #b
gocreate table #a(id varchar(10) collate Chinese_Taiwan_Stroke_CI_AS not null)
create table #b(id varchar(10) collate Chinese_Taiwan_Stroke_CS_AS not null)
goinsert #a
select '1' union all
select '2' union all
select '5' insert #b
select '1' union all
select '3' union all
select '5'
go--會出錯,提示:"Cannot resolve collation conflict for equal to operation."
select * from #a a join #b b on a.id=b.id
go/*
有兩種方法解決:
方法一:查詢的時候指定列名的字符集(兩者一定要指定為相同的字符集)
*/
--1.都指定為Chinese_Taiwan_Stroke_CI_AS字符
select * from #a a join #b b on a.id collate Chinese_Taiwan_Stroke_CI_AS =b.id collate Chinese_Taiwan_Stroke_CI_AS
/*
id id
1 1
5 5
*/--2.都指定為Chinese_Taiwan_Stroke_CI_AS字符
select * from #a a join #b b on a.id collate Chinese_Taiwan_Stroke_CS_AS =b.id collate Chinese_Taiwan_Stroke_CS_AS
/*
id id
1 1
5 5
*/--3.都指定為Chinese_Taiwan_Stroke_CI_AS字符
select * from #a a join #b b on a.id collate Chinese_Taiwan_Stroke_CI_AS_KS =b.id collate Chinese_Taiwan_Stroke_CI_AS_KS
/*
id id
1 1
5 5
*/
/*
方法二:把兩者的列名的字符集指定為相同.可以在企業管理器中去設置,也可用語句設置。
下列通過語句來改:
*/
alter table #b alter column id varchar(10) collate Chinese_Taiwan_Stroke_CI_AS not null
go--現在不會出錯了.
select * from #a a join #b b on a.id=b.id
/*
id id
1 1
5 5
*/