两个表的JOIN问题?

[liguochun]

表1:

ID NAME TYPE
------------------------------
1 NA1 A1
2 NA2 B1
3 NA3 C1

表2:

NUM SEX TYPE
------------------------------
1 0 A1|K1
2 1 B1|K2
3 0 C1|K3


我想两个表用TYPE字段JOIN, 但是表2里的TYPE字段值,"|" 符号 前一部分的值和表1里的TYPE值相同.
用TYPE字段怎么JOIN?

请各位高手帮忙,谢谢!

[dawugui]

先将表2分离

CREATE TABLE A(id INT,country VARCHAR(100))
INSERT A
SELECT 1,'中国;日本;韩国' UNION ALL
SELECT 2,'美国;意大利;法国' UNION ALL
SELECT 3,'德国'
SELECT * FROM A

-- 建立一个辅助的临时表就可以了
SELECT TOP 8000 id = identity(int,1,1)
INTO # FROM syscolumns a, syscolumns b


SELECT
A.ID,
COUNTRY = SUBSTRING(A.COUNTRY, B.ID, CHARINDEX(';', A.COUNTRY + ';', B.ID) - B.ID)
FROM A, # B
WHERE SUBSTRING(';' + a.COUNTRY, B.id, 1) = ';'
ORDER BY 1,2
GO


DROP TABLE A,#

[子陌红尘]

select
*
from
表1 a
inner join
表2 b
on
a.TYPE=left(b.TYPE,charindex('|',b.TYPE)-1)

[子陌红尘]

declare @t1 table(ID int,NAME varchar(6),TYPE varchar(6))
insert into @t1 select 1,'NA1','A1'
insert into @t1 select 2,'NA2','B1'
insert into @t1 select 3,'NA3','C1'

declare @t2 table(NUM int,SEX int,TYPE varchar(20))
insert into @t2 select 1,0,'A1|K1'
insert into @t2 select 2,1,'B1|K2'
insert into @t2 select 3,0,'C1|K3'

select
*
from
@t1 a
inner join
@t2 b
on
a.TYPE=left(b.TYPE,charindex('|',b.TYPE)-1)

/*
ID NAME TYPE NUM SEX TYPE
----------- ------ ------ ----------- ----------- --------------------
1 NA1 A1 1 0 A1|K1
2 NA2 B1 2 1 B1|K2
3 NA3 C1 3 0 C1|K3
*/

[pengdali]

select * from 表1 a join 表2 b on a.type like b.type+'%'

[marco08]

先將表2的TYPE 拆分

[pengdali]

select * from 表1 a join 表2 b on b.type like a.type+'|%'

[StarRains]

select * from 表1 INNER JOIN 表2 ON 表1.TYPE=LEFT(表2.TYPE,CHARINDEX('|',表2.TYPE)-1)