不明白，怎样select一个表中的字段包含另外一个表中的字段啊？

[wyanshan]

比如
t1的字段：name里面有：MichaelJacson,ShainaTwain两个名字。
t2的字段：lastname有Twain,cherry两个姓。
select出来t1的name字段包含t2的lastname的所有字段。

[SQL77]

CORSS JOIN ?

SELECT * FROM A,B
?

[youyou2404]

select *,stuff((select ','+lastname from t2 for xml path('')),1,1,'') from t1

[wyanshan]

[Quote=引用 2 楼 youyou2404 的回复:]
SQL code
select *,stuff((select ','+lastname from t2 for xml path('')),1,1,'') from t1
[/Quote]
看不懂，怎么还有xml path啊？sql server里面的吗？等待清晰的答案。

[sql_sf]

[Quote=引用 3 楼 wyanshan 的回复:]
引用 2 楼 youyou2404 的回复:
SQL code
select *,stuff((select ','+lastname from t2 for xml path('')),1,1,'') from t1

看不懂，怎么还有xml path啊？sql server里面的吗？等待清晰的答案。
[/Quote]
关键是没看懂你的意思

[wyanshan]

就是select出来t1的name字段包含t2的lastname字段的所有记录。啊。比如这个例子select出来的记录就是：t1的ShainaTwain包含了t2的Twain啊。

[永生天地]

什么结果。列一下

[v1ctory1216]

select t1.name
from t1
where t1.name in
(select t2.lastname
from t2
)

这个？

[wyanshan]

比如select t1.name,t2.lastname 条件：t1的name包含t2的lastname。比如t1的ShaniaTwain字符串包含t2的Twain字符串。
结果就是：
ShaniaTwain,Twain
说白了，就是两个字段字符串，一个字符串包含另外一个字符串。两个字符串分别属于两张表而已。

[brownhwy]

这一个好象需要先写出一个函数，用来判定两个字符串是否是包含关系。
我好象不知道sql里有这样的函数。

至于查询简单。

[淘龙]

你的查询语句，意思是查询t1表中的所有name 条件是：t1表中的name要是t2表中lastname字段中的所有存在的字符。

[ShenLiang2025]

--Ⅰ表和数据
SET NOCOUNT ON
GO

CREATE TABLE for_Charindex
(id int IDENTITY(1,1),
 v_name varchar(32)
)


INSERT for_Charindex(v_name) VALUES ('shen')
INSERT for_Charindex(v_name) VALUES ('sheng')
INSERT for_Charindex(v_name) VALUES ('shang')
INSERT for_Charindex(v_name) VALUES ('ang')
INSERT for_Charindex(v_name) VALUES ('ing')
INSERT for_Charindex(v_name) VALUES ('sting')
CREATE TABLE for_Charindex_T
(id int IDENTITY(1,1),
 v_name varchar(32)
)
INSERT for_Charindex_T(v_name) VALUES ('sh')
INSERT for_Charindex_T(v_name) VALUES ('en')
INSERT for_Charindex_T(v_name) VALUES ('ting')

--Ⅱ 查询

DECLARE @v_name varchar(32)
DECLARE @Count int
DECLARE @Max int
DECLARE @v_nameComp varchar(32)
DECLARE @CountComp int
DECLARE @MaxComp int

DROP TABLE #for_Charindex
CREATE TABLE #for_Charindex (v_name varchar(32),v_nameComp varchar(32))

SET @Count = 1 
SET @CountComp = 1 
SELECT @MaxComp = COUNT (id) FROM for_Charindex_T
SELECT @Max = COUNT (id) FROM for_Charindex

WHILE @Count <= @Max 
BEGIN
	SELECT @v_name = v_name FROM for_Charindex WHERE id = @Count
	WHILE @CountComp <= @MaxComp
	BEGIN
		SELECT @v_nameComp = v_name FROM for_Charindex_T WHERE id = @CountComp
		IF CHARINDEX(@v_nameComp,@v_name) > 0 
		BEGIN
			INSERT INTO  #for_Charindex(v_name,v_nameComp) SELECT @v_name,@v_nameComp
		END
		SET @CountComp = @CountComp + 1
	END
	SET @Count = @Count + 1
	SET @CountComp = 1

END


SELECT * FROM  #for_Charindex

--Ⅲ结果
v_name                           v_nameComp
-------------------------------- --------------------------------
shen                             sh
shen                             en
sheng                            sh
sheng                            en
shang                            sh
sting                            ting

[ggsysy]

a join b？

[ShenLiang2025]

SELECT A.*,B.* FROM for_Charindex A ,for_Charindex_T B
WHERE CHARINDEX(A.v_name,B.v_name) > 0 OR  CHARINDEX(B.v_name,A.v_name)> 0

--

id          v_name                           id          v_name
----------- -------------------------------- ----------- --------------------------------
1           shen                             1           sh
2           sheng                            1           sh
3           shang                            1           sh
1           shen                             2           en
2           sheng                            2           en
5           ing                              3           ting
6           sting                            3           ting

(7 row(s) affected)

[steven_0825]

借用一下11楼的表

CREATE TABLE for_Charindex
(id int IDENTITY(1,1),
 v_name varchar(32)
)
INSERT for_Charindex(v_name) VALUES ('shen')
INSERT for_Charindex(v_name) VALUES ('sheng')
INSERT for_Charindex(v_name) VALUES ('shang')
INSERT for_Charindex(v_name) VALUES ('ang')
INSERT for_Charindex(v_name) VALUES ('ing')
INSERT for_Charindex(v_name) VALUES ('sting')
CREATE TABLE for_Charindex_T
(id int IDENTITY(1,1),
 v_name varchar(32)
)
INSERT for_Charindex_T(v_name) VALUES ('sh')
INSERT for_Charindex_T(v_name) VALUES ('en')
INSERT for_Charindex_T(v_name) VALUES ('ting')
select * from for_Charindex t1,for_Charindex_t t2 where CHARINDEX(t1.v_name,t2.v_name)>0

[cosmo_sei]

是不是我理解的这样：
t1.name是全名，t2.lastname是姓，如果t1.name的姓在t2.lastname中存在，则将t2的对应行附加到t1的对应行后面。

SELECT t1.NAME , t2.*
FROM t1
LEFT OUTER JOIN t2
ON PATINDEX('%'+t2.lastname+'%',t1.name)>0  --匹配t2.lastname是t1.name的子集