求一个查询语句

[zyjj1688]

table1
aa
bb
cc
aaa
bbb

table2
aa、bb
aa
aa、cc
bb、cc
ee
ff

目标把查找table2是否在table里存在，如果有“、”就拆分后查找。

[学海无涯-回头是岸]

你网上找下 字符串拆分函数 很多的，你看下 也可以学一下

[吉普赛的歌]

USE tempdb
GO
IF OBJECT_ID('table1') IS NOT NULL DROP TABLE table1
IF OBJECT_ID('table2') IS NOT NULL DROP TABLE table2
GO
CREATE TABLE table1 (n VARCHAR(50))
CREATE TABLE table2 (n VARCHAR(50))
GO
INSERT INTO table1
SELECT 'aa'
UNION ALL SELECT  'bb'
UNION ALL SELECT  'cc'
UNION ALL SELECT  'aaa'
UNION ALL SELECT  'bbb'

INSERT INTO table2
SELECT 'aa、bb'
UNION ALL SELECT  'aa'
UNION ALL SELECT  'aa、cc'
UNION ALL SELECT  'bb、cc'
UNION ALL SELECT  'ee'
UNION ALL SELECT  'ff'

SELECT * FROM table2 AS t2 WHERE EXISTS(
	SELECT 1 FROM table1 AS t1 WHERE t2.n LIKE '%'+t1.n+'%' 	
)
/*
aa、bb
aa
aa、cc
bb、cc
*/

[吉普赛的歌]

上面那个不够严谨， 版主帮忙删除一下。 按这个：

USE tempdb
GO
IF OBJECT_ID('table1') IS NOT NULL DROP TABLE table1
IF OBJECT_ID('table2') IS NOT NULL DROP TABLE table2
GO
CREATE TABLE table1 (n VARCHAR(50))
CREATE TABLE table2 (n VARCHAR(50))
GO
INSERT INTO table1
SELECT 'aa'
UNION ALL SELECT  'bb'
UNION ALL SELECT  'cc'
UNION ALL SELECT  'aaa'
UNION ALL SELECT  'bbb'

INSERT INTO table2
SELECT 'aa、bb'
UNION ALL SELECT  'aa'
UNION ALL SELECT  'aa、cc'
UNION ALL SELECT  'bb、cc'
UNION ALL SELECT  'ee'
UNION ALL SELECT  'ff'

SELECT * FROM table2 AS t2 WHERE EXISTS(
	SELECT 1 FROM table1 AS t1 WHERE '、'+t2.n+'、' LIKE '%、'+t1.n+'、%' 	
)
/*
aa、bb
aa
aa、cc
bb、cc
*/